php display results of a COUNT? I think

Question

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

Gordon Linoff · Accepted Answer · 2015-06-05 01:57:56Z

2

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.

answered Jun 5, 2015 at 1:57

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

php display results of a COUNT? I think

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related