[thelist] PHP/MySQL order by rand()

noah noah at tookish.net
Fri Jan 3 00:13:01 CST 2003


I'm trying to select a random row from a table in a MySQL database using
PHP. The MySQL version is 3.23.53, so I have access to this syntax:

SELECT * FROM quotation ORDER BY RAND()

This works fine from the MySQL command line, but when I do it from a PHP
page it always returns the rows in reverse order, regardless of how many
rows there are in the table. As a result:

SELECT * FROM quotation ORDER BY RAND() LIMIT 1

always returns the last row when done from a PHP page (it works fine from
the command line). Here's the full PHP code:

**********
$quotation_query_string = ("SELECT * FROM quotation ORDER BY RAND() LIMIT 1");
$quotation_query = mysql_query($quotation_query_string);
$quotation_result = mysql_fetch_object($quotation_query);
echo("<q>$quotation_result->quotation</q>");
echo("<cite>$quotation_result->attribution</cite>");
**********

The preceding always returns the last record.

However, if I run the query twice in the PHP page, it returns a random
record. So if I do this:

**********
$temp_quotation_query_string = "SELECT * FROM quotation ORDER BY RAND()
LIMIT 1";
$temp_quotation_query = mysql_query($temp_quotation_query_string);

$quotation_query_string = "SELECT * FROM quotation ORDER BY RAND() LIMIT 1";
$quotation_query = mysql_query($quotation_query_string);
$quotation_result = mysql_fetch_object($quotation_query);
echo("<q>$quotation_result->quotation</q>");
echo("<cite>$quotation_result->attribution</cite>");
**********

It returns a random record.

I'll do this if I have to to make it work, but it seems a little silly. I
must be missing something obvious. Any help would be greatly appreciated.

(BTW, I won't be using "SELECT *" in the final version.)

Cheers,
Noah




More information about the thelist mailing list