CODEWITHSUNDEEP
phpsqlrandom
starshine
starshine

Reputation: 1

Not receiving random results from RAND() query on My SQLwith PHP

I have a column with 4 rows. I use a query to return a random row but 95% of the time, I get the first row in the table. Very rarely do I get any other row than the first. Is there someething wrong with the way I am using this function in PHP?

//Array with the data to insert
$sql_array = array(
    'rightcolumn'    => 'rightcolumn',
);

// Create the SQL statement
$sql = 'SELECT * FROM ' . rightcolumn . '
         ORDER BY RAND() = ' . 1;

 // Run the query 
 $result = $db->sql_query($sql);

 // $row should hold the data you selected
$row = $db->sql_fetchrow($result);

// Be sure to free the result after a SELECT query                        
 $db->sql_freeresult($result);

// Show we got the result we were looking for
 echo $row['rightcolumn'];

Upvotes: 0

Views: 49

Answers (3)

starshine
starshine

Reputation: 1

I followed the instructions by zerkms and Dagon above.

The statement should be: 

$sql = 'SELECT * FROM ' . rightcolumn . '
        ORDER BY RAND()';

Upvotes: 0

zerkms
zerkms

Reputation: 254896

Keeping in mind that RAND() returns values in [0, 1) range - the RAND() = 1 expression always returns false.

So your ORDER BY clause can be represented as ORDER BY false, which has little sense.

Instead you must to order by a random value, ie:

ORDER BY RAND()

Upvotes: 0

user557846
user557846

Reputation:

ORDER BY RAND() only, remove the =1

ORDER BY RAND is very inefficiency with a large table, 4 rows however is fine

Upvotes: 1

Related Questions