David David - 1 month ago 18
Javascript Question

Grouping like terms from array and writing into HTML table

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.

Answer

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;
})
Comments