How to transform sql query results to pivot table

问题:

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?

回答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"

回答2:

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...