Grouping like terms from array and writing into HTML table

Question

I have a SQL query result that looks something like this:

[Resource]      [Department]     [Amount]
Water           Residential      100
Electricity     Residential      200
Electricity     Business         50
Water           Business         400
Gas             Residential      30
Electricity     Industry         200

I have an (empty) HTML table that is like this:

[Department]     [Water]     [Electricity]    [Gas]
Residential
Business
Industry

That is, it uniquely lists every "resource" that came back from the query as a column, and uniquely lists the department names as the values in the first column. I do this just by taking array_unique in PHP of the Resource and Department results from the above query and writing those as the column headers and the values in the first column, respectively.

I'm stuck now on how to fill in the numbers. My thought was to use javascript to loop through my empty HTML table and for each row after the header row, read the value in the "Department" column, and then loop through again for each value in the header row, THEN push my original SQL array of data to a JS array and again loop through the first two columns of data (i.e. a triple nested loop); when there's a match, write that into the HTML table at whatever place I'm at. But I cannot find a helpful example, probably because this is a slow and overly complicated way to do it. It's how I would have done it in Excel VBA, and am still very new to SQL/PHP/JS techniques. Any general tips and advice appreciated.

I suspect this would be a good use of SQL pivot tables but wanted some agreement before I try to figure out how to hack those.

hmm...@david I would try to create a sql query that returns the data for the HTML table that you're seeking. I believe that it would be faster than a bunch of loops in PHP, but it may be a little bit challenging to create the query. — Craig, Oct 09 '16 at 00:48
you want to map the query result array to expected output structure in php. No point sending it to browser to render incorrectly and then modify it — charlietfl, Oct 09 '16 at 00:49
If department number (number of result column) is small, pivot table is good solution. See [MySQL - Rows to Columns](http://stackoverflow.com/questions/1241178/mysql-rows-to-columns) for more explanation. The QA is for `MySQL` but the idea can be applied to other DBMS too. — putu, Oct 09 '16 at 01:49
I add sum solutions for javascript and SQL check the answer again please. — MSS, Oct 11 '16 at 06:17

MSS · Accepted Answer · 2016-10-11T06:16:15.463

In PHP you can group your records like this:

$data=query(...);
$groupedByResource=array(); // or [];
foreach($data as $row){
 $groupedByResource[$row['Resource']][]=$row;
}
var_dump($groupedByResource);

The result should be:

array(...){
"Electric"=>
   Electricity     Residential      200
   Electricity     Business         50
   Electricity     Industry         200
,
"Water"=>
   Water           Residential      100,
   Water           Business         400
,
"Gas"=>
   Gas             Residential      30

And in javascript do this:

var data=[...];
var groupedData={};
data.foreach(function(row){
 groupedData[row.Resource]=row;
})

And in PHP-SQL hybrid solution and just to sum the amount:

$resourseList=query('SELECT Resource FROM table group by Resource');
$headerList=array();
foreach($resourseList as $row){
 $headerList[]="SUM(CASE WHERE Resource='{$row['Resource ']}' THEN amount ELSE 0 END) AS {$row['Resource ']}";
}

$mainQuery="SELECT ".implode(',',$headerList)." FROM table";
var_dump(query($mainQuery));

And if you want what it does it make a query like :

SELECT SUM(CASE WHERE Resource='Electric' THEN amount ELSE 0 END) AS Electric, ... FROM table

So it make a column for every resource you have and sum all amount for that resource.

Notice 1: If your resource are not string change the then way it alias the column.

Notice 2: CASE WHEN ... END works in most T-SQL based DBMSes but in mysql you simply can use IF(,,).

score 1 · Answer 2 · answered Oct 09 '16 at 20:05

I don't see the advantage of using SQL pivot table, while it's pretty easy to directly process your current query result in PHP, like this:

//...get data from DB...
while ($row = /*...your prefered fetch method...*/) {
  $department = $row['department'];
  $resource = $row['resource'];
  $amount = $row['amount'];
  // register resource
  if (!in_array($resource, $resources)) {
    array_push($resources, $resource);
  }
  // feed detailed table
  if (isset($table[$department][$resource])) {
    $table[$department][$resource] += $amount;
  } else {
    $table[$department][$resource] = $amount;
  }
}

Then you can build your HTML table:

Use $resources list to feed the first row with column titles (don't forget to begin with hard-coded "department").
Then iterate with foreach ($table as $department => $columns) to build rows (carefully refer to $resources to set the right columns, since each row may contain only a subset of the whole $resources list).

Grouping like terms from array and writing into HTML table

2 Answers2