[thelist] Re: php mysql ORDER BY
Jon Haworth
jhaworth at witanjardine.co.uk
Tue Jan 14 08:45:01 CST 2003
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
More information about the thelist
mailing list