MySQL query sorting

Question

I'm trying to order results of a MySQL query based on a users set data in a field under their row.

I have a table called "assigned", set up like this:

id  user_id  item_id
--  -------  -------
1   1        1
2   1        23
3   1        304

I want to be able to order these results based on the users table which will be stored like this:

3,1,2

Any ideas?

Answer 1

You should never assume that rows in a table within a relational database have any implicit ordering. i.e. while today 'SELECT * FROM users' might return the data in the order you want, it may be different tomorrow.

The same applies in principle to columns although MySQL is unusual compared with other relational databases in that it does allow you to explicitly state a column order.

Add a column to the users table indicating the ordinal value and sort on that with a join to your assigned table.

Answer 2

You should use the keyword ORDER BY to get the results ordered the way you want :

SELECT column_name(s)
FROM table_name
ORDER BY column_name(s) ASC|DESC

in your case, you would

ORDER BY user_id

If it doesn't answer your question, please tell us more precisely what you want.

Max

Answer 3

you can use the FIELD option:

SELECT * FROM assigned ORDER BY FIELD(id,3,1,2)

where id is the field of the table for which you want to order and the followings are the ids ordered.