Home Accessibility Courses Twitter The Mouth Facebook Resources Site Map About Us Contact
Grouping rows for a summary report - MySQL and PHP

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

(written 2009-06-27, updated 2009-06-29)

 
Associated topics are indexed as below, or enter http://melksh.am/nnnn for individual articles
G996 - Well House Consultants - Newsletter Scripts
  [3179] Oops - I typed ci not vi, and have lost my file ... - (2011-02-21)
  [2539] Changing Images - (2009-12-17)
  [2433] Controlling, supressing, enabling PHP error messages - (2009-10-02)
  [2360] Error Handling in Lua with assert and pcall - (2009-08-13)
  [2145] Using the internet to remotely check for power failure at home (PHP) - (2009-04-29)
  [2046] Finding variations on a surname - (2009-02-17)
  [1954] mod_rewrite for newcomers - (2008-12-20)
  [1813] Ajax - going Asyncronous and what it means - (2008-09-28)
  [1743] First class functions in Lua lead to powerful OO facilities - (2008-08-07)
  [1665] Factory method example - Perl - (2008-06-04)
  [1601] Replacing the last comma with an and - (2008-04-04)
  [1505] Script to present commonly used images - PHP - (2008-01-13)
  [1487] Efficient PHP applications - framework and example - (2007-12-28)
  [1387] Error logging to file not browser in PHP - (2007-10-11)
  [1321] Resetting session based tests in PHP - (2007-08-26)
  [1217] What are factory and singleton classes? - (2007-06-04)
  [1123] mysqldump and mysqlrestore - (2007-03-30)
  [1066] Final, Finally and Finalize - three special words in Java - (2007-02-05)
  [1001] .pdf files - upload via PHP, store in MySQL, retrieve - (2006-12-19)

H113 - Using MySQL Databases in PHP Pages
  [3455] MySQL, MySQLi, PDO or something else - how best to talk to databases from PHP - (2011-09-24)
  [3035] How to display information from a database within a web page - (2010-11-07)
  [2628] An example of an injection attack using Javascript - (2010-02-08)
  [2561] The future of MySQL - (2010-01-03)
  [2447] MySQL stored procedures / their use on the web from PHP - (2009-10-10)
  [2432] Using print_r in PHP to explore mysql database requests - (2009-10-01)
  [2320] Helping new arrivals find out about source code examples - (2009-08-03)
  [2071] Setting up a MySQL database from PHP - (2009-03-08)
  [1983] Keeping PHP code in database and running it - (2009-01-09)
  [1561] Uploading to a MySQL database through PHP - examples and common questions - (2008-03-02)
  [1010] Dates, times, clickable diarys in PHP - (2006-12-28)
  [947] What is an SQL injection attack? - (2006-11-27)
  [937] Display an image from a MySQL database in a web page via PHP - (2006-11-22)
  [915] Paging through hundreds of entries - (2006-11-05)
  [723] Viewing images held in a MySQL database via PHP - (2006-05-17)
  [666] Database design - get it right from first principles - (2006-04-02)
  [647] Checking for MySQL errors - (2006-03-15)
  [581] Saving a MySQL query results to your local disc for Excel - (2006-01-29)
  [572] Giving the researcher power over database analysis - (2006-01-22)
  [515] MySQL - an FAQ - (2005-12-03)
  [104] mysql_connect or mysql_pconnect in PHP? - (2004-10-30)

Q110 - Object Orientation and General technical topics - Programming Algorithms
  [4325] Learning to program - what are algorithms and design patterns? - (2014-11-22)
  [3662] Finding all the unique lines in a file, using Python or Perl - (2012-03-20)
  [3620] Finding the total, average, minimum and maximum in a program - (2012-02-22)
  [3451] Why would you want to use a Perl hash? - (2011-09-20)
  [3102] AND and OR operators - what is the difference between logical and bitwise varieties? - (2010-12-24)
  [3093] How many toilet rolls - hotel inventory and useage - (2010-12-18)
  [3072] Finding elements common to many lists / arrays - (2010-11-26)
  [3042] Least Common Ancestor - what is it, and a Least Common Ancestor algorithm implemented in Perl - (2010-11-11)
  [2993] Arrays v Lists - what is the difference, why use one or the other - (2010-10-10)
  [2951] Lots of way of converting 3 letter month abbreviations to numbers - (2010-09-10)
  [2894] Sorting people by their names - (2010-07-29)
  [2617] Comparing floating point numbers - a word of caution and a solution - (2010-02-01)
  [2586] And and Or illustrated by locks - (2010-01-17)
  [2509] A life lesson from the accuracy of numbers in Excel and Lua - (2009-11-21)
  [2189] Matching disparate referencing systems (MediaWiki, PHP, also Tcl) - (2009-05-19)
  [1949] Nuclear Physics comes to our web site - (2008-12-17)
  [1840] Validating Credit Card Numbers - (2008-10-14)
  [1391] Ordnance Survey Grid Reference to Latitude / Longitude - (2007-10-14)
  [1187] Updating a page strictly every minute (PHP, Perl) - (2007-05-14)
  [1157] Speed Networking - a great evening and how we arranged it - (2007-04-21)
  [642] How similar are two words - (2006-03-11)
  [227] Bellringing and Programming and Objects and Perl - (2005-02-25)
  [202] Searching for numbers - (2005-02-04)

