Building on the previous example, this code allows the data to be grouped on the x-axis, pivot style. Many modern SQL engines have a PIVOT function built in. The following technique produces the same result using a two-pass approach that doesn't rely on the PIVOT function.
As with the subtotal example, the user can choose the column to group by, from a pre-defined list, held in an array. The differences in logic flow are:-
To help visualise this, I'm outputting a screen friendly version of the resulting run-time SQL statement below.
Use the drop-down selection boxes to choose row and column grouping fields. The page will auto-reload each time you make a change.
Group | Peru | UK | Equador | USA | Germany | Total Value |
Blandit Company | 0.00 | 0.00 | 0.00 | 587.03 | 0.00 | 587.03 |
Cursus Institute | 624.68 | 0.00 | 0.00 | 0.00 | 0.00 | 624.68 |
Diam Company | 0.00 | 0.00 | 0.00 | 783.90 | 0.00 | 783.90 |
Magna Lorem Incorporated | 0.00 | 957.65 | 0.00 | 0.00 | 0.00 | 957.65 |
Nulla Associates | 0.00 | 987.03 | 0.00 | 0.00 | 0.00 | 987.03 |
Purus Gravida Limited | 0.00 | 0.00 | 921.84 | 0.00 | 0.00 | 921.84 |
Turpis Industries | 0.00 | 0.00 | 0.00 | 0.00 | 868.17 | 868.17 |
Final Totals | 624.68 | 1,944.68 | 921.84 | 1,370.93 | 868.17 | 5,730.30 |
select Customer, sum(if(Country= 'Peru', value, 0)) , sum(if(Country= 'UK', value, 0)) , sum(if(Country= 'Equador', value, 0)) , sum(if(Country= 'USA', value, 0)) , sum(if(Country= 'Germany', value, 0)) from sales group by Customer order by Customer
<?php
//***CONTROLLER CODE
//Read row grouping parameters
$i=1;
$row_sel = array();
while ($param = trim(strip_tags($_GET['rowgroup'.$i]))) {
array_push($row_sel, $param);
$i++;
}
//If no row groupings selected then force 'customer' as primary
if ($i == 1) array_push($row_sel, 1);
$col_sel = trim(strip_tags($_GET['colgroup']));
//If no column grouping then force 'country' as selection
if (strlen($col_sel)==0) $col_sel = 3;
$model = new Model;
$view = new View;
$view->view_output($model, $row_sel, $col_sel);
//***MODEL
class Model {
function fetch_rows($rowgroupings, $colgrouping) {
$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);
//Pass 1 - determine column values
$sql= 'select distinct ' . $colgrouping . ' from sales ';
$rs = $pdo->query($sql);
$rows = $rs->fetchAll(PDO::FETCH_ASSOC);
$col_vals = array();
foreach ($rows as $row) {
array_push($col_vals, $row[$colgrouping]);
}
//Pass 2 - fetch rows
$sql= 'select ' . implode(", ", $rowgroupings);
for ($i = 0; $i < count($col_vals); $i++ ) {
$sql.= ", sum(if(" . $colgrouping . "= '" . $col_vals[$i] . "', value, 0)) ";
}
$sql.= 'from sales ';
$sql.= 'group by ' . implode(", ", $rowgroupings) . ' order by ' .
implode(", ", $rowgroupings);
$rs = $pdo->query($sql);
$rows = $rs->fetchAll(PDO::FETCH_BOTH);
return array($rows, $col_vals);
}
}
//***VIEW
class View {
public $html;
private $rowid;
private $firstrec;
private $subcount;
private $cat;
private $oldcat;
const MAX_LEVELS = 3;
function __construct() {
$this->html = "";
$this->num_cols = 0;
$this->rowid = 1;
//In this example the row and column grouping selections are the same
$this->row_group_titles = array('Item', 'Customer', 'City', 'Country', 'Salesman');
$this->row_group_fields = array('Item', 'Customer', 'City', 'Country', 'Salesman');
$this->col_group_titles = array('Item', 'Customer', 'City', 'Country', 'Salesman');
$this->col_group_fields = array('Item', 'Customer', 'City', 'Country', 'Salesman');
$this->firstrec = true;
$this->subcount = array();
$this->cat = array();
$this->oldcat = array();
}
public function view_output($model, $row_sel, $col_sel) {
//Determine row grouping fields
$row_fields = array();
foreach ($row_sel as &$val) array_push($row_fields, $this->row_group_fields[$val]);
$col_field = $this->col_group_fields[$col_sel];
//Pull row data from model plus array of column titles
list($rows, $col_vals) = $model->fetch_rows($row_fields, $col_field);
$max_level = count($row_sel);
$this->num_cols = count($col_vals);
//Initialise final totals
$this->data[0] = array_fill(0, $this->num_cols, 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.= "Row Group by: ";
for ($a=1; $a <= View::MAX_LEVELS; $a++) {
//Begin selection element
$this->html.= "<select size='1' name='rowgroup$a'
onchange='document.mainform.submit();'>";
//Create an option entry for each grouping field
for ($b=0; $b < count($this->row_group_titles); $b++) {
$group_option = $this->row_group_titles[$b];
$this->html.= "<option ";
if ($max_level>=$a) {
if ($row_sel[$a-1]==$b) $this->html.= "selected ";
}
$this->html.= "value='$b'>$group_option</option>";
}
//End selection element
$this->html.= "</select>";
}
//Create a column group selection
$this->html.= "Col Group by: <select size='1' name='colgroup'
onchange='document.mainform.submit();'>";
for ($i=0; $i < count($this->col_group_titles); $i++) {
$group_option = $this->col_group_titles[$i];
$this->html.= "<option ";
if ($col_sel==$i) $this->html.= "selected ";
$this->html.= "value='$i'>$group_option</option>";
}
$this->html.= "</select>";
$this->html.= "</form>";
$this->html.= "<table class='subtotals'>";
$this->html.= "<thead>";
$this->html.= "<tr><td>Group</td>";
for ($i=0; $i<$this->num_cols; $i++) {
$this->html.= "<td>". $col_vals[$i] ."</td>";
}
$this->html.= "<td>Total<br />Value</td></tr>";
$this->html.= "</thead>";
foreach ($rows as $row) {
for ($i=1; $i<=$max_level; $i++) {
//The grouping fields are the first in the returned rows
$this->cat[$i] = $row[$i-1];
//First record, copy break field values to old break field values
//in order to prevent summaries.
if ($this->firstrec) {
$this->data[$i] = array_fill(0, $this->num_cols, 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);
}
}
//Detail records accumulate the count for the highest grouping level
//Accumulate data elements from the current row
for ($i=0; $i<$this->num_cols; $i++) {
$this->data[$max_level][$i] += floatval($row[$i + $max_level]);
}
$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);
}
//Only first level visible initially
$style = ($level>1) ? "style='display: none;' " : "";
//Indentation based on level
$indent = (($level-1) * 10)+3;
//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.
if ($level > 0) {
$this->html.= "<tr id='row$this->rowid' $style class='level$level'>";
$this->html.= "<td class='l' style='padding-left: {$indent}px;'>";
//Show a clickable plus/minus to allow expand/collapse of lower level subtotals
if ($level < $max_level) $this->html.= "<a onclick=
'return toggleVisibility($this->rowid);'
href=''><img id='img$this->rowid' src='/images/plus.png'></a>";
$this->html.= $this->oldcat[$level]."</td>";
} else {
$this->html.= "<tfoot><tr>";
$this->html.= "<td class='l'>Final Totals</td>";
}
for ($i=0; $i < $this->num_cols; $i++) {
$this->html.= "<td class='r'>".number_Format($this->data[$level][$i],2)."</td>";
//Add to higher level totals
if ($level>0) $this->data[$level-1][$i] += $this->data[$level][$i];
}
//Row total
$this->html.= "<td class='r'>".number_Format(array_sum($this->data[$level]),2)."</td>";
$this->html.= "</tr>";
//If this was the final total then close the table footer
if ($level == 0) $this->html.= "</tfoot>";
//Increment the row id, reset the grouping field content.
//Accumulate lower level totals if we are not already
//at the lowest level (i.e. final total)
if ($level>0) {
$this->oldcat[$level] = $this->cat[$level]; //Change category value
}
$this->rowid++;
$this->data[$level]=array_fill(0, $this->num_cols, 0);
}
}
?>
The Javascript code is identical to the previous example.
//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;
}