...

View Full Version : 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:



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
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!



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum