Home Accessibility Courses Twitter The Mouth Facebook Resources Site Map About Us Contact
 
For 2023 (and 2024 ...) - we are now fully retired from IT training.
We have made many, many friends over 25 years of teaching about Python, Tcl, Perl, PHP, Lua, Java, C and C++ - and MySQL, Linux and Solaris/SunOS too. Our training notes are now very much out of date, but due to upward compatability most of our examples remain operational and even relevant ad you are welcome to make us if them "as seen" and at your own risk.

Lisa and I (Graham) now live in what was our training centre in Melksham - happy to meet with former delegates here - but do check ahead before coming round. We are far from inactive - rather, enjoying the times that we are retired but still healthy enough in mind and body to be active!

I am also active in many other area and still look after a lot of web sites - you can find an index ((here))
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
S157 - More MySQL commands
  [158] MySQL - LEFT JOIN and RIGHT JOIN, INNER JOIN and OUTER JOIN - (2004-12-20)
  [159] MySQL - Optimising Selects - (2004-12-21)
  [279] Getting a list of unique values from a MySQL column - (2005-04-14)
  [449] Matching in MySQL - (2005-09-24)
  [494] MySQL - a score of things to remember - (2005-11-12)
  [502] SELECT in MySQL - choosing the rows you want - (2005-11-22)
  [513] MySQL - JOIN or WHERE to link tables correctly? - (2005-12-01)
  [515] MySQL - an FAQ - (2005-12-03)
  [517] An occasional chance, and reducing data to manageable levels - (2005-12-04)
  [567] Combining similar rows from a MySQL database select - (2006-01-17)
  [572] Giving the researcher power over database analysis - (2006-01-22)
  [581] Saving a MySQL query results to your local disc for Excel - (2006-01-29)
  [591] Key facts - SQL and MySQL - (2006-02-04)
  [673] Helicopter views and tartans - (2006-04-06)
  [1213] MySQL - the order of clauses and the order of actions - (2007-06-01)
  [1235] Outputting numbers as words - MySQL with Perl or PHP - (2007-06-17)
  [1331] MySQL joins revisited - (2007-09-03)
  [1574] Joining MySQL tables revisited - finding nonmatching records, etc - (2008-03-15)
  [1735] Finding words and work boundaries (MySQL, Perl, PHP) - (2008-08-03)
  [1904] Ruby, Perl, Linux, MySQL - some training notes - (2008-11-23)
  [2110] MySQL - looking for records in one table that do NOT correspond to records in another table - (2009-03-31)
  [2448] MySQL - efficiency and other topics - (2009-10-10)
  [2643] Relating tables with joins in MySQL - (2010-02-21)
  [2644] Counting rows in joined MySQL tables - (2010-02-22)
  [2645] Optimising and caching your MySQL enquiries - (2010-02-22)
  [2647] Removing duplicates from a MySQL table - (2010-02-22)
  [3061] Databases - why data is split into separate tables, and how to join them - (2010-11-20)
  [3270] SQL - Data v Metadata, and the various stages of data selection - (2011-04-29)
  [4481] Extracting data from backups to restore selected rows from MySQL tables - (2015-05-01)

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

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

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


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
4759 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, 88, 89, 90, 91, 92, 93, 94, 95, 96 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., 2024: 48 Spa Road • Melksham, Wiltshire • United Kingdom • SN12 7NY
PH: 01144 1225 708225 • EMAIL: info@wellho.net • WEB: http://www.wellho.net • SKYPE: wellho

PAGE: http://www.wellho.net/mouth/2259_Gro ... d-PHP.html • PAGE BUILT: Sun Oct 11 16:07:41 2020 • BUILD SYSTEM: JelliaJamb