first row never shown in a RAND () LIMIT sql.

mrsocks

hello,

i am trying to get the first row to show up in the results of the statement:

$sql = "SELECT name,sm_logo FROM brands WHERE sm_logo IS NOT NULL and active = 1 ORDER BY RAND() LIMIT 5";

the first row never shows up. there are only 15 rows in the table. if i add a new row with the same information, it shows up no problem in a random order.

MySQL 3.23.53a
PHP 4.2.3

thanks

reidme

Sorry I cant help you answer your question, but reading this post has just answered one of mine, how to pull a random entry from a table. thanks 😃

svnfightsvn

well - maybe the first row is null or active=0 .

Originally posted by mrsocks
hello,

i am trying to get the first row to show up in the results of the statement:

$sql = "SELECT name,sm_logo FROM brands WHERE sm_logo IS NOT NULL and active = 1 ORDER BY RAND() LIMIT 5";

the first row never shows up. there are only 15 rows in the table. if i add a new row with the same information, it shows up no problem in a random order.

MySQL 3.23.53a
PHP 4.2.3

thanks

mrsocks

svnfightsvn:

-i tried the rand()-1 but that didnt do anything ( i will look into what that does though)
-active = 1
-nessary feilds are NOT NULL.

if i copy the row that row does show up in the random order. If make the first row active=0, then the second row does not ever show up.

there are only 12 rows in the table and i am selecting 5 at a time.
i should be able to get it to show once in a while. i can refresh the page 20 times and it never shows. (but as soon as a i create a new row, it shows up randomly)

thanks for the suggestions, but there is something a bit more involved.

svnfightsvn

it might have something to do with your LIMIT operator.
Does it work properly w/o the RAND()?
If so - does it work properly w/o the LIMIT?
This will narrow it down.

I have seen a second parameter to the LIMIT operator - try LIMIT 0,5

mrsocks

yes, i have tried LIMIT 0,5 and that doesnt make any difference.

i did remove the limit and the record does show up then.

reidme

What about using php rand() to acheive your goal?
pasted from php.net:

igge at netpond dot com
30-Oct-2002 05:35

About selecting random rows from a MySQL table:

SELECT * FROM tablename ORDER BY RAND() LIMIT 1

works for small tables, but once the tables grow larger than 300,000 records or so this will be very slow because MySQL will have to process ALL the entries from the table, order them randomly and then return the first row of the ordered result, and this sorting takes long time. Instead you can do it like this (atleast if you have an auto_increment PK):

SELECT MIN(id), MAX(id) FROM tablename;

Fetch the result into $a

$id=rand($a[0],$a[1]);

SELECT * FROM tablename WHERE id>='$d' LIMIT 1