Re-order mysql rows using PHP

Question

| id  | url    | title  | menu_id |
------+--------+--------+----------
| 1   | http://| link 1 | 1       |
| 2   | http://| link 2 | 2       |
| 3   | http://| link 3 | 3       |
| 4   | http://| link 4 | 4       |

Hi, I was wondering if its possible using PHP to reorder the above to something like below. I am trying to generate a menu which can easily be reordered by clicking an up or down arrow. I have no idea where to start. Any help would be much appreciated.

| id  | url    | title  | menu_id |
------+--------+--------+----------
| 1   | http://| link 1 | 2       |
| 2   | http://| link 2 | 4       |
| 3   | http://| link 3 | 3       |
| 4   | http://| link 4 | 1       |

Answer 1

It's definitely possible, but you have to do a little bit of extra logic, it's not something that can be done in one query.

Think about the steps when you click an up/down arrow. For example, let's say that you start with the items in the order 1, 2, 3, 4 and then you click the up arrow on #3. Two things have to happen to change the order to 1, 3, 2, 4:

• #3's position changes to 2 (it gets decreased by 1)
• #2's position changes to 3 (it gets increased by 1)

So you have to change the position for the item that they clicked up on, as well as the one that was previously in its new position. Clicking a down arrow is almost the same, but with the increase/decrease reversed.

Answer 2

I went with using the following code

$menu_id = $_GET['page_id'];
$page_order = $_GET['page_order'];
if ($_GET['do'] == 'up') {


mysql_query("UPDATE menu SET menu_order = '$page_order' +1 WHERE id != '$menu_id' AND menu_order < '$page_order'");
mysql_query("UPDATE menu SET menu_order = menu_order -1 WHERE id = '$menu_id'");    


} else if ($_GET['do'] == 'down') {

mysql_query("UPDATE menu SET menu_order = '$page_order' -1 WHERE id != '$menu_id'");
mysql_query("UPDATE menu SET menu_order = menu_order +1 WHERE id = '$menu_id'");    


}