Count and list items in the same query
Posted: Tue Jun 25, 2013 5:48 pm
This may be a very simple question but I was not able to find an answer. Forgive me if I am posting in the wrong discussion. I have not been on this forum for quite a while.
Table:
Output
There are 2 Clothing items.
There are 1 Food items.
There are 3 Music items.
There are 2 Toy items.
How can I list the names in the same query? I want the output to be:
There are 2 Clothing items: Men's T-shirt, Blouse
There are 1 Food items: Water Melon
There are 3 Music items: Park's Great Hits, Electronica 2002, Country Tunes
There are 2 Toy items: Silly Puddy, Play Station
Thank you in advance.
Table:
Code: Select all
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
Code: Select all
<?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 />";
}
?>
There are 2 Clothing items.
There are 1 Food items.
There are 3 Music items.
There are 2 Toy items.
How can I list the names in the same query? I want the output to be:
There are 2 Clothing items: Men's T-shirt, Blouse
There are 1 Food items: Water Melon
There are 3 Music items: Park's Great Hits, Electronica 2002, Country Tunes
There are 2 Toy items: Silly Puddy, Play Station
Thank you in advance.