If I have a list of database fields:
id order title
1 3 This is a post
2 1 This is another post
3 2 This is also a post
4 4 This is still a post
And I want change the order of these based on order.
If I set order on id #1 to 2 I want order on id #3 to be set to 3.
If I set order on id #4 to 1 I want order on id #1 to be 4, order on id #2 to be 2, order on id #3 to be 3 etc. etc.
How can I do this? I think it should be quite simple but I can't figure it out.
I have searched but I don't know what to search for...
You can do it in SQL with two UPDATE statements:
START TRANSACTION;
SELECT order FROM t WHERE order BETWEEN $new_order AND $old_order FOR UPDATE;
UPDATE t SET order = order + 1 WHERE order BETWEEN $new_order AND $old_order;
UPDATE t SET order = $new_order WHERE id = $id;
COMMIT;
I have locked the table during these updates to prevent concurrency issues (however, note that transactions/locks are only applicable to InnoDB tables).
there are two ways : read SQL doc and use the ORDER BY close in your query
read PHP doc and use usort function
First you have to find the $other_row, the one that has the order value you want to reuse. Then change each row’s value.
$id_to_set = 1;
$order_to_set = 2;
// Find the order that will need to be swapped
$result = mysql_query("select order from xxx where id = $id_to_set");
$row = mysql_fetch_row($result);
// Find the other row that will be modified
$result = mysql_query("select id from xxx where order = $order_to_set");
$other_row = mysql_fetch_row($result);
// Do the actual swapping
mysql_query("update xxxx set order = $order_to_set where id = $id_to_set");
mysql_query("update xxxx set order = " . $row['order'] . " where id = " . $other_row['id']);