[PHP] count vs mysql_num_rows
I've got a little function that just checks to see if something is in a mysql db. There are several ways to do this and was curious as to the best way. The following are 2 (simplified) versions that work just fine. If these are the best ways, which of the following is better from a performance standpoint. And if there is a way that is better than one of these, I'd love to know what that is. $query = SELECT count(id) as count FROM wncci_intranet.iAdmin_users WHERE name = '$name' LIMIT 1; $results = mysql_query($query); $row = mysql_fetch_array($results); $count = $row[count]; return $count; OR $query = SELECT id FROM wncci_intranet.iAdmin_users WHERE name = '$name' LIMIT 1; $results = mysql_query($query); $count = mysql_num_rows($results); return $count; Thanks. -- Kevin Murphy Webmaster: Information and Marketing Services Western Nevada College www.wnc.edu 775-445-3326 P.S. Please note that my e-mail and website address have changed from wncc.edu to wnc.edu.
Re: [PHP] count vs mysql_num_rows
On 10/8/07, Kevin Murphy [EMAIL PROTECTED] wrote: I've got a little function that just checks to see if something is in a mysql db. There are several ways to do this and was curious as to the best way. The following are 2 (simplified) versions that work just fine. If these are the best ways, which of the following is better from a performance standpoint. And if there is a way that is better than one of these, I'd love to know what that is. $query = SELECT count(id) as count FROM wncci_intranet.iAdmin_users WHERE name = '$name' LIMIT 1; $results = mysql_query($query); $row = mysql_fetch_array($results); $count = $row[count]; return $count; OR $query = SELECT id FROM wncci_intranet.iAdmin_users WHERE name = '$name' LIMIT 1; $results = mysql_query($query); $count = mysql_num_rows($results); return $count; i would do a select count() from the database. no sense in returning all the row data back to the script just to do a count on it there. that's additional network/socket transfer between the app and the database, then processing on the app side to collect all the rows into memory, etc. you also don't need to do select count(id) as count, just do a select count(id) and do a mysql_fetch_array($results, MYSQL_NUM) or mysql_fetch_row($results) - you don't need to make it an associative array, since it's only one column and the name is irrelevant. mysql_fetch_row() is the best function for what you want. you'll save any additional PHP overhead with different types of arrays being setup. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] count vs mysql_num_rows
Kevin Murphy wrote:
I've got a little function that just checks to see if something is in a
mysql db. There are several ways to do this and was curious as to the
best way. The following are 2 (simplified) versions that work just fine.
If these are the best ways, which of the following is better from a
performance standpoint. And if there is a way that is better than one of
these, I'd love to know what that is.
$query = SELECT count(id) as count FROM wncci_intranet.iAdmin_users
WHERE name = '$name' LIMIT 1;
$results = mysql_query($query);
$row = mysql_fetch_array($results);
$count = $row[count];
return $count;
OR
$query = SELECT id FROM wncci_intranet.iAdmin_users WHERE name =
'$name' LIMIT 1;
$results = mysql_query($query);
$count = mysql_num_rows($results);
return $count;
Thanks.
Is this a joke? You are using a LIMIT 1, so your count is always going to be 1.
But, if you didn't use the LIMIT 1 and really wanted to see how many the results would be found,
then I would do it like this.
$SQL = SELECT count(id)
FROMiAdmin_users
WHERE name = '{$name}';
$results = mysql_query($SQL) OR die('Mysql Error: '.mysql_error());
$count = 0;
if ( $results ) {
list($count) = mysql_fetch_row($results);
}
return $count;
--
Jim Lucas
Some men are born to greatness, some achieve greatness,
and some have greatness thrust upon them.
Twelfth Night, Act II, Scene V
by William Shakespeare
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] count vs mysql_num_rows
Is this a joke? You are using a LIMIT 1, so your count is always going to be 1. No, its not a joke. The answer is not going to always 1, its going to be 1 (the value exists in the DB at least once) or 0 (the value doesn't exist in the DB), that's what I am trying to test for. I don't need to know how many times something exists, just if it does. -- Kevin Murphy Webmaster: Information and Marketing Services Western Nevada College www.wnc.edu 775-445-3326
RE: [PHP] count vs mysql_num_rows
[snip] Is this a joke? You are using a LIMIT 1, so your count is always going to be 1. No, its not a joke. The answer is not going to always 1, its going to be 1 (the value exists in the DB at least once) or 0 (the value doesn't exist in the DB), that's what I am trying to test for. I don't need to know how many times something exists, just if it does. [/snip] If you just need to know if data exists use the query with limit 1 and test to see if 1 === $result['count']. I agree with the poster about not returning all of the rows, it is more efficient to do it this way. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] count vs mysql_num_rows
At 12:47 PM -0700 10/8/07, Kevin Murphy wrote: Is this a joke? You are using a LIMIT 1, so your count is always going to be 1. No, its not a joke. The answer is not going to always 1, its going to be 1 (the value exists in the DB at least once) or 0 (the value doesn't exist in the DB), that's what I am trying to test for. I don't need to know how many times something exists, just if it does. -- Kevin Murphy Webmaster: Information and Marketing Services Western Nevada College www.wnc.edu 775-445-3326 Kevin: Considering what you want to do, you might want to investigate having mysql do it for you, see here: http://dev.mysql.com/doc/refman/5.0/en/insert-on-duplicate.html Cheers, tedd -- --- http://sperling.com http://ancientstones.com http://earthstones.com -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
