I can’t help but think of The Count while writing this post… I’m such a big baby!
Alright, in this quickie we’ll talk about the power of the count() function in MySQL (In this example. I’m sure its almost a standard in other SQL systems).
This posts automatically assume that you have basic MySQL experience at the very least and know how to work it up with PHP. So here we go!
Imagine this scenario:
You have a shop’s database and you’re asked to display count of each Item’s category. To clarify, “Mars bar” is “Chocolate” while “Tomato” is “Grocery” and so on.
You can do this (This is a snippet, not the whole PHP code):
$query = "SELECT * FROM Items"; $result = mysql_query($query) or die(mysql_error()); $Chocolate = 0; $Grocery = 0; $Dairy = 0; $Pastry= 0; while ($row = mysql_fetch_array($result)) { if ($row['Category'] == "Chocolate") $Chocolate++; if ($row['Category'] == "Grocery") $Grocery++; if ($row['Category'] == "Dairy") $Dairy++; if ($row['Category'] == "Pastry") $Pastry++; } echo "Chocolate: " . $Chocolate . "\n"; echo "Chocolate: " . $Grocery . "\n"; echo "Chocolate: " . $Dairy . "\n"; echo "Chocolate: " . $Pastry. "\n";
Is this a good practice? HELL NO! This is a really REALLY bad practice!
Why? Because
1) If you have a long list, you’ll get such a long reply which consumes time and resources. This might not be an issue if you have a small database and a little amount of replies. But cause a bottleneck if you have big ones.
2) If you have a lot of categories, you’re not gonna assign each variable for each time and do this. This is just wrong! I mean, Lets say you add a new category, are you going to add a new variable hardcoded?
The power of The Count comes in!
$query = "SELECT Name, COUNT(Category) FROM Items"; $result = mysql_query($query) or die(mysql_error()); while ($row = mysql_fetch_array($result)) { echo $ROW['Name'] . ": " . $row['COUNT(Category)'] . "\n"; }
From 21 lines to 6 (4 if you ignored ‘{‘ and ‘}’).
Kinda as efficient as it gets because you’re building up a dynamic query and listing. As in, if you keep on adding new categories.
But lets say you have a STATISTICS table which logs hits. And you want to count how many hits you had since its start. Easy!
$query = "SELECT COUNT(*) FROM STATISTICS"; $result = mysql_query($query) or die(mysql_error()); $count = mysql_fetch_array($result)); echo "You have " . $count[0] . " Hits so far\n";
4 lines and you’re good to go!
But whats really the difference?
Well, going all “SELECT *” without the count means that you’re getting EVERYTHING from your MySQL server which is fine for few requests and results but not intensive ones even if they’re in the same machine and you’re connecting to “localhost”. The more requests you push and results you get, the more you exhaust it and get slower responses.
The Count “SELECT COUNT(whatever)” asks the MySQL server to give you a result of how many rows are there. So you basically get only that, not the whole rows. Which is the best thing to do for many purposes where you only want rows count. Or at least as far as I know.
PHP quickies will get more intense soon, hope you like’em 😀
Resources:
mysql_num_rows
mysql_fetch_array”
COUNT
MySQL count quick tutorial