User-sorted list (PHP+MySQL)

[marcfalk]

Hey guys, I have a problem.

 

I have 2 tables in my database, one named ”users” (id, username, email, password, sort_order) and one named ”sources” (id, name, feed, favicon).

 

If I have a site, where a user logs in to decide which rows he wants to see from "sources", how do I make some code which makes it possible to update the "users" > "sort_order" with the ID's from the sources and the order of these sources (you could call it Order-ID's).

 

And worse.. How do I load the information about which sources, and in which order these should be sorted, when the user logs in again?

 

It's not so much about the code, it's more the structure I can't figure out. But, in some way you have to update the "sort_order" with two ID's and a separator, per row from the "sources"-table... something like: 1(ORDER-ID),2(SOURCE-ID) | 2,2 | 3,45 | 4,20 etc. or?

 

I've made a little illustration of what I want, It might help.

 

Any suggestions? Thanks in advance!

[rhodesa]

make another table:

 

source_selections

user_id

source_id

sort_order

 

then you can select the users selects with a join:

SELECT s.* FROM source_selections ss LEFT JOIN sources s ON ss.source_id = s.id WHERE ss.user_id = 2 ORDER BY ss.sort_order