Counting database rows in MySQL and PHP

I help out fellow coders a lot on the PHP Freaks forums and I find a lot of new coders grasp PHP quickly but try to do too much in PHP that MySQL can handle in one query. Counting the number of rows in a select query is a good example.

I constantly see people using PHP to count the number of rows in a select statement. I use to do it myself all the time before I learned more about MySQL. Most of the time I see people using mysql_num_rows or a while loop to count the rows in a MySQL data set in PHP. This would only be acceptable if you are actually displaying those rows as output. If all I need from a query is the total number of rows, this is all that should be run:
[pre lang=”php”]
$num_rows = mysql_result(mysql_query(“SELECT count(id) FROM table_name”),0);
[/pre]

“id” is your primary key for the table and “table_name” is the name of your table. Add any WHERE clauses to it if you need to.

If you try out the three methods (while loop, mysql_num_rows and method above) you probably won’t notice a difference for a small table. As your tables get bigger however, the difference in execution time will be noticeable. I had to fix some old code for my employer that used the while method multiple times to count rows and some of these tables had 10k-100k rows. After switching over to the count method above, the difference in load time was a magnitude of seconds. SECONDS! It went from a 5-10 second load to under a second.

So before you starting coding a ton of lines to manipulate a MySQL data set, ask yourself first if MySQL can do that for me. See for info about using the MySQL data functions instead of doing it in PHP.

[carousel keywords=”php, mysql” category=”Books” tag=”damnsemi01-20″ showBorder=”True”]

Continue Reading