On Wed, Nov 16, 2005 at 05:19:52PM +1300, Jasper Bryant-Greene wrote:
> John Taylor-Johnston wrote:
> >My question is simnple. I want to randomly select a row in a mysql
> >table. I have a primary id.
> >
> ><?php
> >$server = "localhost";
> >$user = "foo";
> >$pass = "foo";
> >$db="foo_db";
> >$table="foo_table";
> >$myconnection = mysql_connect($server,$user,$pass);
> >mysql_select_db($db,$myconnection);
> >
> >$sql = ??;
> >
> >$news = mysql_query($sql) or die(print "<font
> >color=red>".mysql_error()."</font>");
> > while ($mydata = mysql_fetch_object($news))
> > {
> >??
> > }
> >?>
> >
>
> If your table isn't too big, it's fine to do:
>
> SELECT * FROM mytable ORDER BY RAND() LIMIT 1
>
> If you've got more than a few 10,000s of rows, you might want to look at
> other ways, like selecting a random row based on the primary key by
> generating a random number in PHP before executing the SQL.
Very good idea on the 10,000+ rows, if you are dealing with large
tables order by rand() isn't very wise, there is one catch though
with using a random value from php.
Assuming you have a auto_increment for the primary key named 'id':
the first thing you need to do is get the max value of it:
select max(id) from table
That isn't the catch, that query should be quicker than ~.0002
seconds or so :) The problem is you have to account for any deleted
records:
$min = 1; // mysql first auto_increment
list($max) = mysql_fetch_row(mysql_query("select max(id) from table"));
do {
$rand = mt_rand($min, $max);
$row = mysql_query("select * from table where id = $rand");
if(mysql_errno() ) {
break; // fook me.
}
} while(! $row);
if ($row) {
// we have a quick random row.
}
Depending on the space between deleted records, this most likely would
be more resource friendly (on large tables) than havning mysql sort
all the records to some tmp table (on disk) then sending only the first
record.
HTH,
Curt.
--
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
