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
– AeroX, Commented Dec 29, 2014 at 10:34
can you post your php code ? But i would use "month" as a Key of your array — MouradK
– MouradK, Commented Dec 29, 2014 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
– Barmar, Commented Dec 29, 2014 at 10:38
possible duplicate of How to transform results of an sql query to an html table with specific format — Saharsh Shah
– Saharsh Shah, Commented Dec 29, 2014 at 11:01
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
– George, Commented Dec 29, 2014 at 11:10

jarlh · Accepted Answer · 2014-12-29 10:44:15Z

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, 2014 at 10:44

answered Dec 29, 2014 at 10:38

jarlh

44.9k8 gold badges52 silver badges68 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Barmar Over a year ago

You need backticks around the alias Physics-undergrad.

jarlh Over a year ago

Because it's an invalid SQL identifier? Corrected now. (And MONTH too, it's a reserved word...)

AeroX Over a year ago

@Barmar That assumes that the OP is using MySQL which may not be the case.

Barmar Over a year ago

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.

AeroX · Accepted Answer · 2014-12-29 11:26:54Z

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

Collectives™ on Stack Overflow

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

2 Answers 2

4 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related