Location: PHPKode > scripts > CrossData > crossdata.php
<?PHP
// Class CROSSDATA V. 0.6.0 22 marzo 2013 PHP 5 compatible
// free to use but no warranties
// El Condor - Condor Informatique - Turin
class CrossData {
	var $sql = "";		  	  	   	// sql for get data
	var $error = "";		  	 	// syntax error
	var $percent = false;  	  	 	// percent
	var $last = "";	 				// intestazione last row, last column
	var $data1Label = "";	 		// first column head
	var $data2Label = "";	 		// first row head
	var $lastLabel = "";	 		// last row and column head
	var $myBeLabel = "";	 		// last row and column head
	public $precision = 2;			// decimal for % and Average
	public $ifEmpty = null;			// 0 if empty
	var $opField = false;			// true if field extraction
	function percent($a,$t) {
		return sprintf("%3.".$this->precision."f",round(100*$a/$t,$this->precision));
	}
	function createTitle($f1,$f2,$op,$f3="") {
		return (($op == "")? "Count ":"$op $f3 ")."of $f1 by $f2";
	}
	function takeWord(&$str) {   // take words, space separated, and words in apostrophe
		$str = ltrim($str);
		If (substr($str,0,1) == "'") {
			$token = strtok($str, "'");
			$str = substr($str,strlen($token)+2);
		} else { 
			$token = strtok($str, " ");
			$str = substr($str,strlen($token));
		}
		return $token;
	}
	function scanCommand($cross) {
		$sum = "COUNT(*) CK";		// default group function
		$this->myBeLabel = "COUNT";
		if (strtoupper($this->takeWord($cross))!= "CROSS") {$this->error = "lack of CROSS command";return false;}
		$data1 = $this->takeWord($cross);		// first field
		$this->data1Label = $data1;
		$par = $this->takeWord($cross);
		if (strtoupper($par) != "BY") {
			$this->data1Label = $par;			// first label
			$par = $this->takeWord($cross);
		}
		if (strtoupper($par) != "BY") {$this->error = "lack of BY clause";return false;}
		$data2 = $this->takeWord($cross);		// second field	
		while (true) {
			$par = $this->takeWord($cross);
			switch (strtoupper($par)) {				
				case "%":
				 	$this->percent = TRUE;
					$this->last = $par;
					break;
				case "SUM":
				case "MIN":
				case "MAX":
				case "AVG":
					$field = $this->takeWord($cross);
					$this->myBeLabel = $field;
	   		 		if ($this->percent) $this->myBeLabel .= " %";
					$this->last = strtoupper($par);
					if (strtoupper($par) == "AVG")
						$sum = "ROUND(AVG($field),".$this->precision.") CK";
	   		 		else $sum = "$par($field) CK";
					break;
				case "ROWS":
				case "COLS":
					$field = $this->takeWord($cross);
				 	$this->percent = TRUE;
					$this->last = strtoupper($par);
					$this->myBeLabel = $field;
					if ($par != "*") $sum = "SUM($field) CK";
					break;
				case "FIELD":
					$field = $this->takeWord($cross);
					$sum = "GROUP_CONCAT(DISTINCT $field ORDER BY $field) CK";
					$this->myBeLabel = $field;
					$this->opField = true;
					$this->last = strtoupper($par);
    				break;
				case "FROM":
					break;
				default:
					if ($this->last == "") {
						if ($this->data2Label == "")  $this->data2Label = $par;
						else $this->lastLabel = $par;
					} else $this->lastLabel = $par;
			}
			if (strtoupper($par) == "FROM") break;
			if ($par == "") {$this->error = "my be lack of field";return false;}
		}
		if ($this->lastLabel == "") $this->lastLabel = $this->last." ".$this->myBeLabel;
		if ($this->data2Label == "")  $this->data2Label = $data2;
		$union = "";
		// z or space prepended for order purpose
		if ($this->last == "" || strpos("FIELD",$this->last)=== false)
			$union = " UNION SELECT $sum,$data1,'z$this->lastLabel' FROM $cross GROUP BY $data1"
					." UNION SELECT $sum,'$this->lastLabel',CONCAT(' ',$data2)  FROM $cross GROUP BY $data2"
					." UNION SELECT $sum,'$this->lastLabel','z$this->lastLabel' FROM $cross";
		$this->sql = "SELECT $sum, $data1 DT1,CONCAT(' ',$data2) DT2 FROM $cross GROUP BY $data1,CONCAT(' ',$data2)$union";
		if (is_null($this->ifEmpty)) $this->ifEmpty = ($this->last == "FIELD")? "":0;
		return true;
    }
	function makeTable($arr,$aRows,$aCols,$title="") {
  		if ($title == "")
			$title = $this->createTitle($this->data1Label,$this->data2Label, $this->last,$this->myBeLabel);
		$cd = "<TABLE CLASS=CDTable ID=CDTable>";
  		$cd .= "<CAPTION>$title</CAPTION>\n<THEAD><TR>";
		$cd .= "<TH><div style='text-align:right'>$this->data2Label</div><div style='text-align:left'>$this->data1Label</div>";
		foreach($aCols as $key => $value) $cd .= "<TH>".substr($key,1);
		for ($i=0;$i<count($arr);$i++) {
			$cd .= "<tr><td>".$aRows[$i];
			for ($j=0;$j<count($arr[$i]);$j++) {
				$cd .= "<TD";
				$cellStyle = ($this->last == "FIELD")?"left":"right";
				$v = $arr[$i][$j];
				if ($v == "") $v = $this->ifEmpty;
				$cd .= " Style='text-align:$cellStyle'>".(($v === "")?"&nbsp;":$v)."</TD>";				
			}	
		}
		return $cd."</TABLE>";
	}
	function makeArray($arr,$aRows,$aCols) {
		for ($i=0;$i<count($arr);$i++) {
			for ($j=0;$j<count($arr[$i]);$j++) if ($arr[$i][$j] == "") $arr[$i][$j] = $this->ifEmpty;
			array_unshift($arr[$i],$aRows[$i]);
		}
		$a[] = $this->data2Label."/".$this->data1Label;
		foreach($aCols as $key => $v) $a[] = substr($key,1);
		array_unshift($arr,$a);
	if ($this->percent) $arr[count($arr)-1][] = sprintf("%3.".$this->precision."f",100);
	return $arr;		
	}
	function Cross($cross,$title="",$onlydata=false) {
		if (!$this->scanCommand($cross)) {
			echo "<br>Error: ".$this->error;
			exit;
		}
		$aRows = array();    	// array of data1 values
		$aCols = array();     	// array of data2 values
		$res = mysql_query($this->sql);
		while ($fields = mysql_fetch_row($res)) {
			$aRows[$fields[1]] = "";
			$aCols[$fields[2]] = "";
		}
		ksort($aCols);
		mysql_data_seek($res,0);
		foreach($aRows as $k=>$v) $arrOut[$k] = $aCols;	// complete array		
		while ($fields = mysql_fetch_row($res)) $arrOut[$fields[1]][$fields[2]] = $fields[0]; // populate array
   		$arrOut = array_values($arrOut);
		for ($i=0;$i<count($arrOut);$i++) $arrOut[$i] = array_values($arrOut[$i]);
// % handling
		if ($this->percent) {
			$nRows = count($arrOut);
			$nCols = count ($arrOut[0]);
			switch ($this->last) {
				case "%":case "SUM":
					for($i=0;$i<$nRows;$i++)		// horizontal %
						$arrOut[$i][] = $this->percent($arrOut[$i][$nCols-1],$arrOut[$nRows-1][$nCols-1]);
					for($i=0;$i<$nCols;$i++) 		// vertical %
						$arrOut[$nRows][] = $this->percent($arrOut[$nRows-1][$i],$arrOut[$nRows-1][$nCols-1]);
					$aRows["%"] = "";
					$aCols[" %"] = "";
					break;
				case "ROWS":				
					for($i=0;$i<$nRows-1;$i++) {		// horizontal %
						$rowTotal = $arrOut[$i][$nCols-1];
						for($j=0;$j<$nCols;$j++)
							$arrOut[$i][$j] = $this->percent($arrOut[$i][$j],$rowTotal);
					}
					array_pop($arrOut);
					break;
				case "COLS":				
					for($i=0;$i<$nCols-1;$i++) {		// vertical %
						$colTotal = $arrOut[$nRows-1][$i];
						for($j=0;$j<$nRows;$j++)
							$arrOut[$j][$i] = $this->percent($arrOut[$j][$i],$colTotal);
					}
					for($j=0;$j<$nRows;$j++) array_pop($arrOut[$j]);
					array_pop($aCols);
			}
		}
		mysql_free_result ($res);
		if (!$onlydata) return $this->makeTable($arrOut,array_keys($aRows),$aCols,$title);				 // no list, only need data
		else return $this->makeArray($arrOut,array_keys($aRows),$aCols);
	}
}		   // end class
?>
Return current item: CrossData