Count and list items in the same query

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
PHP_mySQL__Newbie
Forum Commoner
Posts: 29
Joined: Fri Aug 12, 2011 5:40 pm

Count and list items in the same query

Post by PHP_mySQL__Newbie »

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:

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 />";
}
?>
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.
User avatar
requinix
Spammer :|
Posts: 6617
Joined: Wed Oct 15, 2008 2:35 am
Location: WA, USA

Re: Count and list items in the same query

Post by requinix »

The programmatic answer is to not do any grouping, sort by the types (so all items of the same type come together), and do the counting when you output the types and items. Honestly I prefer this method.

The shorter answer is GROUP_CONCAT.
Post Reply