[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