Re-ordering a column using MySQL and PHP

I have a column in a MySQL table, this column represents the order I want each field to be displayed, and I want to be able to change a row's number and if I change that, it'll change all the elements in the table.

for example:

item_name item_order
item1     1
item2     2
item3     3
item4     4
item5     5

and let's say I want item5 to be 1st, than the new开发者_如何学C result set will look like this:

item_name item_order
item5     1
item1     2
item2     3
item3     4
item4     5

Thanks in advance, itai.

---

Why not just use an order by clause in your select queries ?

select *
from your_table
order by item_order

Using an order by clause will allow to specify in which order results must be sorted -- here, in the example I've given, they'll be sorted by ascending item_order.

---

There are many ways to implement such useful feature.
Here is an example of quite easy one, just to give you an idea:

$id = intval($_POST['id']);
if (isset($_POST['up'])) {
  $sort=dbgetone("SELECT sort FROM $table WHERE id=$id");
  $sort2=dbgetone("SELECT max(sort) FROM $table WHERE sort < $sort");
  if ($sort2) $id2=dbgetone("SELECT id FROM $table WHERE sort = $sort2");
} elseif (isset($_POST['down'])) {
  $sort=dbgetone("SELECT sort FROM $table WHERE id=$id");
  $sort2=dbgetone("SELECT min(sort) FROM $table WHERE sort > $sort");
  if ($sort2) $id2=dbgetone("SELECT id FROM $table WHERE sort = $sort2");
}
if ($sort2) {
  $q1="UPDATE $table SET sort=$sort2 WHERE id=$id";
  $q2="UPDATE $table SET sort=$sort WHERE id=$id2";
  dbquery($q1);
  dbquery($q2);
}                                                         

there are "up" and "down" buttons next to each row displayed, to move it one position back or forth

---

use order by

select *
from table
order by item_order

---

If you want to have the number run consequtive than you need to make an update statement as shown below.

If you make column item_order a decimal(10,1) instead of a int you can really easy renumber the ranking numbers.

item_name item_order  
item1     1  
item2     2  
item3     3  
item4     4  
item5     5  

update mytable set item_order = 0.5 where item_name = item5

item_name item_order  
item1     1  
item2     2  
item3     3  
item4     4  
item5     0.5

After that you can reorder the item_order in proper integer order like so

set @order = 1;
update mytable 
  set item_order = @order:= @order + 1 
  order by item_order asc;

Edit

Of course you can keep the ints and only use even numbers for the ranking and use odd numbers to insert a changed ranking in between somewhere. Then you'd use the following query to update...

set @order = 2;
update mytable 
  set item_order = @order:= @order + 2 
  order by item_order asc;