Below is the MySQL table "products".
Products Table:
| id | name | type | price |
|---|---|---|---|
| 123451 | Park's Great Hits | Music | 19.99 |
| 123452 | Silly Puddy | Toy | 3.99 |
| 123453 | Playstation | Toy | 89.95 |
| 123454 | Men's T-Shirt | Clothing | 32.50 |
| 123455 | Blouse | Clothing | 34.97 |
| 123456 | Electronica 2002 | Music | 3.99 |
| 123457 | Country Tunes | Music | 21.55 |
| 123458 | Watermelon | Food | 8.73 |
MySQL COUNT - Counting Records
The COUNT function is an aggregate function that simply counts all the items that are in a group. The "products" table that is displayed above has several products of various types. One use of COUNT might be to find out how many items of each type there are in the table.
Just as we did in the aggregate introduction lesson, we are going to GROUP BY type to create four groups: Music, Toy, Clothing and Food. For a slight change of pace, let's count the name column to find how many products there are per type.
PHP and MySQL Code:
<?php
// Make a MySQL Connection
$query = "SELECT type, COUNT(name) FROM products GROUP BY type";
$result = mysql_query($query) or die(mysql_error());
// Print out result
while($row = mysql_fetch_array($result)){
echo "There are ". $row['COUNT(name)'] ." ". $row['type'] ." items.";
echo "<br />";
}
?>
Display:
There are 2 Clothing items.
There are 1 Food items.
There are 3 Music items.
There are 2 Toy items.
There are 1 Food items.
There are 3 Music items.
There are 2 Toy items.