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 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.
You can't do this with SQL, do it programmatically with php. Essentially you run your query, loop through all the records, swapping those that you need to so that artists don't appear consecutively.
It's not ideal for database performance to do sub-queries like that. I'd have the application query for a random song, query separately for the last played song, then if the random song is the same artist as the last played song, repick a random song. Keep repeating until an acceptable song is found.
You could use PHP or you could use stored procedures. But it would be interesting to see how you handle rare cases where you may not have a choice