[thelist] Re: php mysql ORDER BY

[Jon Haworth]

Hi Dunstan,

> > 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?
>
> Thanks ever so much for that rudy, but I can't remember how to put the
> numbers I've grabbed into the second statement.

Try something like this (untested):

<?php

// empty string to hold IDs
$ids = "";

// grab results
$q = mysql_query("SELECT id FROM $table ORDER BY RAND() LIMIT 5");

// loop through
while ($r = mysql_fetch_array($q)) {

  // concatenate each id (and a comma) to the $ids string
  $ids .= $r["id"]. ",";

}

// chop the trailing "," off $ids
$ids = substr($ids, 0, strlen($ids)-1);

// use $ids in the final query:
$sql = "SELECT * FROM $table WHERE id IN ($ids) ORDER BY quizid";
echo "Your query is: <code>". $sql. "</code>";

?>


HTH
Jon