S157 - More MySQL commands
  [3270] SQL - Data v Metadata, and the various stages of data selection - (2011-04-29)
  [3061] Databases - why data is split into separate tables, and how to join them - (2010-11-20)
  [2647] Removing duplicates from a MySQL table - (2010-02-22)
  [2645] Optimising and caching your MySQL enquiries - (2010-02-22)
  [2644] Counting rows in joined MySQL tables - (2010-02-22)
  [2643] Relating tables with joins in MySQL - (2010-02-21)
  [2448] MySQL - efficiency and other topics - (2009-10-10)
  [2110] MySQL - looking for records in one table that do NOT correspond to records in another table - (2009-03-31)
  [1904] Ruby, Perl, Linux, MySQL - some training notes - (2008-11-23)
  [1735] Finding words and work boundaries (MySQL, Perl, PHP) - (2008-08-03)
  [1574] Joining MySQL tables revisited - finding nonmatching records, etc - (2008-03-15)
  [1331] MySQL joins revisited - (2007-09-03)
  [1235] Outputting numbers as words - MySQL with Perl or PHP - (2007-06-17)
  [1213] MySQL - the order of clauses and the order of actions - (2007-06-01)
  [673] Helicopter views and tartans - (2006-04-06)
  [591] Key facts - SQL and MySQL - (2006-02-04)
  [567] Combining similar rows from a MySQL database select - (2006-01-17)
  [517] An occasional chance, and reducing data to manageable levels - (2005-12-04)
  [513] MySQL - JOIN or WHERE to link tables correctly? - (2005-12-01)
  [502] SELECT in MySQL - choosing the rows you want - (2005-11-22)
  [494] MySQL - a score of things to remember - (2005-11-12)
  [449] Matching in MySQL - (2005-09-24)
  [279] Getting a list of unique values from a MySQL column - (2005-04-14)
  [159] MySQL - Optimising Selects - (2004-12-21)
  [158] MySQL - LEFT JOIN and RIGHT JOIN, INNER JOIN and OUTER JOIN - (2004-12-20)


Back to
Questions I have been asked on answering the phone
Previous and next
or
Horse's mouth home
Forward to
Important - the future of Melksham / Chamber of Commerce
Some other Articles
Mysqldump fails as a cron job - a work around
History is not always pretty
Tcl - nice and nasty
Important - the future of Melksham / Chamber of Commerce
Grouping rows for a summary report - MySQL and PHP
Questions I have been asked on answering the phone
Ramblings on church and state linkage in Melksham
Is it Python past cheetah already?
Past PHP delegates / others - coding help needed for next 3 months
Forum membership - a privilege not a right
4344 posts, page by page
Link to page ... 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 34, 35, 36, 37, 38, 39, 40, 41, 42, 43, 44, 45, 46, 47, 48, 49, 50, 51, 52, 53, 54, 55, 56, 57, 58, 59, 60, 61, 62, 63, 64, 65, 66, 67, 68, 69, 70, 71, 72, 73, 74, 75, 76, 77, 78, 79, 80, 81, 82, 83, 84, 85, 86, 87 at 50 posts per page


This is a page archived from The Horse's Mouth at http://www.wellho.net/horse/ - the diary and writings of Graham Ellis. Every attempt was made to provide current information at the time the page was written, but things do move forward in our business - new software releases, price changes, new techniques. Please check back via our main site for current courses, prices, versions, etc - any mention of a price in "The Horse's Mouth" cannot be taken as an offer to supply at that price.

Link to Ezine home page (for reading).
Link to Blogging home page (to add comments).

You can Add a comment or ranking to this page

© WELL HOUSE CONSULTANTS LTD., 2014: Well House Manor • 48 Spa Road • Melksham, Wiltshire • United Kingdom • SN12 7NY
PH: 01144 1225 708225 • FAX: 01144 1225 899360 • EMAIL: info@wellho.net • WEB: http://www.wellho.net • SKYPE: wellho

PAGE: http://www.wellho.net/mouth/2259_Gro ... d-PHP.html • PAGE BUILT: Thu Sep 18 15:30:25 2014 • BUILD SYSTEM: WomanWithCat