How do I count the number of rows in a database table?

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
tom8521
Forum Commoner
Posts: 25
Joined: Thu May 13, 2010 6:24 am

How do I count the number of rows in a database table?

Post by tom8521 »

How do I count the number of rows in a table named 'mail' where Status=1?

The answer then needs to be returned to the screen. Thanks!
User avatar
liljester
Forum Contributor
Posts: 400
Joined: Tue May 20, 2003 4:49 pm

Re: How do I count the number of rows in a database table?

Post by liljester »

use this query, and print the result in php:

Code: Select all

select count(*) from mail where status = '1';
oscardog
Forum Contributor
Posts: 245
Joined: Thu Oct 23, 2008 4:43 pm

Re: How do I count the number of rows in a database table?

Post by oscardog »

Or you could use:

Code: Select all

$query = mysql_query("SELECT * FROM mail WHERE status = '1'");
$totalRows = mysql_num_rows($query);

echo $totalRows;
User avatar
liljester
Forum Contributor
Posts: 400
Joined: Tue May 20, 2003 4:49 pm

Re: How do I count the number of rows in a database table?

Post by liljester »

the way you suggest is much more inefficent, as the database would return the entire dataset (all the rows) to php. if you only need the number of rows, its best to use the query i suggested.
oscardog
Forum Contributor
Posts: 245
Joined: Thu Oct 23, 2008 4:43 pm

Re: How do I count the number of rows in a database table?

Post by oscardog »

liljester wrote:the way you suggest is much more inefficent, as the database would return the entire dataset (all the rows) to php. if you only need the number of rows, its best to use the query i suggested.
Well obviously you would only need to retrieve 'status' from the database and not all of it. This would make it as efficient(or close) to your solution. There would be no noticeable difference in load time or performance. Well, not unless you're handling millions of rows.
User avatar
liljester
Forum Contributor
Posts: 400
Joined: Tue May 20, 2003 4:49 pm

Re: How do I count the number of rows in a database table?

Post by liljester »

oscardog wrote:
liljester wrote:the way you suggest is much more inefficent, as the database would return the entire dataset (all the rows) to php. if you only need the number of rows, its best to use the query i suggested.
Well obviously you would only need to retrieve 'status' from the database and not all of it. This would make it as efficient(or close) to your solution. There would be no noticeable difference in load time or performance. Well, not unless you're handling millions of rows.
obviously not, as the original poster didnt know that, and you certainly did not put it in your post. also i would like to point out that the tests ive run it was no where near as efficient to do it either way you suggested. i created a simple table with 3 columns: id (int primary key auto_increment), hash (varchar(32)), status (int). i randomly generated 5000 rows, 2477 of wich had a status = '1'. i ran 1000 queries of each type and took an average of the time and measured approx. memory usage. here are the results:

select count(*) from table01 where status = '1';
1000 queries, average time of: 0.000068 sec
memory usage: 2,400 bytes

select status from table01 wehre status = '1';
1000 queries, average time of: 0.000260
memory usage: 3,464 bytes

select * from table01 where status = '1';
1000 queries, average time of: 0.000439
memory usage: 4,536 bytes

while it is a simplistic test, it proves my original statement that the way you suggest is much more inefficent. your original suggestion (select * from mail where status = '1') executes in 646% of the time and uses 89% more the memory of the method i suggested. your second "obvious" suggestion executes in 382% of the time and uses 44% more memory. so while it may not seem like much longer, it is still much more inefficient, and every little bit counts in programming.


my test script:

Code: Select all

<?php

$db_info = array (
	'host'		=> 'localhost',
	'database'	=> 'test',
	'username' 	=> 'user',
	'password'	=> 'pass!',
);

$iterations = 1000;


$db = mysql_connect($db_info['host'], $db_info['username'], $db_info['password']);
mysql_select_db($db_info['database'], $db);


/*
for($i = 0; $i < 5000; $i++) {
	$hash = md5(mt_rand(0, 1000000));
	$status = mt_rand(0,1);
	$query = "insert into table01 (id, hash, status) values ('', '$hash', '$status');";
	mysql_query($query);
	print mysql_error();
}

print"done.";
*/
$base_mem = memory_get_usage();
for($a = 0; $a < $iterations; $a++) {
	$a_start = microtime(true);
	$query = "select count(*) as cnt from table01 where status = '1';";
	$result = mysql_query($query);
	$row = mysql_fetch_assoc($result);
	print $row['cnt'] ."<br />\n";
	$a_time += number_format((microtime(true) - $a_start), 6);
}
$a_mem = memory_get_usage();
$a_average = number_format(($a_time / $iterations), 6);

for($b = 0; $b < $iterations; $b++) {
	$b_start = microtime(true);
	$query = "select status from table01 where status = '1';";
	$result = mysql_query($query);
	print mysql_num_rows($result) ."<br />\n";
	$b_time += number_format((microtime(true) - $b_start), 6);
}
$b_mem = memory_get_usage();
$b_average = number_format(($b_time / $iterations), 6);

for($c = 0; $c < $iterations; $c++) {
	$c_start = microtime(true);
	$query = "select * from table01 where status = '1';";
	$result = mysql_query($query);
	print mysql_num_rows($result) ."<br />\n";	
	$c_time += number_format((microtime(true) - $c_start), 6);
}
$c_mem = memory_get_usage();
$c_average = number_format(($c_time / $iterations), 6);


print"base memory: $base_mem<br />\n";
print"A: $a_average sec, ". ($a_mem - $base_mem) ." bytes<br />\n";
print"B: $b_average sec, ". ($b_mem - $base_mem) ." bytes<br />\n";
print"C: $c_average sec, ". ($c_mem - $base_mem) ." bytes<br />\n";


?>
Post Reply