Diafol's Demos

Back To Index

<demo>Generic Class for Rowspans from Arrays</demo>

This is a PHP class for creating a HTML table complete with automatic rowspans for "grouped keys" in an array. For example the country-state-city-members:

* denotes grouped keys to be rowspanned.

The SQL and Output

Our SQL would be something like this:

SELECT c.country, s.state, t.city, m.name FROM countries AS c
INNER JOIN states AS s ON s.country_id = c.country_id
INNER JOIN cities AS t ON t.state_id = s.state_id
INNER JOIN members AS m ON m.city_id = t.city_id
ORDER BY c.country,s.state,t.city,m.name

The ORDER BY clause is all-important as the PHP Class expects the data array to be sorted.

Demo

The following demo generates (via ajax) the table from an array from an SQL query.

Show Demo Output:

Demo Output

The PHP Class

The following class is a little rough and ready and has a number of loops that I'm sure could be optimized, but anyway, here goes...

<?php
class rowSpanner
{
	private $data;
	private $groupArray = array();
	private $header=NULL;

 /**
  *
  * Constructor
  * 
  * @param array $data Datarows from query
  * @param array $groups Fieldnames required to be rwospanned
  * @param array $header OPTIONAL Column headings to output with the table 
  */	
	public function __construct($data, $groups, $header=NULL)
	{
		$this->data = (array) $data;
		$this->groupArray = (array) $groups;
		if($header) $this->header = (array) $header;
	}
 /**
  * 
  * Return Rowspanned Table Output 
  *
  * @return string A HTML <tbody> containing rowspanned data, optionally with <thead> section
  */
	
	public function get_table_output()
	{
		$flipGroups = array_flip($this->groupArray);
		$changeData = $this->get_change_data($flipGroups);
		$cascade = $this->build_cascade($changeData);
	
		$rowCascade = array_map('array_flip',$cascade);
		$rowspans = array_map(array($this, 'get_rowspans'), $rowCascade);	
		
		$output = $this->attach_header() . '<tbody>' . $this->create_output($rowspans, $flipGroups) . '</tbody>';
		return $output;
	}

 /**
  * 
  * Return Unspanned Table Output 
  *
  * @return string A HTML <tbody> containing unspanned data, optionally with <thead> section
  */
	
	public function get_unspanned()
	{
		return $this->attach_header() . '<tbody>' . $this->unspan_output() . '</tbody>';
	}
	
 /**
  * 
  * Echo Rowspanned Table Output 
  *
  */

	public function render()
	{
		echo $this->get_table_output;
	}

 /**
  * 
  * Return header in <thead> section 
  *
  * @return string A HTML <thead> section
  */

	private function attach_header()
	{
		if($this->header) return '<thead><tr><th>' . implode('</th><th>',$this->header) . '</th></tr></head>';
	}
	
	private function get_rowspans($rowCascade)	
	{
		$i=0;
		$rows = array();
		$count = count($this->data);
		foreach($rowCascade as $key=>$v)
		{
			if($i == 0)
			{
				$prevkey = $key;
				$i++;
				continue;	
			}
			$rowValue = $key - $prevkey;
			$rows[$prevkey] = $rowValue;
			$prevkey = $key;
			$i++;
		}
		$rows[$prevkey] = $count - $prevkey; 
		return $rows;
	}
	
	private function unspan_output()
	{
		$output = '';
		foreach($this->data as $row) $output .= '<tr><td>' . implode('</td><td>', $row) . '</td></tr>';
		return $output;
	}

	private function get_change_data($flipGroups)
	{
		$i = 0;
		$changeData = array();
		$prev = array_fill_keys($this->groupArray,'');
		foreach($this->data as $datarow)
		{
			foreach($flipGroups as $groupKey=>$groupValue)
			{
				$start = false;
				if($datarow[$groupKey] != $prev[$groupKey] || $start)
				{
					$changeData[$groupKey][] = $i;
					$start = true; 	
				}
			}
			$i++;
			$prev = $datarow; 
		}
		return $changeData;
	}
	
	private function build_cascade($changeData)
	{
		$prev = array();
		$cascade = array();
		foreach($changeData as $key=>$value)
		{
			$value = array_unique(array_merge(array_values($value),array_values($prev)));
			sort($value);
			$cascade[$key] = $value;
			$prev = $value;
		}
		return $cascade;
	}
	
	

	private function create_output($rowspans, $flipGroups)
	{
		$output = '';
		foreach($this->data as $key=>$value)
		{
			
			$output .= '<tr>';
			$residueArray = array_diff_key($value, $flipGroups);
			
			foreach($this->groupArray as $groupItem)
			{
				if(isset($rowspans[$groupItem][$key]))
				{
					$output .= '<td';
					$output .= ($rowspans[$groupItem][$key] > 1) ? ' rowspan = "'.$rowspans[$groupItem][$key].'">' : '>';
					$output .= $value[$groupItem];
					$output .= '</td>';
				}	
			}
			$output .= '<td>' . implode('</td><td>', $residueArray) . '</td>';
			$output .= '</tr>';
		}
		return $output;	
	}
}
?>

Sample Usage

Taking the country-state-city-member example, it could be something like this:

<?php
	require '../class/rowspanner.class.php';

	$query = "SELECT c.country, s.state, t.city, m.name FROM countries AS c
	INNER JOIN states AS s ON s.country_id = c.country_id
	INNER JOIN cities AS t ON t.state_id = s.state_id
	INNER JOIN members AS m ON m.city_id = t.city_id
	ORDER BY c.country,s.state,t.city,m.name";
	
	$groups = array("country","state","city");
	
	$header = array("Country", "State", "City", "Member");
		
	$db = new mysqli("localhost","root","","demo");

	$result = $db->query($query);
	$data = array();
	while($row = $result->fetch_array(MYSQLI_ASSOC)) $data[] = $row;
	
	$table = new rowSpanner($data,$groups,$header);
?>
<table>	
	<?php $table->render();?>
</table>