CODEWITHSUNDEEP
phpmysql
code
code

Reputation: 1081

php display results of a COUNT? I think

I'm trying to figure out how to display the username column in the while loop below and also with counting how many hits each user had.

 $query = "SELECT company, COUNT(company) FROM tableName GROUP BY company"; 
 $result = mysql_query($query) or die(mysql_error());

 // Print out result
 while($row = mysql_fetch_array($result)){
 echo "There are ". $row['COUNT(company)'] ."
 ". $row['company'] ." hits total so far";
 echo "<br />";

 }

enter image description here

For example 

 There are 3 facebook hits total so far - user1(1 total), user2(1 total), user3(1 total)
 There are 2 google hits total - user1(2 total)
 There are 6 mircosoft hits total - user3(1 total)
 There are 0 dropbox hits total

I appreciate any help you can provide!

Tim

Upvotes: 1

Views: 45

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269563

The basic data you want comes from:

SELECT company, userid, COUNT(*)
FROM tableName
GROUP BY company, userid;

An approximation of the format you want is:

SELECT company, GROUP_CONCAT(userid, '(', cnt, ' total)')
FROM (SELECT company, userid, COUNT(*) as cnt
      FROM tableName
      GROUP BY company, userid
     ) cu
GROUP BY company;

Of course, this does not have a row for "dropbox". For that functionality, you need a table with the companies of interest and you need to LEFT JOIN it to this query.

As others may note, "mysql" is outdated, you should use mysqli or PDO.

Upvotes: 2

Related Questions