This is a code snippet that I wrote years ago and have used variations of in my day job many, many times. It provides a view of a dataset with dynamic subtotalling. An array of field names/titles is used to drive both the form selection boxes and necessary grouping logic. In this example, a static object property is used to control the number of grouping levels.
The code is based around a MVC (Model-View-Controller) pattern. A summary of the logic flow:-
Use the drop-down selection boxes to choose grouping fields. The page will auto-reload each time you make a change.
Item | Customer | City | Country | Salesman | Value |
---|---|---|---|---|---|
ABC123 | Cursus Institute | Sierra Gorda | Peru | Brooke Sanchez | 4.26 |
ABC100 | Magna Lorem Incorporated | Penrith | UK | Elton Schneider | 19.15 |
ABC200 | Purus Gravida Limited | Cabano | Equador | Calvin Melton | 35.88 |
AAA001 | Nulla Associates | Penrith | UK | Dolan Wolf | 96.18 |
AAA010 | Magna Lorem Incorporated | Penrith | UK | Elton Schneider | 46.73 |
ABC025 | Diam Company | Moose Jaw | USA | Calvin Melton | 90.59 |
AAB500 | Purus Gravida Limited | Cabano | Equador | Elton Schneider | 53.14 |
AAA005 | Turpis Industries | Guben | Germany | Elton Schneider | 76.21 |
ABC025 | Diam Company | Moose Jaw | USA | Leroy Irwin | 57.72 |
AAB001 | Turpis Industries | Guben | Germany | Calvin Melton | 55.31 |
ABC050 | Diam Company | Moose Jaw | USA | Dolan Wolf | 60.56 |
AAB001 | Nulla Associates | Penrith | UK | Brooke Sanchez | 75.03 |
ABC050 | Nulla Associates | Penrith | UK | Elton Schneider | 82.86 |
ABC123 | Diam Company | Moose Jaw | USA | Brooke Sanchez | 59.17 |
ABC123 | Diam Company | Moose Jaw | USA | Leroy Irwin | 4.87 |
ABC200 | Cursus Institute | Sierra Gorda | Peru | Leandra Lynn | 48.32 |
ABC100 | Magna Lorem Incorporated | Penrith | UK | Dolan Wolf | 10.23 |
ABB010 | Cursus Institute | Sierra Gorda | Peru | Calvin Melton | 56.40 |
AAA100 | Nulla Associates | Penrith | UK | Leroy Irwin | 52.68 |
ABB250 | Turpis Industries | Guben | Germany | Calvin Melton | 38.58 |
ABC050 | Blandit Company | Pike Creek | USA | Calvin Melton | 28.17 |
AAA005 | Turpis Industries | Guben | Germany | Elton Schneider | 69.51 |
AAB001 | Diam Company | Moose Jaw | USA | Dolan Wolf | 58.31 |
AAB001 | Magna Lorem Incorporated | Penrith | UK | Elton Schneider | 39.02 |
ABC123 | Turpis Industries | Guben | Germany | Brooke Sanchez | 93.20 |
ABC025 | Diam Company | Moose Jaw | USA | Leandra Lynn | 53.12 |
ABC025 | Diam Company | Moose Jaw | USA | Leandra Lynn | 21.66 |
ABC100 | Blandit Company | Pike Creek | USA | Calvin Melton | 55.41 |
AAA100 | Magna Lorem Incorporated | Penrith | UK | Leroy Irwin | 90.85 |
AAB500 | Magna Lorem Incorporated | Penrith | UK | Dolan Wolf | 54.85 |
ABC250 | Diam Company | Moose Jaw | USA | Dolan Wolf | 51.14 |
AAB500 | Blandit Company | Pike Creek | USA | Brooke Sanchez | 68.94 |
ABB010 | Purus Gravida Limited | Cabano | Equador | Leroy Irwin | 95.58 |
ABB010 | Blandit Company | Pike Creek | USA | Leroy Irwin | 79.22 |
AAB001 | Purus Gravida Limited | Cabano | Equador | Leroy Irwin | 29.49 |
AAB001 | Purus Gravida Limited | Cabano | Equador | Leandra Lynn | 98.17 |
AAB001 | Turpis Industries | Guben | Germany | Elton Schneider | 36.64 |
ABC200 | Magna Lorem Incorporated | Penrith | UK | Dolan Wolf | 50.49 |
AAA001 | Nulla Associates | Penrith | UK | Leandra Lynn | 71.67 |
ABC025 | Blandit Company | Pike Creek | USA | Leroy Irwin | 62.79 |
AAA100 | Turpis Industries | Guben | Germany | Leandra Lynn | 56.94 |
ABC250 | Blandit Company | Pike Creek | USA | Dolan Wolf | 72.56 |
AAB001 | Diam Company | Moose Jaw | USA | Leroy Irwin | 91.76 |
ABC123 | Cursus Institute | Sierra Gorda | Peru | Brooke Sanchez | 43.61 |
ABC250 | Turpis Industries | Guben | Germany | Elton Schneider | 79.93 |
ABC001 | Purus Gravida Limited | Cabano | Equador | Calvin Melton | 94.84 |
ABC001 | Diam Company | Moose Jaw | USA | Calvin Melton | 62.79 |
AAB001 | Nulla Associates | Penrith | UK | Calvin Melton | 47.36 |
ABB250 | Nulla Associates | Penrith | UK | Calvin Melton | 69.46 |
ABB250 | Magna Lorem Incorporated | Penrith | UK | Dolan Wolf | 73.10 |
ABC001 | Nulla Associates | Penrith | UK | Dolan Wolf | 78.65 |
ABC050 | Cursus Institute | Sierra Gorda | Peru | Leandra Lynn | 68.60 |
AAA010 | Nulla Associates | Penrith | UK | Dolan Wolf | 91.67 |
ABB250 | Blandit Company | Pike Creek | USA | Leandra Lynn | 31.38 |
ABC001 | Blandit Company | Pike Creek | USA | Elton Schneider | 19.95 |
ABB010 | Magna Lorem Incorporated | Penrith | UK | Dolan Wolf | 52.56 |
ABC050 | Cursus Institute | Sierra Gorda | Peru | Calvin Melton | 79.87 |
AAA010 | Magna Lorem Incorporated | Penrith | UK | Leroy Irwin | 24.87 |
AAA100 | Magna Lorem Incorporated | Penrith | UK | Leroy Irwin | 11.93 |
ABB010 | Turpis Industries | Guben | Germany | Calvin Melton | 56.07 |
ABC001 | Blandit Company | Pike Creek | USA | Elton Schneider | 94.41 |
AAB001 | Magna Lorem Incorporated | Penrith | UK | Calvin Melton | 3.73 |
AAA001 | Cursus Institute | Sierra Gorda | Peru | Elton Schneider | 90.27 |
AAA010 | Turpis Industries | Guben | Germany | Brooke Sanchez | 12.38 |
AAA001 | Magna Lorem Incorporated | Penrith | UK | Dolan Wolf | 87.53 |
ABC250 | Turpis Industries | Guben | Germany | Leroy Irwin | 73.03 |
ABC250 | Nulla Associates | Penrith | UK | Brooke Sanchez | 64.99 |
AAA001 | Purus Gravida Limited | Cabano | Equador | Dolan Wolf | 90.08 |
AAA001 | Turpis Industries | Guben | Germany | Calvin Melton | 64.28 |
ABC100 | Diam Company | Moose Jaw | USA | Dolan Wolf | 59.58 |
ABC001 | Purus Gravida Limited | Cabano | Equador | Dolan Wolf | 77.37 |
ABB010 | Magna Lorem Incorporated | Penrith | UK | Dolan Wolf | 61.92 |
ABC025 | Magna Lorem Incorporated | Penrith | UK | Leandra Lynn | 45.37 |
ABC100 | Blandit Company | Pike Creek | USA | Leandra Lynn | 68.81 |
ABC050 | Purus Gravida Limited | Cabano | Equador | Dolan Wolf | 67.78 |
ABC123 | Magna Lorem Incorporated | Penrith | UK | Brooke Sanchez | 59.74 |
AAA001 | Nulla Associates | Penrith | UK | Leandra Lynn | 30.04 |
ABB250 | Cursus Institute | Sierra Gorda | Peru | Brooke Sanchez | 51.68 |
AAA001 | Cursus Institute | Sierra Gorda | Peru | Elton Schneider | 35.54 |
ABC250 | Nulla Associates | Penrith | UK | Calvin Melton | 42.32 |
ABC025 | Magna Lorem Incorporated | Penrith | UK | Leroy Irwin | 57.94 |
ABB250 | Diam Company | Moose Jaw | USA | Leroy Irwin | 64.53 |
ABC250 | Turpis Industries | Guben | Germany | Leandra Lynn | 1.86 |
ABC100 | Nulla Associates | Penrith | UK | Brooke Sanchez | 55.28 |
AAA100 | Purus Gravida Limited | Cabano | Equador | Elton Schneider | 73.01 |
AAA100 | Nulla Associates | Penrith | UK | Leroy Irwin | 33.94 |
ABC250 | Turpis Industries | Guben | Germany | Leroy Irwin | 71.64 |
ABC100 | Nulla Associates | Penrith | UK | Dolan Wolf | 94.90 |
ABC250 | Purus Gravida Limited | Cabano | Equador | Calvin Melton | 84.36 |
ABC123 | Cursus Institute | Sierra Gorda | Peru | Brooke Sanchez | 16.62 |
ABA005 | Cursus Institute | Sierra Gorda | Peru | Brooke Sanchez | 99.39 |
ABC250 | Purus Gravida Limited | Cabano | Equador | Elton Schneider | 71.03 |
ABC123 | Cursus Institute | Sierra Gorda | Peru | Leroy Irwin | 30.12 |
AAA001 | Magna Lorem Incorporated | Penrith | UK | Leandra Lynn | 63.30 |
ABC250 | Diam Company | Moose Jaw | USA | Elton Schneider | 48.10 |
AAA100 | Magna Lorem Incorporated | Penrith | UK | Calvin Melton | 66.99 |
ABB010 | Magna Lorem Incorporated | Penrith | UK | Leandra Lynn | 37.35 |
ABB250 | Turpis Industries | Guben | Germany | Calvin Melton | 82.59 |
ABB250 | Purus Gravida Limited | Cabano | Equador | Leandra Lynn | 51.11 |
AAA005 | Blandit Company | Pike Creek | USA | Leandra Lynn | 5.39 |
Final Totals | 5,730.30 |
<?php
//***CONTROLLER CODE
//Read grouping parameters
$i=1;
$sel = array();
while ($param = trim(strip_tags($_GET['group'.$i]))) {
array_push($sel, $param);
$i++;
}
$model = new Model;
$view = new View;
$view->view_output($model, $sel);
//***MODEL
class Model {
function fetch_rows($groupings) {
$db = "";
$user = "";
$pass = "";
$host = 'mysql15.namesco.net';
$charset = 'utf8';
$dsn = "mysql:host=$host;dbname=$db;charset=$charset";
try {
$pdo = new PDO($dsn, $user, $pass);
} catch (PDOException $e) {
echo 'Connection failed: ' . $e->getMessage();
exit;
}
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
//Build query string
$sql = "SELECT Item, Customer, City, Country, Salesman, Value FROM sales ";
if ($groupings) $sql .= " order by " . implode(", ", $groupings);
//Execute and return result set
$rs = $pdo->query($sql);
$rows = $rs->fetchAll(PDO::FETCH_ASSOC);
return $rows;
}
}
//***VIEW
class View {
public $html;
private $rowid;
private $firstrec;
private $subtot;
private $cat;
private $oldcat;
private $group_titles;
private $group_fields;
const MAX_LEVELS = 3;
function __construct() {
$this->html = "";
$this->rowid = 1;
$this->group_titles = array('[None]', 'Item', 'Customer', 'City', 'Country', 'Salesman');
$this->group_fields = array('', 'Item', 'Customer', 'City', 'Country', 'Salesman');
$this->firstrec = true;
$this->subtot = array();
$this->cat = array();
$this->oldcat = array();
}
public function view_output($model, $sel) {
//Determine grouping fields
$sel_fields = array();
foreach ($sel as &$val) array_push($sel_fields, $this->group_fields[$val]);
//Pull data from model
$rows = $model->fetch_rows($sel_fields);
$max_level = count($sel);
$detail_level = $max_level + 1;
//Initialise final total array element
$this->subtot[0] = 0;
$this->html.= "<form style='width: 750px;' name='mainform' method='get'>";
//Create a set of numbered group selection boxes up to the number
//specified in MAX_LEVELS.
//Populate each box with every one of the possible grouping fields.
$this->html.="Subtotal level ";
for ($a=1; $a <= View::MAX_LEVELS; $a++) {
//Begin selection element
$this->html.= "$a: <select size='1' name='group$a'
onchange='document.mainform.submit();'>";
//Create an option entry for each grouping field
for ($b=0; $b < count($this->group_titles); $b++) {
$group_option = $this->group_titles[$b];
$this->html.= "<option ";
if ($max_level>=$a) {
if ($sel[$a-1]==$b) $this->html.= "selected ";
}
$this->html.= "value='$b'>$group_option</option>";
}
//End selection element
$this->html.= "</select>";
}
$this->html.= "</form>";
$this->html.= "<table class='subtotals'>";
$this->html.= "<thead><tr><th>Item</th><th>Customer</th><th>City</th><th>Country</th>
<th>Salesman</th><th>Value</th></tr></thead>";
foreach ($rows as $row) {
for ($i=1; $i<=$max_level; $i++) {
$this->cat[$i] = $row[$sel_fields[$i-1]];
//First record, copy group field values to old group field values
//in order to prevent summaries being triggered.
if ($this->firstrec) {
$this->subtot[$i] = 0;
$this->oldcat[$i] = $this->cat[$i];
}
}
//At each grouping level check for a change in value
for ($i=1; $i<=$max_level; $i++) {
//If a grouping field has changed value then generate subtotals for this level and any sub-levels
if ($this->cat[$i] <> $this->oldcat[$i])
$this->write_subtotal($i, $max_level);
}
//The subtotal array is used to sum the sales value for each group.
$this->subtot[$max_level] += $row['Value'];
//Process the detail records.
//Check whether grouping is being used. If not then just write a plain table row.
$this->html.= ($max_level) ? "<tr style='display: none;'
id='row$this->rowid' class='level$detail_level'>" : "<tr>";
$this->html.= "<td>".$row['Item']."</td>";
$this->html.= "<td>".$row['Customer']."</td>";
$this->html.= "<td>".$row['City']."</td>";
$this->html.= "<td>".$row['Country']."</td>";
$this->html.= "<td>".$row['Salesman']."</td>";
$this->html.= "<td class='r'>".number_format($row['Value'],2)."</td>";
$this->html.= "</tr>";
$this->rowid++;
$this->firstrec=false;
}
//End of records, write final set of subtotals as needed
$this->write_subtotal(0, $max_level);
//Close table
$this->html.="</table>";
//Output view
echo $this->html;
}
private function write_subtotal($level, $max_level = 0) {
//If this level isn't the highest then recursively process all higher levels first
if ($level < $max_level) $this->write_subtotal($level+1, $max_level);
//By default all levels should be visible from the outset.
$style = ($level>1) ? "style='display: none;' " : ""; //Only first level visible
//Indentation based on level
$indent = ($level-1) * 10;
//Output the subtotal, storing the level in the class name
//and the row number in the row id.
//The plus/minus image has an 'onclick' event to toggle visibility.
$this->html.= "<tr id='row$this->rowid' $style class='level$level'>";
if ($level > 0) {
$this->html.= "<th style='text-align: left; padding-left:
{$indent}px;' colspan='5'><a onclick='return
toggleVisibility($this->rowid);' href=''>
<img id='img$this->rowid' src='/images/plus.png'></a>".
$this->oldcat[$level]."</th><th class='r'>".
number_format($this->subtot[$level],2)."</th>";
} else {
$this->html.= "<th style='text-align: left;' colspan='5'>Final Totals</th>
<th class='r'>".number_format($this->subtot[$level],2)."</th>";
}
$this->html.= "</tr>";
//Accumulate lower level totals if we are not already at the
//lowest level (i.e. final total)
if ($level>0) {
$this->subtot[$level-1] += $this->subtot[$level];
$this->oldcat[$level] = $this->cat[$level]; //Change category value
}
//Incrment the row id, and reset the group record count.
$this->rowid++;
$this->subtot[$level]=0;
}
}
?>
//Respond to user clicking on plus/minus symbols by showing/hiding rows as appropriate
function toggleVisibility(rowid) {
var makevisible;
//Check for the existence of the row and its related toggle image
var togglerow = document.getElementById("row"+rowid);
var imgsrc = document.getElementById("img"+rowid).src;
if (togglerow) {
//Using the source of the image clicked on, determine whether we are
//showing or hiding rows. Also, toggle the image.
if (/plus/.test(imgsrc)) {
document.getElementById("img"+rowid).src=imgsrc.replace(/plus/, "minus");
makevisible = true;
} else {
document.getElementById("img"+rowid).src=imgsrc.replace(/minus/, "plus");
makevisible = false;
}
//Establish the grouping level of the row that the user clicked on
//by stripping the word 'level' from the row class.
var togglelevel=parseInt(togglerow.className.replace(/level/,""));
//Walk backward through the table rows until we either reach the beginning of the table
//or a row with the same/lower grouping level.
for (var i=rowid-1; i>0; i--) {
var row=document.getElementById("row"+i);
if (row) {
var level = parseInt(row.className.replace(/level/,""));
//Early exit test - if level found to be same or less than toggling row
//then exit, no more child rows to toggle
if (level<=togglelevel) break;
if (makevisible) {
//We are making rows visible
//Only make visible rows with the next highest grouping level
if (level==togglelevel+1) {
document.getElementById("row"+i).style.display="";
}
} else {
//We are making rows invisible
//Hide all rows with a higher grouping level
if (level>togglelevel) {
document.getElementById("row"+i).style.display="none";
//Check for toggle images that need to
//be reset to a 'plus' symbol
if (document.getElementById("img"+i)) {
document.getElementById("img"+i).src=imgsrc.replace
(/minus/, "plus");
}
}
}
}
}
}
return false;
}