...

View Full Version : Display sql results in random order.



twomt
12-30-2006, 03:27 PM
Hello,

I am not sure how to do the following task:

I want to order by a certain collumns, but if there are rows that contain the same value in the collumn that I do the order by on then I would like to display the results differently everytime I do the query....

For example:


id lvl
1 1
2 2
3 2

Now if I do SELECT * FROM table ORDER by lvl DESC the result will always be:

id lvl
2 2
3 2
1 1

I would like to see the result above change randomly to

id lvl
3 2
2 2
1 1

and perhaps next time the query is executed the result is again

id lvl
2 2
3 2
1 1

Any suggestions (I hope I made it clear enough)?

I have posted this on the MySQL forum as well and with the help of the people there sort of got it to work by ordering on multiple collumns, but this is not 100% what I was looking for. Is there a way to add this random feature using php?

Thanks,
twomt.

dumpfi
12-30-2006, 04:54 PM
$result = mysql_query('SELECT * FROM table ORDER by lvl DESC, id '.((mt_rand(0, 1)) ? 'DESC' : 'ASC'));
// do something with $resultsdumpfi

twomt
12-30-2006, 05:01 PM
$result = mysql_query('SELECT * FROM table ORDER by lvl DESC, id '.((mt_rand(0, 1)) ? 'DESC' : 'ASC'));
// do something with $resultsdumpfi

Not really random then right? It'll either be sorting on the 2nd collumn in ASC or DESC if I understood your code right.

dumpfi
12-30-2006, 08:34 PM
Well, yes. It does sort randomly in ascending or descending order.

If a fully random order is needed, this is probably better:
$result = mysql_query('SELECT * FROM table ORDER by lvl DESC, RAND()');dumpfi

twomt
12-31-2006, 09:02 AM
PERFECT !!

thanks mate.



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum