Hello, I run a website that, basically, allows people to create a playlist of music by selecting songs from a large directory of music. Currently there is no way for users to sort their list of songs, it's sorted by the date the user selected the song, with the most recently selected song on the top.
I need some help changing the database so that later on I can implement drag and drop song rearrangement.
I don't think this would be hard for someone who knows mysql well, and if anyone can help me this I would greatly appreciate it.
Here's is how the database is set up and what I need to do specifically:
I have a table called "votes" and the columns in the table are "user_id" and "vote_date". I need to insert a new column called "order_number", and have a mysql statement that goes through each user_id (1-2000) and inserts numbers for each row in the order_number column that represent that rows placement in relation to the other rows (with that user_id) when sorted chronologically.
So, if there are 2000 users, there would be 2000 rows with an order_number of 1, since it's looking at each user and inserting these numbers according to the rows chronological position in relation to just the other rows with that user_id.
This is hard for me to wrap my head around, but I feel like the solution would actually be quite simple. If anyone can help that would be greatly appreciated!