SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Zealot
    Join Date
    Jun 2006
    Posts
    186
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Need some help with MYSQL

    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!

    Thanks,
    Gabe

  2. #2
    Follow Me On Twitter: @djg gold trophysilver trophybronze trophy Dan Grossman's Avatar
    Join Date
    Aug 2000
    Location
    Philadephia, PA
    Posts
    20,578
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    You've come up with a problem that doesn't need a solution.

    When you SELECT the songs for a user's list, ORDER BY order_number, vote_date. That way the order_number will take precedence if it's set, but if they all have the same value, they'll be sorted by vote_date. Until you implement custom ordering, your display won't change.

    Now when you do implement drag-and-drop ordering, set the order column values at that point, when you have the ordering from the user. Now those columns' default value (0? NULL? doesn't matter at all) is replaced with the real ordering, and the lists will be sorted in that order without changing the SELECT query.

  3. #3
    SitePoint Zealot
    Join Date
    Jun 2006
    Posts
    186
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks Dan! Well, that was much simpler then I thought.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •