I have basic PHP/MySql knowledge. I can create basic CRUD applications. I'm trying to slowly create more advanced display of results and I am struggling with how to display "grouped" data.

Here is an issue for a job posting site I am working on.

1) I have a simple table that is storing "how did you hear about us?" information after a visitor apply's for a job position. The table contains only 3 pieces of data (as of right now). Date of application (submitted), how did you hear about us (hdyhau) and job number (file_number).

2) There can be multiple applications for the same job number. For example: 10 entries with the same file_number.

3) I'd like to display the file_number with a count of each hdyhau used. Something similar to below:

Job Number: HDYHAU:
J1234 Google (5)
Yahoo (2)
Amazon (6)
dogpile (8)

J4321 Yahoo (4)
Amazon (2)
Dogpile (3)

Right now I can only display the data this way

J1234 Google
J1234 Google
J1234 Google
J1234 Google
J1234 Google
J1234 Yahoo
J1234 Yahoo
J1234 Amazon
J1234 Amazon
J1234 Amazon
J1234 Amazon
J1234 Amazon
J1234 Amazon
J1234 Dogpile
J1234 Dogpile
J1234 Dogpile
J1234 Dogpile
J1234 Dogpile
J1234 Dogpile
J1234 Dogpile
J1234 Dogpile

Any idea on how to create the query and php code? I've experimented with GROUP BY but it's not displaying the results. I am assuming I need to create an array of some sort....but having trouble knowing how to start it.

Thanks for all the help.

    Does this work?

    SELECT file_number, hdyhau, count(hdyhau) from mytable group by file_number;

    EDIT: No, it doesn't. Not yet ...

      It seems to only count 1 of the hdyhau options.

        should be able to do something like:

        select file_number, hdyhau, count(*) as hdyhau_count
from table_name
group by file_number, hdyhau
order by file_number, hdyhau -- possibly redundant?

        That should give rows something like.

        J1234  Google  12
J1234  Yahoo  8
J5678  Google  15
J5678  Yahoo  99

        If you don't want to repeat the file number in each line, you could load it into a multi-dimension array (though be careful of large result sets making huge arrays), something like

        $results = array();
while($line = $stmt->fetch(PDO::FETCH_ASSOC)) {
    $results[$line['file_number']][$line['hdyhau']] = $line['hdyhau_count'];
}
echo "<ul>\n";
foreach($results as $file => $counts) {
  echo "<li>$file<ul>\n";
  foreach($counts as $key => $value) {
    echo "<li>$key: $value</li>\n"
  }
  echo "</ul></li>\n";
}
echo "</ul>";

        Caveat: completely untested. 🙂

          Yeah, you wouldn't need ORDER BY:

          mysql> select file_number, how_heard,count(how_heard) from applications group by file_number, how_heard;
+-------------+-----------+------------------+
| file_number | how_heard | count(how_heard) |
+-------------+-----------+------------------+
| J123        | Bing      |                2 |
| J123        | Google    |                1 |
| J123        | Yahoo     |                1 |
| J1234       | Bing      |                1 |
| J1234       | Google    |                3 |
| J1234       | Yahoo     |                1 |
+-------------+-----------+------------------+
6 rows in set (0.00 sec)

          Data set:

          mysql> select * from applications;
+--------------+-------------+-----------+
| date_applied | file_number | how_heard |
+--------------+-------------+-----------+
|   1485528329 | J123        | Google    |
|   1485528335 | J123        | Yahoo     |
|   1485528340 | J1234       | Yahoo     |
|   1485528344 | J1234       | Google    |
|   1485528345 | J1234       | Google    |
|   1485528345 | J1234       | Google    |
|   1485528349 | J1234       | Bing      |
|   1485528353 | J123        | Bing      |
|   1485528354 | J123        | Bing      |
+--------------+-------------+-----------+
9 rows in set (0.00 sec)
            Write a Reply...