On Mon, October 3, 2005 2:39 pm, Graham Anderson wrote: > what would be the best way to make MYSQL's RAND() more random ? > > my random result set always seems pretty predictable. The first > record > in the result set always seems to be the same 4 tracks :( > Would adding some kind of random seed like > RAND($randomNumberGenerator) > work ? > If so, how do you implement this ? > > > $sql = "SELECT media.id, > artist.name as artist, > artist.spanish as bio, > artist.purchaseLink, > artist.picture, > media.spanish as trackName, > media.path, > media.quality, > mediaType.id as mediaType > FROM artist, media, playlistItems, mediaType > WHERE playlistItems.playlist_id = $myID > AND playlistItems.media_id = media.id > AND media.artist_id = artist.id > AND media.mediaType_id = mediaType.id > ORDER BY RAND($randomSeed) LIMIT 0, 30"; > > many thanks in advance If you are using a version of MySQL with sub-queries, you may want to try wrapping the whole ORDER BY RAND() around it in an outer query... Maybe the optimization/indexing of the results and the LIMIT are somehow interacting to mess up RAND() Just a guess, really. Depending on the size of your library, you could get ALL the possible tracks, and then use PHP and http://php.net/mt_rand to skip around with mysql_result() at random. If your library of tracks is WAY too big for that, don't do it. You may also want to consider using more than just RAND() to choose tracks. For example, the playlist I generate for a coffeehouse every day has 3 factors: 1. How soon/recently the artist plays/played there. 2. Subjective "Quality" rating by the sound engineer of the track. 3. Random factor http://uncommonground.com/ has a link to the radio right under the photo. [site is getting a facelift soon, so this may change without notice] So GREAT tracks and tracks of artists who are coming back soon or who just played are MORE likely to get played, but there's always a "random factor". There are "multipliers" for each quantity, and the coffeehouse owner can play around with them and "shape" the function to weigh it more heavily toward any of the three factors. In your case, I'd guess that you could consider factors like: 1. How recently was this song in a playlist. 2. How good was this song rated by the user 3. Random None of this will actually "fix" your random issue, but by confusing the order with other factors, might make the lack or randomness less obvious and less bothersome. :-) -- Like Music? http://l-i-e.com/artists.htm -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
