Getting a random record from MySQL

software development

#1

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?


#2

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

  • wil

#3

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)


#4

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

  • wil

#5

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.


#6

Hi -

Does seeding RAND() with a value help?

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

  • Jeff @ DreamHost
  • DH Discussion Forum Admin

#7

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


#8

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