Need Help With Advanced PHP/MySQL query.. Brainstorm with me?

Discussion in 'PHP' started by retwedia, Dec 30, 2011.

0
  1. #1
    I'm developing an internet interactive radio station. When a user logs in I do a mysql query to fetch the data from the songs table in random order.
    Each song has an artist ID. I want to some how fetch the data in random order but avoid the same artist playing twice in a row..

    Any suggestions? I am stumped..

    Currently;

    
$a = mysql_query("SELECT * FROM `songs` WHERE `song_id` NOT IN (SELECT song FROM `listens` WHERE `user_id` = '$user_id' AND (`time` > '$start' AND `time` < '$end')) AND `song_id` NOT IN (SELECT `song_id` FROM `dislikes` WHERE `user_id` = '$user_id') ORDER BY RAND()");
    PHP:
    The artist id is in the songs table, artist_id
     
    retwedia, Dec 30, 2011 IP
  2. WPC

    WPC Peon

    Messages:
    116
    Likes Received:
    1
    Best Answers:
    0
    Trophy Points:
    0
    #2
    You can do something like add a column for 'hasbeenplayed' and then use rand() with the id your grabbing, and if it has been played update the query to say; 1. and if it's not then play it, etc. Get a butchers paper and draw circles of if's and if nots, and it'll help. Then turn those ifs into a switch or function to check etc.
     
    WPC, Dec 31, 2011 IP
  3. retwedia

    retwedia Member

    Messages:
    196
    Likes Received:
    1
    Best Answers:
    0
    Trophy Points:
    48
    #3
    I'm not sure if I understand fully.
    The above query fetches all the current songs in the database that has not already been played by the logged in user.
    Say the query returns 20 songs, each one having a column with the ID of the artist the song belongs to.

    I want the songs to be returned in random order BUT I also want to avoid two songs with the same artist ID to be played directly after one another.
     
    retwedia, Dec 31, 2011 IP
  4. ThePHPMaster

    ThePHPMaster Well-Known Member

    Messages:
    737
    Likes Received:
    52
    Best Answers:
    33
    Trophy Points:
    150
    #4
    The only way you can do is return 1 song per artist but not in an alternative way (aka playing two songs with same artists one after the other, that needs backend logic filtering):

    
$a = mysql_query("SELECT * FROM `songs` WHERE `song_id` NOT IN (SELECT song FROM `listens` WHERE `user_id` = '$user_id' AND (`time` > '$start' AND `time` < '$end')) AND `song_id` NOT IN (SELECT `song_id` FROM `dislikes` WHERE `user_id` = '$user_id') GROUP BY song_artist_field ORDER BY RAND()");
    PHP:
     
    ThePHPMaster, Dec 31, 2011 IP