[PHP] random row
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))
{
??
}
?
--
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.
?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.
Jasper
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
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.
?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
[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...
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
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]
