For example, I have a table which looks like this :
id | name
1 | Mike
2 | Adam
3 | John
4 | Sarah ...
Now, when I execute query select * from table order by id desc it will output something like this:
4 | Sarah
3 | John
2 | Adam
1 | Mike
Now what do I do if I want to move John's row up or down, or move Adam's row up or down ( with a MySQL query ( I need basic one, just to know from where to start )).
My solution :
First of all, I created another column named orderID which has the same value as id.
Here is an example which moves up a user:
$query = "
SELECT (
SELECT orderID
FROM test WHERE id = 'user id that i want to move up'
) AS user_order,
(
SELECT orderID
FROM test WHERE orderID > user_order
ORDER BY orderID
LIMIT 0,1
) AS nextUser_order
";
$result = mysql_query($query);
$data = mysql_fetch_assoc($result);
$query = "
UPDATE test SET orderID = IF(orderID='{$data[nextUser_order]}',
'{$data[user_order]}', '{$data[nextUser_order]}')
WHERE orderID IN ('{$data[nextUser_order]}', '{$data[user_order]}');
";
$result = mysql_query($query);
Is there a better way to do that?
