Solved

3 column count in PHP

Posted on 2011-02-14
5
303 Views
Last Modified: 2012-05-11
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!
0
Comment
Question by:ScotTFO
5 Comments
 
LVL 108

Expert Comment

by:Ray Paseur
Comment Utility
Uhh, would you want to add the column name for the "name" to the query?  Then echo $row["name"] or something like that?
0
 

Author Comment

by:ScotTFO
Comment Utility
Yeah..just trying to add a 3rd column but not sure how to do a second count within that SQL query.
0
 
LVL 16

Expert Comment

by:Chris Harte
Comment Utility
Sounds like the function you require is array_count_values

http://uk.php.net/manual/en/function.array-count-values.php
0
 
LVL 108

Expert Comment

by:Ray Paseur
Comment Utility
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
 
LVL 3

Accepted Solution

by:
wwwdeveloper2 earned 500 total points
Comment Utility
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

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Part of the Global Positioning System A geocode (https://developers.google.com/maps/documentation/geocoding/) is the major subset of a GPS coordinate (http://en.wikipedia.org/wiki/Global_Positioning_System), the other parts being the altitude and t…
These days socially coordinated efforts have turned into a critical requirement for enterprises.
The viewer will learn how to dynamically set the form action using jQuery.
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now