Closed Thread Icon

Topic awaiting preservation: PHP/MySQL -> order by rand() (Page 1 of 1) Pages that link to <a href="http://ozoneasylum.com/backlink?for=12541" title="Pages that link to Topic awaiting preservation: PHP/MySQL -&amp;gt; order by rand() (Page 1 of 1)" rel="nofollow" >Topic awaiting preservation: PHP/MySQL -&gt; order by rand() <span class="small">(Page 1 of 1)</span>\

 
Gweilo
Bipolar (III) Inmate

From: switzerland
Insane since: Sep 2002

posted posted 12-04-2002 14:37

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

Perfect Thunder
Paranoid (IV) Inmate

From: Milwaukee
Insane since: Oct 2001

posted posted 12-04-2002 15:39

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

code:
$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

code:
foreach ($random_result as $item)
{
echo $item["fieldname"];
echo $item["other_fieldname"];
}



or

code:
for ($i = 0; $i < count($random_result); $i++)
{
echo $random_result[$i]["fieldname"];
// and so forth
}



Hope this solution works.

Gweilo
Bipolar (III) Inmate

From: switzerland
Insane since: Sep 2002

posted posted 12-04-2002 17:28

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?

bitdamaged
Maniac (V) Mad Scientist

From: 100101010011 <-- right about here
Insane since: Mar 2000

posted posted 12-04-2002 18:34

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



.:[ Never resist a perfect moment ]:.

Tyberius Prime
Paranoid (IV) Mad Scientist with Finglongers

From: Germany
Insane since: Sep 2001

posted posted 12-04-2002 21:36

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.

Gweilo
Bipolar (III) Inmate

From: switzerland
Insane since: Sep 2002

posted posted 12-05-2002 08:49

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

Gweilo
Bipolar (III) Inmate

From: switzerland
Insane since: Sep 2002

posted posted 12-05-2002 08:57

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 ?

« BackwardsOnwards »

Show Forum Drop Down Menu