[RESOLVED] how to re-order db entries

@jogolMar 29.2007

hello experts,

i have a list of drinks stored in a mysql table. the fields i have are “name”, “price”, “group” (softdrinks, red wine, white wine, etc), “description” and “id” (auto_increment).

what i need is the possibility to (change the) order (of) the drinks freely via an admin page. maybe with an additional mysql table field called “order” or something?

what is the best way to achieve this?

tia

to post a comment

PHP

15 Comments(s) _↴

@DARTHTAMPONMar 29.2007 — #You wont be able to if the colum is auto numbered.

You may want to add a new column into the table that will allow you to alter the order. You will not want to mess with the id since it is most likly the primary key and only bad things can happen when you change the link to the table.

@aj_nscMar 29.2007 — #You may want to change the order of how they are outputted at any specific time, but why change the order of the way they are listed in the database? It makes no differences if a long island iced-tea comes before or after a margarita in the database......the only time when the order matters is when you output them, and you can order them then based on your query.

@jogolauthorMar 30.2007 — #thx for your suggestions. well unfortunately for my client it makes a difference if the margherita comes before or after the long island iced tea, so he wants total control over the order.

so if i add another field called "order" like DARTHTAMPON suggested how could the admin page look like?

having the order numbers in a drop down (with the current order number selected) next to each entry?

when a order number is selected twice a javascript could alert. if everythings fine the entries have to be updated in the db with a while or foreach loop?

@aj_nscMar 30.2007 — #Yeah, once again though, this is the order of the OUTPUT, not the way the entries are in the database. How does your client want them ordered? Alphabetically? By Price?

Google "mysql order by" and you'll find some information on how to order the entries in the output

If your client does not want them ordered by any specific field, then yeah, you'll have to add another one, sorry if I have misintepreted your problem.

@DARTHTAMPONMar 30.2007 — #I wouldnt add a dropdown menu next to each item. I would put a radio next to each drink and then have 2 buttons at the bottom of the page that say move up/move down. This way you can controll the lagitimacy of the movement and will keep the user from choosing 8 drinks to be in the 3rd row. If there are to many drinks to move only by one you could add a drop down next to the 2 buttons that allows you to choose how many to move by.

@jogolauthorMar 30.2007 — #now that sounds like a nice idea DARTHTAMPON. could you tell me how the form with the radio- and submit buttons and the foreach or while loop should look like, since more than one entry in the db needs to be updated when an entry is moved up/down?

thanks

@DARTHTAMPONMar 30.2007 — #are you going to allow the movement by only 1 or more? How big is the dataset (how many rows)?

@jogolauthorMar 30.2007 — #there are about 70 drinks in the db right now, so a movement by more than one would be quite nice. thanks for your help.

@DARTHTAMPONMar 30.2007 — #ok this is quasi php so take it more as a howto than an actual script

[code=php]
 $oldPosition = (the old position)
 $newPosition = (the new position)
 
 if ($oldPosition > $newPosition)
 {
 $query = "select * from table where position > $newPosition and position < $oldPosition order by position asc";
 $num=mysql_numrows($result);
 $query = "update table set position = $newPosition where tableid = ".mysql_result($result,$num-1,"positionid");
 $i=0;
 while ($i < $num-1) 
 {
 $query = "update table set position = ".mysql_result($result,$i,"position")+1." where tableid = ".mysql_result($result,$i,"positionid");
 $i++;
 }
 
 }
 else
 {
 #do exactly oppisit of what was done above.
 }
 [/code]

@jogolauthorMar 30.2007 — #ok looks like i understand what the code does. i´ll modify it and see if it works. the only question i have right now is how to get the vars $oldPosition and $newPosition out of the form.

i´d try it this way:

value of radiobutton is itemid. then i have a hidden field with the current position. the select dropdown tells it how many rows it should move up, e.g. the value 2. then 2 buttons ("move up", "move down").

e.g. the "move up" button has the value "up".

then the php would do:

if($_POST["up"]){

$newPosition = POST['currentposition'] + POST['dropdown'];

}

else {

$newPosition = POST['currentposition'] - POST['dropdown'];

}

is this an acceptable approach?

@DARTHTAMPONMar 30.2007 — #logicaly speaking that would work fine. But users will think that when moving files up that the file is moving up in the display and not in the order. so You may want the up button to

$newPosition = POST['currentposition'] - POST['dropdown'];

and the down to

$newPosition = POST['currentposition'] + POST['dropdown'];

unless you are displaying them with highest position first.

@jogolauthorMar 30.2007 — #a yeah sure, i mixed up - and +. thanks!

@jogolauthorApr 02.2007 — #i need help with a while loop please:

item (position)

let´s say

A (1)

B (2)

C (3)

D (4)

E (5)

i move E up by 2 (let´s call this "$value"):

A (1)

B (2)

E (5)($newPosition)

C (3)

D (4)

E (5)($oldPosition)

so i set the position of E to 3 ($oldPosition - $value).

[code=php]
 $query = "update table set position = '$newPosition' where itemid = '$id'";
 mysql_query($query);
 [/code]

A (1)

B (2)

E (3)($newPosition)

C (3)

D (4)

E (5)($oldPosition)

but how to achieve that C and D get updated?

the logic is that row $oldPosition -1 (D) and row $oldPosition -2 (C) need to get +1. so this 2 (= $value) rows before $oldPosition need to get updated with a while loop.

A (1)

B (2)

E (3)(newPosition)

C (3)(needs +1)

D (4)(needs +1)

E (5)(oldPosition)

my approach is

[code=php]
 
  $i = 0;
  while ($i < ($rows + 1))
   {
   $query = "update table set position = (position+1) where position = ('$oldPosition' - '$i')";
   mysql_query($query);
   $i++;
   }
 [/code]

but the problem is the loop sets E(3) plus 1 aswell so the end result is

A (1)

B (2)

E (4)

C (4)

D (5)

E (6)

any help would be appreciated. thanks!

@DARTHTAMPONApr 02.2007 — #try making your sql statement like this

$query = "update table set position = (position+1) where position = ('$oldPosition' - '$i') and itemid <> '$id'"

@jogolauthorApr 02.2007 — #hey, how easy.. and it works... thanks!

Also in #PHP _↴

Need Help reading and display data from a txt file - PHP [RESOLVED] MySQL select based on current date in date field [RESOLVED] PHP IF Variable Contains...

Success!

Help @jogol spread the word by sharing this article on Twitter...

Tweet This

[RESOLVED] how to re-order db entries

15 Comments(s) _↴

Also in #PHP _↴

Success!

Social

Version

[RESOLVED] how to re-order db entries

15 Comments(s) ↴

Also in #PHP ↴

Success!

The web is an endless sea of information. Don't miss the boat... Subscribe!

Social

Version

15 Comments(s) _↴

Also in #PHP _↴