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;
精彩评论