将MySQL结果转换为Google Graphs的JavaScript数组

Question

I'm running a query that fetches some data back which need to be converted to a JavaScript array for using a Google Graph.

Here is the query:

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

This query produces the following array:

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] => 
                         ) 
               ) 
      ) 

I then need to take this query and convert it to fit the following JavaScript array:

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

The data currently in the string is example data. I need the array to look like this:

[
   ['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],
]

This is what I've tried so far:

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

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

$str1 .= "],[";



echo $str1;

This results in:

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

The first line is OK but I'm not sure where to go from here to get what I need, nor am I sure this is even the right/best approach.

Answer 1

Use the following code it will help you. You have to separate the keys and values and then merge.

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