Getting a random record from MySQL

Hey there-

anyone ever have trouble using RAND() or RAND(NOW()) with dreamhosts MySQL servers? I should be able to get a random record set from MYSQL with “SELECT * FROM whatevertable ORDER BY RAND()”.

but it doesn’t seem to work. even used RAND(NOW()) and while it gave me a different record then without NOw() it stops being random.

anyone have any ideas?

Hm, how many records are you returning? If it’s only one, did you include a LIMIT 1 clause after that?

  • wil

Yup - LIMIT 1

The record set I’m going after currently is about 4 records. the results set is always in the same order (ordered by the primary key ID)

OK. Can you paste more of your code for us? What function do you use to grab the data?

  • wil

Here’s the url in action. the part that needs to be random is the Red Devil Pick area - http://www.reddevilgames.com

here’s the query -

SELECT * FROM games g, platform p, ESRB where p.platform_ID=g.platform_ID AND ESRB.esrb_ID=g.esrb_ID AND g.pick=‘Y’ ORDER BY RAND() LIMIT 1

Even breaking the query down to its base SELECT * FROM games ORDER BY RAND() produces the same results.

Hi -

Does seeding RAND() with a value help?

ie. “RAND(UNIX_TIMESTAMP())” ???

  • Jeff @ DreamHost
  • DH Discussion Forum Admin

I recall the same problem. I am sorry, but I don’t have access to that code right now. I believe I got around it by pulling the data from MySQL, and then randomizing the resulting array with PHP.

It worked on this page http://www.walltowallstencils.com/custom/ideas/inspirational.php which presents the same data in a different order each time it loads (if not cached).

Jeff
www.walltowallstencils.com

I tried seeding RAND() with RAND(NOW()) and RAND(UNIX_TIMESTAMP())

both of which return a different result, but neither which is random.

So without a seed I come up with the same game over and over, with now() I get a different game over and over, and trying the UNIX_TIMESTAMP I come up with an even different game over and over.

I’ll have to use php I guess. this should work though so I’m a bit confused on whats wrong.

thanks