PHP mysql ordering rows

Question

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?

Answer 1

You should def be using another column which holds the order of the display. id is just a unique identifier. On a relational database moving up and down rows might result in a lot of queries because of the updates on the related tables so I stick with the idea of defining a special row for this purpose.

Answer 2

You can put arbitrary values into an order by clause in a query, but none will work easily for a simple "move up/down a row" type things. You can force certain values to sort first or last, but not "put this value after that value, but let that value go into its natural place". You'd need to have an extra field to specify sorting order.

Answer 3

Changing the id is not what you want to do. You never want to mess with your primary key especially because later down the road it would be easier (and take up much less space, one is an int the other a varchar) to reference your users using their id rather than their name from other tables, it is nice to have a field that you know will never change.

Make another field such as order as a floating point number.

When you move foo between bar and foobar, set foo's order to the average of bar and foobar's order.

Answer 4

SQL tables aren't inherently ordered - they effectively behave like a "bag of rows". If you want the results in a specific order, you will need to sort them (using ORDER BY ...) when you pull them out of the bag -- otherwise, the SQL server will return them in whatever order it feels is easiest. (In this case, they're coming out in the reverse order you inserted them, but that's not guaranteed at all.)

Answer 5

You have to switch IDs, or to order it by another column. That's the only way.