[thelist] php mysql ORDER BY

rudy r937 at interlog.com
Mon Jan 13 06:37:00 CST 2003


> SELECT * FROM $table ORDER BY RAND() LIMIT 5
>
> Which is good for selecting 5 random rows,
> but also displays them in a random manner.


there are many ways to do this, but here's a simple one

first, instead of entire rows, select just the primary keys randomly

    select id from $table
        order by rand() limit 5

bring back those 5 ids into your php script, and stuff them into a second
query which gets the rows in the order you want

   select * from $table
      where id in ( 45, 32, 87, 21, 55 )
        order by quizid

easy, eh?

if mysql supported subqueries, the above could be combined into one query

   select * from $table
      where id in ( select id from $table
                          order by rand() limit 5 )
        order by quizid


rudy




More information about the thelist mailing list