3 column count in PHP

My database is setup like this
Type - Identifier - Name

Currently I am displaying the Type and the count of how many times it appears.  I would like to show next to it the name that has the most occurrences of the type in the database.

This is the code I am using
echo "<table border=1><tr><td><strong>Type</strong></td><td><strong>Count</strong></td></tr>";
$query =  mysql_query("SELECT COUNT(*) as cnt, type FROM db GROUP BY type ORDER BY cnt DESC");

while ($row = mysql_fetch_array($query)) {
	echo "<tr><td>" . $row['type'] . "</td><td>" . $row['cnt'] . "</td></tr>";
}

echo "</table>";

Open in new window


How can I have the sql query count which Name has the most occurrences of type in the above code?

Thanks!
ScotTFOAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
wwwdeveloper2Connect With a Mentor Commented:
Here is a start on how you might achieve this.  I didn't try to set up a demo table to test it.  It should give you the idea of the route I'm taking.  Basically in your while loop I'm going out and doing another query to count the names based on the type you are currently looping through.  I'm trying to order and limit the the results by 1, so you will have the name with the most counts on top and only output that.
 
   
echo "<table border=1><tr><td><strong>Type</strong></td><td><strong>Count</strong></td></tr>";
$query =  mysql_query("SELECT COUNT(*) as cnt, type FROM db GROUP BY type ORDER BY cnt DESC");

while ($row = mysql_fetch_array($query)) {
	echo "<tr><td>" . $row['type'] . "</td><td>" . $row['cnt'];
        
	$queryNames =  mysql_query("SELECT COUNT($row['name']) as nameCnt, name FROM db where type=$row['type'] order by nameCnt desc LIMIT 1");

	while ($rowName = mysql_fetch_array($queryNames)) {
	echo "<td>" . $rowName['name'] . "</td>";
	}
	echo "</td></tr>";

}

echo "</table>";

Open in new window

0
 
Ray PaseurCommented:
Uhh, would you want to add the column name for the "name" to the query?  Then echo $row["name"] or something like that?
0
 
ScotTFOAuthor Commented:
Yeah..just trying to add a 3rd column but not sure how to do a second count within that SQL query.
0
 
Chris HarteThaumaturgeCommented:
Sounds like the function you require is array_count_values

http://uk.php.net/manual/en/function.array-count-values.php
0
 
Ray PaseurCommented:
Please explain this a little more.  What exactly do you want to count?  If you could give us an example it might be helpful.  Thanks, ~Ray
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.