I am looking for some help to build a generic PHP function that will take in the output of SQL query
SELECT DISTINCT categoryID, StatusID, COUNT( * ) FROM tableA GROUP BY categoryID
Sample result:
categoryID StatusID COUNT( * ) CategoryB On Hold 1 CategoryA On Hold 4 CategoryC On Hold 3 CategoryB Draft 1
There can be any number of CategoryIDs and Statuses in the database...
and return a TABULAR table format:
My desired outcome goes something like this:
Status Summary Table by Category: --------------------------------------------------------- | CategoryA | CategoryB | CategoryC | ... | TOTAL Completed | 0 | 1 | 0 | ... | 1 On Hold | 4 | 0 | 3 | ... | 7 Draft | 0 | 1 | 1 | ... | 2 --------------------------------------------------------- TOTAL: | 4 | 2 | 4 | ... | 10
I figure it out! -- I am hoping this helps someone else in the future.. It is not pretty code, but it works and it is pretty genetic so it can be used as needed.. Who it is called:
$sql="SELECT DISTINCT categoryID, statusID, COUNT( * ) as Count1 FROM entries GROUP BY categoryID" ; $results = mysql_query($sql, $con); // Now that we have both Status and Category, lets place the counts in the right cells: echo displayTabularSum ($myarray,"status","category",1) ;
Now the functions I used:
function displayTabularSum($myarray, $table1,$table2,$includeTotals) { // First get all data from $table1 into an array $sql = "SELECT * FROM $table1 WHERE 1"; $results= mysql_query($sql); $statusCodes= getsqlresultintoarray ($results) ; // Second: get all data from $table2 into an array $sql = "SELECT * FROM $table2 WHERE 1"; $results= mysql_query($sql); $categoryCodes= getsqlresultintoarray ($results) ; // Now create the results table with appropriate values in columns $statusTable=array(); $out = ''; $first = true; $cT=array(); for ($x=0; $x'; for ($y=0; $y'.$categoryCodes[$y][1].''; } if ($includeTotals) $out.= 'Total'; $out.= ''; $first = false; } $out .=""; $out .="".$statusCodes[$x][1].""; $rT=0; for ($y=0; $y"; $c1=searchForId($categoryCodes[$y][0], $myarray, "categoryID"); $c2=searchForId($statusCodes[$x][0], $myarray, "statusID"); $count1=0; $same=($c1==$c2); If ( $same ) { If ($c1==99999 OR $c2==99999) { // do nothing... These are NULLs } else { $count1=$count1+$myarray[$c1]['Count1']; } } $out .= $count1; $rT=$count1+$rT; $cT[$y]=$cT[$y]+$count1; // Collecting column Totals $out .=""; } if ($includeTotals) $out.= ''.$rT.''; // display rowTotal $out .=""; } if ($includeTotals) { // Display the column Total before closing table. $cT1=0; $out .=""; $out.= 'Total:'; for ($x=0; $x'.$cT[$x].''; $cT1=$cT1+$cT[$x]; } $out.= ''.$cT1.''; $out .=""; } $out .=""; return $out; } Function getresultsintoarray1 ($results) { // Function to get all results from a SQL QUery into an Array. $num_rows = mysql_num_rows($results); $myarray=array(); if ($num_rows>0) { while($row = mysql_fetch_assoc($results)) { $myarray[] = $row; } }else { echo " No data found on database... " ; unset($myarray); $myarray=array(); } return $myarray; } function getsqlresultintoarray ($get) // YES { $num_rows = mysql_num_rows($get); $returnArray=array(); $i=0; while($row1 = mysql_fetch_array($get)) { $returnArray[$i][0]=$row1[0]; // This is the ID $returnArray[$i][1]=$row1[1]; // This is the name $i++; } return $returnArray; } function searchForId($id, $array, $field) { If (count($array)>0) { foreach ($array as $key => $val) { if ($val[$field] === $id) { return $key; } } } return 99999; }
If anyone has ideas as to how to improve, I would appreciate it!