Jump to content


Photo

Random row in mySQL


  • Please log in to reply
2 replies to this topic

#1 LoneAntiTrust

LoneAntiTrust
  • New Members
  • Pip
  • Newbie
  • 1 posts
  • LocationNew York, USA

Posted 03 January 2003 - 10:48 PM

ok, i got this code which is supposed to select a random row in mysql.

select * from quotes order by rand() limit 1

i have 10 rows in the table, and i seem to get the same row each time! i don\'t understand. maybe after reloading about 50 times i\'ll get one different row, then it will go back to the previous one for another 30 times. i have no idea why it\'s really not randomizing these.

any ideas?

#2 pallevillesen

pallevillesen
  • Members
  • PipPipPip
  • Advanced Member
  • 135 posts
  • LocationDenmark

Posted 06 January 2003 - 04:45 PM

Use a seed value that differs all the time:


select * from quotes order by rand($seed) limit 1


Use some reandom integer in $seed (time or something).

The reason for the problem - probably because you\'re creating a shiny new connection the database each time the page is loaded and the \"not really random\" function in sql returns the same values when a new connection has been initialized (?)....

I think the seed is the solution - BUT remeber that the same seed returns the same random value! It would actually be a lot easier to pick a random row using the following (assuming sql and php or something similar):


$seed = random(10);

select * from quotes limit by $seed, 1;// picks 1 row from $seed


P., denmark

ok, i got this code which is supposed to select a random row in mysql.

select * from quotes order by rand() limit 1

i have 10 rows in the table, and i seem to get the same row each time!  i don\'t understand.  maybe after reloading about 50 times i\'ll get one different row, then it will go back to the previous one for another 30 times.  i have no idea why it\'s really not randomizing these.

any ideas?


Palle Villesen, www.birc.dk [br]Bioinformatics Research Center

#3 pallevillesen

pallevillesen
  • Members
  • PipPipPip
  • Advanced Member
  • 135 posts
  • LocationDenmark

Posted 06 January 2003 - 04:50 PM

Just a quickie:

If using pure sql:


select * from quotes order by rand(time_to_sec(curtime())) limit 1


This would use the number of seconds as seed...

P., denmark
Palle Villesen, www.birc.dk [br]Bioinformatics Research Center




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users