javascript - 将 MySQL 结果转换为用于 Google Graphs 的 JavaScript 数组

Question

我正在运行一个查询来取回一些数据，这些数据需要转换为 JavaScript 数组才能使用 Google Graph。

这里是查询:

SELECT 
sub.supplier_name,
(
    SELECT ROUND(AVG(sp.progress), 2) as P1 
    FROM `sub_performancemeasures` AS sp
    INNER JOIN `submissions` AS sub ON sub.id = sp.sub_id
    WHERE `period` = '1'
) AS p1,
(
    SELECT ROUND(AVG(sp.progress), 2) as P2 
    FROM `sub_performancemeasures` AS sp
    INNER JOIN `submissions` AS sub ON sub.id = sp.sub_id
    WHERE `period` = '2'
) AS p2,
(
    SELECT ROUND(AVG(sp.progress), 2) as P3
    FROM `sub_performancemeasures` AS sp
    INNER JOIN `submissions` AS sub ON sub.id = sp.sub_id
    WHERE `period` = '3'
) AS p3,
(
    SELECT ROUND(AVG(sp.progress), 2) as P4
    FROM `sub_performancemeasures` AS sp
    INNER JOIN `submissions` AS sub ON sub.id = sp.sub_id
    WHERE `period` = '4'
) AS p4,
(
    SELECT ROUND(AVG(sp.progress), 2) as P5
    FROM `sub_performancemeasures` AS sp
    INNER JOIN `submissions` AS sub ON sub.id = sp.sub_id
    WHERE `period` = '5'
) AS p5
FROM `submissions` AS sub
INNER JOIN `sub_performancemeasures` AS sp ON sub.id = sp.sub_id
INNER JOIN `performance_measures` AS pm ON sp.pm_id = pm.id
INNER JOIN `kpis` ON kpis.id = pm.kpi_id
GROUP BY sub.supplier_name

此查询生成以下数组:

Array ( 
  [0] => Array ( 
          [sub] => Array ( 
                           [supplier_name] => C Spencer Ltd 
                         ) 
           [0]  => Array ( 
                           [p1] => -11.43 
                           [p2] => 36.67 
                           [p3] => 
                           [p4] => 
                           [p5] => 
                         ) 
               ) 
   [1] => Array ( 
          [sub] => Array ( 
                           [supplier_name] => Supplier 2 
                         ) 
           [0]  => Array ( 
                           [p1] => 15.21 
                           [p2] => -6.44 
                           [p3] => 
                           [p4] => 65.41
                           [p5] => 
                         ) 
               ) 
      ) 

然后我需要获取此查询并将其转换为适合以下 JavaScript 数组:

[
   ['Period', 'Allen', 'Tom', 'Sim'],
   [1, null, null, -100],
   [2, 3, null, null],
   [3, 3, null, null],
   [4, null, 2, null],
   [5, 3, 100, null],
]

当前字符串中的数据为示例数据。我需要数组看起来像这样:

[
   ['Period', 'C Spencer Ltd', 'Supplier 2'],
   [1, -11.43, 15.21],
   [2, 36.67, -6.44],
   [3, null, null],
   [4, null, 65.41],
   [5, null, null],
]

这是我到目前为止尝试过的:

$str1="[['Period',";
foreach($avgSupScores as $supplier){

    $str1 .= "'".$supplier['sub']['supplier_name']."', ";
    //$str2 .= "1, ".$periods[0]['p1']."";
}
$str1 = rtrim($str1, ", ");

$str1 .= "],[";



echo $str1;

这导致:

[['Period','C Spencer Ltd', 'Supplier 2'],[

第一行没问题，但我不确定从这里到哪里才能得到我需要的东西，我也不确定这是否是正确/最佳方法。

Answer 1

使用下面的代码，它会帮助你。您必须将键和值分开，然后合并。

<?php
$con=mysql_connect("localhost","Uname","pwd") or die("Failed to connect with database!");
mysql_select_db("db", $con); 

$fields = mysql_query("SELECT * FROM chart_data");

while($r = mysql_fetch_assoc($fields)) {
$arr2=array_keys($r);
$arr1=array_values($r);

}

for($i=0;$i<count($arr1);$i++)
{
    $chartData[$i]=array((string)$arr2[$i],intval($arr1[$i]));
}
echo "<pre>";
$data=json_encode($chartData);  //pass this $data in the google graph
?>