Randomizing a SQL Statement Results

[abelajohnb]

Hell.

I have a need to randomize the results of a MySQL query, and can't seem to get anything to work.

My original statement was:

$query = "SELECT * FROM questions WHERE deleted = '0' ORDER BY question_id RAND() LIMIT 10" or die(mysql_error());

which did not work, as it threw the error:
"You have an error in your SQL syntax near 'RAND() LIMIT 10' at line 1"

So, I discovered that RAND() can not be used in such a manner.

So, I tried:

$query = "SELECT *, rand() AS random FROM questions WHERE deleted = '0' ORDER BY random LIMIT 10";

And that returned 10 entries, however they were not randomized.

I am needing a way to show 10 entries, in a randomized order.

The table has 21 entries.

I am guessin that I will have to query all the results, put them into a randomizer, than select 10 results from them.

That does not fancy me too much.

So, does anybody know of a way to get this to work?

The system is running:
PHP: Version 4.0.4pl1
MySQL: 3.23.22-beta

Many thanks.
JbA

[Anon]

Anybody??

[gregj]

// set $n = to the number of results you want to return
$n = 3;

$result = mysql_query("SELECT * FROM table",$db);
$i = 0;
srand(time());
$rows = mysql_num_rows($result);
$bks = $n;
if ($rows < $n) {
$bks = $rows;
}
while (($i < $bks) && ($rows > 0)){
if ($rows == 1) {
$rndval = 0;
} else {
$rndval = rand(0,$rows-1);
}
$var = mysql_result($result,$rndval,"var");
}