Random Row

nurseryboy · 11-06-2002, 05:33 AM

Hey guys,

I just have a quick question about grabbing a random row from a mysql table.. how do you do it?
I have tried "ORDER BY rand()" but it keeps picking the same row for some reason (maybe that reason can be explained).
Any suggestions would be greatly appreciated.
Thanks a lot,

Matthew

bcarl314 · 11-06-2002, 11:35 AM

Try changing ORDER BY to LIMIT.

Just a shot in the dark

Galdo · 11-06-2002, 02:41 PM

LIMIT is used to specify the number of rows returned, so isn't really appropriate here as far as i know. I would suggest something like:

PHP Code:


			
srand((double)microtime()*1000000);

$randnumber = rand(1,NUMBER);

Then select the row using the random number generated:

SELECT * from TABLE WHERE RowID = '$randnumber'

RowID should be some sort of auto incremented field using to identify rows. The 'NUMBER' above should be changed to the total number of rows in your table to avoid creating a random number which is more than your number of rows.

nurseryboy · 11-06-2002, 02:56 PM

Ok,
If I use php to generate a random number, and then grab that number from an autoincremented column on the table, wont that mess up if the number isnt there? It is grabbing information that will be added and deleted as time goes on. So autoincremented numbers will be lost here and there. Ex: The number of rows may be 30, but the highest autoincrement may be 50, because 20 rows have been deleted.
I hope that makes sense. If so, how can I fix that part of it?
Thanks a lot,

Matthew

mordred · 11-06-2002, 08:12 PM

Do you run an old version of MySQL? Because

SELECT * FROM table ORDER BY RAND() LIMIT 1;

works very good for me... and should so since MySQL 3.23.2

nurseryboy · 11-07-2002, 02:14 AM

I am running version 3.23.52.
ORDER BY RAND() keeps grabbing the same row for some reason.
But, when I try to grab a random row from another table, it seems to work fine..
Weird.. lol.

Matthew

Jeewhizz · 11-07-2002, 04:36 PM

how many rows do you have in the table? if its only one row.. what do you think will happen

nurseryboy · 11-07-2002, 05:52 PM

Haha, yeah, I thought of that.
The fist thing I did was make sure I had at least two rows. That would be kinda dumb to be expecting something that wasnt there.. lol.
Good suggestion though. Keeping me on my feet.

Matthew

Jeewhizz · 11-08-2002, 12:59 AM

even if yo have two rows it could still bring up one row continually!

nurseryboy · 11-08-2002, 01:16 AM

Why would it do that? I would think eventually it would pick the other? Maybe?

Matthew

oracleguy · 11-08-2002, 01:27 AM

Quote:

Originally posted by nurseryboy
Why would it do that? I would think eventually it would pick the other? Maybe?

Matthew

It would eventually but it would be much easier to have ten or more rows in there.

So it's probably working just fine.

nurseryboy · 11-08-2002, 02:20 AM

I will add some more rows and try it out.
Thanks for the suggestion. Hopefully thats it.

Matthew

nurseryboy · 11-08-2002, 03:00 AM

Ok, I added a few more and it seems to work. It sticks to one row more than the others, but overall they are random.
Thanks a lot for the help, I think that was the problem.

Matthew

Jeewhizz · 11-08-2002, 02:53 PM

Glad we could help.... if you have two rows it could always go to the same row because its a 50/50 chance

with 10 rows its a 10/90 chance... so more chance of getting another one!