How to transform sql query results to pivot table using php arrays?

Question

I am trying to create some statistics for a library reservation system. The result of my sql query looks like the following structure.

total_no_students| department  | property       | month  
 241             | Physics     | undergraduate  | Nov
 236             | Physics     | undergraduate  | Dec
 254             | Physics     | postgraduate   | Nov
 210             | Physics     | postgraduate   | Dec
 193             | Architecture| undergraduate  | Nov
 181             | Architecture| undergraduate  | Dec
 127             | Architecture| postgraduate   | Nov
 292             | Architecture| postgraduate   | Dec
 134             | Biology     | undergraduate  | Nov
 188             | Biology     | undergraduate  | Dec
 129             | Biology     | postgraduate   | Nov
 219             | Biology     | postgraduate   | Dec

I am trying using php to write some code in order to create a statistics table with the following appearance:

    |Physics-undergrad|Physics-postgrad|Architecture-undergrad|Architecture-postgrad|
Nov |      241        |     254        |         193          |        127          |
Dec |      236        |     210        |         181          |        292          |

I have to transform it with php (pivot technique) using arrays and some loops (foreach). I tried to do it but I am confused with nested loops.

The code that I have written is as follows:

$csvArray = array();


$departments = array('Physics','Architecture','Biology');
$properties = array('undergraduate','postgraduate');

$con = mysqli_connect($mysql_hostname, $mysql_user, $mysql_password, $mysql_dbname);

$sql="SELECT count(id) as total_no_students, department, property, MONTH(table1.created) as month

FROM table1
left JOIN table2
ON table2.barcode=table1.input

group by department, property, month";

$res=mysqli_query($con,$sql);                                                   

while($row=mysqli_fetch_array($res)){

$time = $row['month'];

    if (!array_key_exists($time, $csvArray) ) {
        foreach ($departments as $department) {
            $csvArray[$department] = array();
            foreach($properties as $property) {
                $csvArray[$department][$property] = array();

            }
        }
    }
    $department = $row['department'];
    $property = $row['property'];
    $total_no_students = $row['total_no_students'];

    $csvArray[$time][$departments][$property] = $total_no_students;

}

Any help how to transform the query using php to the above table?

Please tag your question with which SQL implementation you are using (i.e. MySQL, SQL Server, etc.) — AeroX, Dec 29 '14 at 10:34
can you post your php code ? But i would use "month" as a Key of your array — MouradK, Dec 29 '14 at 10:34
Please show your attempted code. We'll help you fix what you tried, we're not going to do it for you. — Barmar, Dec 29 '14 at 10:38
possible duplicate of [How to transform results of an sql query to an html table with specific format](http://stackoverflow.com/questions/27602896/how-to-transform-results-of-an-sql-query-to-an-html-table-with-specific-format) — Saharsh Shah, Dec 29 '14 at 11:01
You say that you **have** to transform it using PHP. Is this a homework question? As otherwise it would be far easier to solve this using SQL. — AeroX, Dec 29 '14 at 11:07
I have already implemented it (with the help of Saharsh Shah) using SQL but when you have many departments and properties the SQL query becomes enormous. So I have to do it with pivot technique using php arrays — George, Dec 29 '14 at 11:10

jarlh · Answer 1 · 2014-12-29T10:44:15.213

1

select "month",
       SUM(case when department = 'Physics' and property = 'undergraduate' then total_no_students else 0 end) as "Physics-undergrad",
       [...]
from table_name
group by "month"

edited Dec 29 '14 at 10:44

answered Dec 29 '14 at 10:38

jarlh

42,561
8
45
63

You need backticks around the alias `Physics-undergrad`. – Barmar Dec 29 '14 at 10:38
Because it's an invalid SQL identifier? Corrected now. (And MONTH too, it's a reserved word...) – jarlh Dec 29 '14 at 10:43
@Barmar That assumes that the OP is using MySQL which may not be the case. – AeroX Dec 29 '14 at 10:45
1

Right, didn't notice he hadn't specified. It will have to be escaped regardless of the DB, but the syntax of escaping will be different. – Barmar Dec 29 '14 at 10:47

score 1 · Answer 2 · answered Dec 29 '14 at 11:26

Assuming you start with the following array structure:

Array
(
    [0] => Array
        (
            [total_no_students] => 241
            [department] => Physics
            [property] => undergraduate
            [month] => Nov
        )

    [1] => Array
        (
            [total_no_students] => 236
            [department] => Physics
            [property] => undergraduate
            [month] => Dec
        )
...

Then the following foreach loop:

$new_array = Array();
foreach( $old_array as $v )
{
    if(!isset( $new_array[$v["month"]][($v["department"].'-'.$v["property"])] ))
    {
        $new_array[$v["month"]][($v["department"].'-'.$v["property"])] = 0;
    }
    $new_array[$v["month"]][($v["department"].'-'.$v["property"])] += $v["total_no_students"];
}

Would produce the following:

Array
(
    [Nov] => Array
        (
            [Physics-undergraduate] => 241
            [Physics-postgraduate] => 254
            [Architecture-undergraduate] => 193
            [Architecture-postgraduate] => 127
            [Biology-undergraduate] => 134
            [Biology-postgraduate] => 129
        )

    [Dec] => Array
        (
            [Physics-undergraduate] => 236
            [Physics-postgraduate] => 210
            [Architecture-undergraduate] => 181
            [Architecture-postgraduate] => 292
            [Biology-undergraduate] => 188
            [Biology-postgraduate] => 219
        )

)

You can then display that however you want...

How to transform sql query results to pivot table using php arrays?

2 Answers2

Linked