如何使用php数组将SQL查询结果转换为数据透视表? [英] How to transform sql query results to pivot table using php arrays?

问题描述

我正在尝试为图书馆预订系统创建一些统计信息.我的sql查询结果看起来像下面的结构.

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

我正在尝试使用php编写一些代码，以创建具有以下外观的统计信息表:

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          |

我必须使用数组和一些循环(foreach)使用php(数据透视技术)对其进行转换.我尝试这样做，但是我对嵌套循环感到困惑.

我编写的代码如下:

$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;

}

任何帮助如何将使用php的查询转换为上表?

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

推荐答案

假设您从以下数组结构开始:

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

然后执行以下foreach循环:

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"];
}

将产生以下内容:

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

这篇关于如何使用php数组将SQL查询结果转换为数据透视表?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！