Projects

WebGL Demos
PHP Data Pivot
PHP Data Subtotals
HTML5 Graph
Java NW3D2
JS Code Formatter
HTML5 Clock
Silverlight Gauge
Java NW3D
Java Fireworks
Java Early 3D
Java Snow
Java Dogfight
Java Water Simulation
Java Bump Mapping
Java Elite Ships

Data Pivot

Description

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.

Demonstration - Customer Sales

Use the drop-down selection boxes to choose row and column grouping fields. The page will auto-reload each time you make a change.

Row Group by:                  Col Group by:   
GroupPeruUKEquadorUSAGermanyTotal
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

Live SQL

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 code

<?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);
		} 
	}
?>


Javascript code

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;
}