Topic awaiting preservation: PHP/MySQL -> order by rand() (Page 1 of 1)

I'm using MySQL version 3.23.53a on my server and the random sort function doesn't work for MySQL.
I tried everything:
select *, rand() as r from bla order by r
select * from bla order by rand()
select a,b,c from bla order by rand()
select *,rand() from bla order by 2

.. But the results are always displayed in descending order (oldest first). Is there a possibility to make this work? I know somebody else having exactly the same problem.

Of course... I could pick a random number, jump to that position, save the number, read the row, pick another number, check if it wasn't already picked, jump to that position etc etc. But is there no "normal" way to solve this problem???

Thanks for your help

There's probably an SQL way of doing it, but you can circumvent your problem like this: if you're using PHP, perform your query, then feed your results into a multidimensional array. Then you can srand() to fire up the old random number generator, and shuffle() to randomize the array items.

Relevant code would be along the lines of

$query = "SELECT blah blah blah";

$result = mysql_query($query) or die("Error :" . mysql_error());



$random_result = array();



while ($row = mysql_fetch_array($result))

{

    $random_result[] = $row;

}



srand();

shuffle($random_result);

Better look up the srand() and shuffle() functions, I've never used them myself, so I could be off on the syntax.

Then you can loop through the randomized multidimensional array with

foreach ($random_result as $item)

{

    echo $item["fieldname"];

    echo $item["other_fieldname"];

}

or

for ($i = 0; $i < count($random_result); $i++)

{

    echo $random_result[$i]["fieldname"];

    // and so forth

}

Hope this solution works.

Yeah, that's another possibility. But personnally I think my version i posted is better in performance, 'cuz I don't recieve all entries (because my query is limited to max. 15, sorry, I forgot to mention), and don't have to shuffle such a huge array. Another possibility would be, to make an array with all numbers from 1 to [row_num], then shuffle that array, and get the first 15 objects with the named ID's (actually row-numbers)

But is there now way to do this directly? Or does anybody know why this error actaully occurs?

hmm this worked perfect for me
SELECT * FROM `posts` ORDER BY RAND() LIMIT 5



.:[ Never resist a perfect moment ]:.

To quote the Mysql Manual...

"You can't use a column with RAND() values in an ORDER BY clause, because ORDER BY would evaluate the column multiple times. In MySQL Version 3.23, you can, however, do: SELECT * FROM table_name ORDER BY RAND() This is useful to get a random sample of a set SELECT * FROM table1,table2 WHERE a=b AND c<d ORDER BY RAND() LIMIT 1000. Note that a RAND() in a WHERE clause will be re-evaluated every time the WHERE is executed.
"

So' Id supose you could do that.

hmm. I tried that too:
SELECT * FROM table WHERE 1 ORDER BY RAND() LIMIT 12
won't work either. Well, it worked fine on my localhost and I really have no idea why it doesn't work

I did a little bit of testing in PHPmyAdmin. The funny thing is, that it works for some tables, and it doesn't work for others (ie. the one I intended to use order by rand()). Is it possible, that it only works, if the table has at least x tables?
Because the test-tables where it didn't work hat <=3 elements, and the other ones (where it worked) had 20+ elements ?