Main Content

Grouping rows for a summary report - MySQL and PHP

Archive - Originally posted on "The Horse's Mouth" - 2009-06-27 17:57:09 - Graham Ellis

It's quite quick and easy to write a loop that goes through a mysql result set and displays the content on an HTML page (but remember security of data, size of resulting HTML page, and the need to consider special characters such as <). But what if you want a shorter report, grouping records together in summarising blocks?

One way of grouping records - highly efficient if it provides the facilities you need - is to use the GROUP clause in your MySQL select statement. More flexible but slower, you can group within your web application. This example is in PHP, but the principles are the same for other languages.

Where you are counting records, you start off with an immediate 'problem'. When you have generated your first potential output, based on your first input record, it is exactly how I have described it - a potential output. You can't actually output it until later - when you have read in the next record to see if you need to increment the count, and realised that the next record is part of a new batch to be counted separately.

The solution to this problem? You save the record for output (in my example in the variable $prev_line) and a separate counter, and only when you find data that starts a new batch do you output the old one. Care must be taken to avoid a zero (null) record being output at the very beginning, and to flush the final record from $prev_line once you have completed the processing of your data set.

Here's the method I describe in (tested) source code:

<?php
mysql_connect("localhost","[username]","[password]");
mysql_select_db("[database]");
$rset = mysql_query("select ... order by ...");
 
$html = "";
$prev_line = "";
$tlc = 0;
 
# Fetch each row in turn
 
while ($row = mysql_fetch_row($rset)) {
 
  # Make up a (potential) row
 
  $line = "<tr>";
  foreach ($row as $item) {
    $icook = htmlspecialchars($item);
    if ($icook == "") $icook = " ";
    $line .= "<td>$icook</td>";
    }
 
  # If this is a new row, output any PREVIOUS
  # The store the current as "previous"
 
  if ($prev_line != $line) {
    if ($prev_line != "") {
      $html .= "$prev_line<td>$tlc</td></tr>"; }
    $prev_line = $line;
    $tlc = 1;
 
  # And if not a new row, add one to the count
 
  } else {
    $tlc++;
  }
}
 
# When done, add final row to output
 
if ($prev_line != "") {
  $html .= "$prev_line<td>$tlc</td></tr>";
  }
 
?>
<html>
<head>
<title>IP addresses of posts</title>
</head>
<body>
<table>
<?= $html ?>
</table>
</body>
</html>

You might want to add a further 'special case' to handle a completely empty result set rather than producing a blank table, and you might want to add a set of headers to the table so that you know which column is which.

Note that the algorithm used in this example relies on the MySQL query returning all the records in a grouping in a single clump - you would do this by making sure that your ORDER clause was appropriate. If it is not practical to clump in this way, you would keep all the resultant rows in an associative array, which you would sort prior to generation of the HTML. If you need to do that, it's a further reduction in efficiency (may not be a problem) and a further shift of logic from the MySQL engine to the PHP one.

Here's some sample data - from reporting on forum log files. For privacy reasons, I am only providing sample rows for my own user name ... and I have provided an expanded example which has also noted the first and last timestamp on each IP.

1grahame66.14.122.1341Fri, 26 Sep 2008 02:15:59 +0100Fri, 26 Sep 2008 02:15:59 +0100
1grahame66.59.98.2107Sat, 20 Sep 2008 19:45:12 +0100Sun, 21 Sep 2008 15:19:09 +0100
1grahame68.101.40.1001Sat, 11 Aug 2007 13:33:21 +0100Sat, 11 Aug 2007 13:33:21 +0100
1grahame69.85.104.479Fri, 20 Mar 2009 11:35:18 +0000Sun, 22 Mar 2009 18:49:14 +0000