Call table in specific order

[frost]

Hi, I have a table set up with 10 items in it, I want to allow users to customize the order of the items when they are logged in.

 

How can I call the items from the table in the order set by the user?

 

Thanks for any help.

[AbraCadaver]

ORDER BY column_name

[frost]

How will that work?

 

If a user sets his account to 1,4,5,6,7,8,9,10,2,3 how can I select those in that order from the table?

[AbraCadaver]

You would need to save the ids with their placement in a user preference table.  Then you would join the two tables and do something like:

 

items is your table

 

prefs has:  id  placement  user

 

SELECT * from items, prefs WHERE items.id = prefs.id AND prefs.user = something ORDER by placement

 

So it would look something like this:

id   placement
1    1
4    2
5    3
6    4
7    5
8    6
9    7
10   8
2    9
3    10

 

There might be an easier way with a subquery but I think this is pretty standard.

[sasa]

you can order your table with FIND_IN_SET() function

 

SELECT ... FROM ... WHERE .... ORDER BY FIND_IN_SET(id, '1,4,5,6,7,8,9,10,2,3')

[frost]

perfect, thanks for the help guys.

[AbraCadaver]

you can order your table with FIND_IN_SET() function

 

SELECT ... FROM ... WHERE .... ORDER BY FIND_IN_SET(id, '1,4,5,6,7,8,9,10,2,3')

 

Good advice.  However, you still need to store '1,4,5,6,7,8,9,10,2,3' somewhere.  Maybe in a session, but then if they log out and back in they must reorder.

[aabid]

I think it will be good idea to save the order in cookies and don't delete them while they logout. This way when users logback they will find there preferred order.

[AbraCadaver]

I think it will be good idea to save the order in cookies and don't delete them while they logout. This way when users logback they will find there preferred order.

Unless they want to login from another computer or browser.

[frost]

The order will be saved as part of there account settings and will be set on login so no worries there. Thanks again.

[frost]

you can order your table with FIND_IN_SET() function

 

SELECT ... FROM ... WHERE .... ORDER BY FIND_IN_SET(id, '1,4,5,6,7,8,9,10,2,3')

 

Just a note on this:

 

SELECT book_id, book, book_display FROM books WHERE book_status = 'active' ORDER BY FIND_IN_SET(book_id, '13,4,5,6,7,8,9,10,2,3' ) LIMIT 0 , 30

 

this is returning a strange set, the book_id it is returning are (in order given back):  1,12,11,13,4,5,6,7,8,9,10,2,3

 

To get the proper result I had to provide all 13 book ids, this returned them in the proper order. Hopefully that will help someone else.

 

[sasa]

you must filter results to given set and then order

SELECT book_id, book, book_display FROM books WHERE book_status = 'active' AND FIND_IN_SET(book_id, '13,4,5,6,7,8,9,10,2,3' ) ORDER BY FIND_IN_SET(book_id, '13,4,5,6,7,8,9,10,2,3' )