random images are not random

corvinedesign

so I am trying to select one image out of a database and it will only select the first entry in the database.

SELECT * FROM gallery ORDER BY RAND() LIMIT 1

wont work even though everywhere I go they tell me to use that code. any help

corvinedesign

when i do the query without the limit 1 i can see that it is ordering them at random but the first record is always the same. how can I get it to order them all random

HalfaBee

I tried it and it worked ok on a small table.

The other way is to do this

$res = mysql_query( 'select * from gallery' );
$size = mysql_num_rows( $res );
mysql_data_seek( $res , rand( 0,$size-1 ) );
$row = mysql_fetch_array( $res );

HalfaBee

corvinedesign

the problem is I dont have every entry still in the table i have deleted tons out so that means the id tag is not incremental any more. so num rows does not equal the correct numbers in the id tag. I know others have had this issue. how do i get it to select a different first image.

laserlight

HalfaBee's solution doesnt involve using the indices.

Rather it generates a pseudorandom integer in the range of the number of rows selected, then shifts the internal resource pointer to the row corresponding to this pseudorandom integer.

corvinedesign

after doing some testing it seems that it works some times but not very often. 80% of the time it still pulls the first entry over and over. on some computers it works better than others (macs, PCs) anyones ever had this issue

steadyguy

You can skip the first record:
SELECT *
FROM gallery
ORDER BY RAND()
LIMIT 1,1
This will return the 2nd record ... but if the first record is always the same, you'll never get to see that row.

It may or may not solve your problem, but if you have a numeric column in the table, you can use this funky workaround that might result in more random results:

SELECT , numericColumnRAND() as random
FROM gallery
ORDER BY random,
LIMIT 1; -- (or 1,1)