Re: [PHP] random row
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. > > > > >$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 " >color=red>".mysql_error().""); > > 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
Re: [PHP] random row
John Taylor-Johnston wrote: My question is simnple. I want to randomly select a row in a mysql table. I have a primary id. $news = mysql_query($sql) or die(print "color=red>".mysql_error().""); 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. Jasper -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP] random row
My question is simnple. I want to randomly select a row in a mysql table. I have a primary id. $news = mysql_query($sql) or die(print "color=red>".mysql_error().""); while ($mydata = mysql_fetch_object($news)) { ?? } ?> -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Random Row...
Don't make life difficult for yourself :) SELECT * FROM myTable ORDER BY RAND() LIMIT 0,1 HTH Danny. - Original Message - From: "Brian McGarvie" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Thursday, January 16, 2003 9:37 AM Subject: [PHP] Random Row... > $sql = " > select count(*) as theCount > from myTable > "; > ... > $row_id = rand(1, $myrow["theCount"]); > ... > $sql_randow_row " > select * > from myTable > where id = $row_id > "; > > I assume I'm right with the method above to select a random row? or any > other way? > -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP] Random Row...
$sql = " select count(*) as theCount from myTable "; ... $row_id = rand(1, $myrow["theCount"]); ... $sql_randow_row " select * from myTable where id = $row_id "; I assume I'm right with the method above to select a random row? or any other way? --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.438 / Virus Database: 246 - Release Date: 08/01/03 -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Random Row From Database
On Sunday 11 November 2001 05:18, HEW Staff wrote: > Hi, > > I'm trying to pull a random row from my database and display the > contents. > > I know that the code: > SELECT * FROM TABLENAME WHERE RAND() LIMIT 1 > should work. (1) [EMAIL PROTECTED] has nothing to do with this. So don't crosspost to there. (2) No, it shouldn't work "WHERE RAND ()" means "where some random number evaluates to true (i.e. is nonzero)" you want "ORDER BY RAND () LIMIT 1" -- Christian Reiniger LGDC Webmaster (http://lgdc.sunsite.dk/) ...1000100011010101101010110100111010113... -- PHP General Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
[PHP] Random Row From Database
Hi, I'm trying to pull a random row from my database and display the contents. I know that the code: SELECT * FROM TABLENAME WHERE RAND() LIMIT 1 should work. However, there are 19 rows in the database and only the first row ever shows up. Is there a way to do this to ensure uniqueness? The code I am using at the moment also filters so that rows are pulled from the database given certain other criteria such as: SELECT * FROM TABLENAME WHERE PayerID=$id AND RAND() LIMIT 1 All of the rows in the database contain the same PayerID and this column is not the Primary Key. Any help much appreciated. Jonathan S Hardiman President/CEO Hardiman Enterprises Worldwide -- PHP General Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]