teamrj

PHP & MYSQL: Cross Tab Reports
" A process or function that combines and/or summarizes data from one or more sources into a concise format for analysis or reporting."

Cross-tabulation of data is a necessity in todays high data environment. I can turn hard to read data into simplified overviews of the data, simply by displaying the data in the most logical format.

In this example we have a football database - with the UEFA rankings of each country from 1999 - 2004.
The object of this demonstration is:

To highlight the effectiveness of crosstabbing data.
Look at the most efficent method approach.

Here we have all the 260 displayed as the results from a simple query:

SELECT country, year, value 
FROM football
ORDER BY year desc, value desc, country asc

Now lets look at cross-tabulating the data.

Instead of just one query as in the previous example, I'm need two. One to give me the data in order of the countries and another to give me the unique headers.

Then I can go throught the main data and check it against the headers data and add it to the cross-tabbed array ready for output.

 Data Query 

SELECT country AS ROWHEADER, year AS COLHEADER, value AS VALUE
FROM football
ORDER BY country asc, year desc, value desc	


 Header Query 

SELECT DISTINCT year AS COLHEADER
FROM football
ORDER BY year desc

Now to cross tab all I have to do is:

Loop the table headers and add to the first row of the crosstab array.
Loop the Data query:

Check if the previous country (ROWHEADER) is the same as the current, If not add the new country to the start of the new row.
Loop the headers and when the year(COLHEADER) from the headers is equal, then add the current value into the array, using the currentRow counter and the current header key for the position to store in the array
Check the current ROWHEADER against the next, if its different then the next item from the recordset is a new countries data

Check the final array for any blanks and add data
Output the crosstabbed array

1) Initialise the variables.
$previousRowHeader = 'teamName';
$row = 0;
$crosstabData = array();
2) Set the first column on the first row (Blank).
$crosstabData[$row][] = ' ';
3) Loop the Headers and put into the first row.
 foreach ($ratingsHeader as $tableHeaderKey => $tableHeaderItem)
 {
  $crosstabData[$row][] = $tableHeaderItem['COLHEADER'];	
 }
 ++$row;
4) Loop the data.
foreach ($ratingsData as $key => $item)
{
4.1) Check the headers if not equal then start a new row.
$currentRowHeader = $item['ROWHEADER'];
$nextRowHeader = $ratingsData[$key+1]['ROWHEADER'];
if ($currentRowHeader != $previousRowHeader)
{
 $crosstabData[$row][]= $currentRowHeader;			
}
4.2) Loop the headers.
foreach ($ratingsHeader as $headerKey => $headerItem)
{
4.3) If equal add the current value to the crosstab data array.
 if ($headerItem['COLHEADER'] == $item['COLHEADER'])
  {
   $crosstabData[$row][$headerKey+1] = number_format($item['VALUE'], 2, '.', '');
  }
}
4.4) Check the next row and increment the row counter if needed.
if ($currentRowHeader != $nextRowHeader)
{
  ++$row;		
}
 $previousRowHeader = $currentRowHeader;
}
5) Loop the crosstab data array and fill in any blanks.
/* Check for any missing figures and fill the blanks with the user defined blank.*/
    foreach ($crosstabData as$crosstabDataKey => $crosstabDataItem)
    {
    foreach ($ratingsHeader as $ratingsHeaderkey => $ratingsHeaderitem)
    {
    if (!$crosstabDataItem[$ratingsHeaderkey])
    {
      $crosstabData[$crosstabDataKey][$ratingsHeaderkey] ='0.00';
      ksort($crosstabData[$crosstabDataKey]);
    }
  }
 }
 6) To output the array - simply loop and output in a table.

This time I want get the key identifying each item of data. As the data query is the largest there is a cost of looping it, so with the unique key for each recordset I can then directly get the data from the data.

So in this example the unique key is both the country and the year, so I put them together as the unique key. The header query is essentually the same as before.

 Data Query 

SELECT CONCAT(country,'|',year) AS ROWKEY, value AS VALUE
FROM football
ORDER BY country ASC


 Header Query 

SELECT DISTINCT year AS ROWKEY
FROM football	
ORDER BY year desc

First I have to work on the data query to create an array where the key is: [ROWKEY].
Now I have the data asI need to get an array of the unique rows - in this case its the country. So I loop the data query and split the ROWKEY and then get the unique keys using the array_unique() function.

All thats needed now to generate the cross tab is:

Loop the headers to set the first row.
Loop the unique rows to get the first part of the key.
Loop the headers again to the the second part of the key
Use the combined key to get the data from the data query recordset

$SQL = "SELECT CONCAT(country,'|',year) AS ROWKEY, value AS VALUE
		FROM football
		ORDER BY country ASC
		";
$result = $DB_football->Execute($SQL);
1) Set the Data into an associative array with the unique key.
$fixturesData = array();
if (isset($result->fields['ROWKEY']) && isset($result->fields['VALUE']))
{
	while (!$result->EOF) 
	{
       $fixturesData[$result->fields['ROWKEY']] = $result->fields['VALUE'];
  	   $result->MoveNext();
	}
}
else
{
	$fixturesData = array();
}
$SQL = "SELECT DISTINCT year AS ROWKEY
		FROM football		
		";
$fixturesHeader = $DB_football->getAll($SQL);
2) Get the unique keys (Countries).
$uniqueKeys = array();
foreach ($fixturesData as $keyKey => $keyItem)
{
	$headers = explode("|", $keyKey);
	$uniqueKeys[] = $headers[0];
}
$uniqueKeys = array_values(array_unique($uniqueKeys));
3) Initialize the array.
$crosstabData = array();
$row = 0;
$crosstabData[$row][] = ' ';
4) Add the headers.
foreach ($fixturesHeader as $headerKey => $headerItem)
{
	$crosstabData[$row][] = $headerItem['ROWKEY'];
}
++$row;
5) Loop the countries.
foreach ($uniqueKeys as $uniqueKeysKey => $uniqueKeysItem)
{
	$crosstabData[$row][] = $uniqueKeysItem;
	5.1) Loop the headers (years).
	foreach ($fixturesHeader as $headerKey => $headerItem)
	{
		5.2) Create the unique column key
		$uniqueKey = $uniqueKeysItem.'|'.$headerItem['ROWKEY'];
		5.3) Check it exists and add blank if not set.
		if (isset($fixturesData[$uniqueKey]))
		{
			$crosstabData[$row][] = number_format($fixturesData[$uniqueKey], 2, '.', '');
		}
		else
		{
		 	$crosstabData[$row][] = ' ';
		}
	}
	++$row;
}

This method is quicker by far and simpler. The server load is less as the amount of data it has to loop is much smaller than the previous example. To compare times look at the base of each table, where it displays the processing time.

Links