转换MySQL结果到JavaScript数组的谷歌图表
Convert MySQL results into JavaScript array for Google Graphs
我正在运行一个查询,获取一些数据,需要转换为JavaScript数组使用谷歌图。
下面是查询:
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'],[
第一行是好的,但我不确定从这里去哪里得到我需要的,我也不确定这甚至是正确的/最好的方法。
使用下面的代码,它将帮助您。你必须把键和值分开,然后再合并。
<?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
?>
相关文章:
- 如何创建一个谷歌地图地理坐标数组
- 将行从数组添加到谷歌图表
- 尝试从JSON数组在谷歌地图中制作路径
- 谷歌跟踪代码管理器使用什么模式来观察 de 'dataLayer' 数组
- 如何将数组(数字)加载到谷歌应用脚本 Byte[] 中
- 谷歌图表错误'不是数组'关于Ajax更新
- Javascript-将谷歌趋势url中的数据保存到PHP数组中
- 谷歌从数组中映射标记、折线和窗口信息
- 如何在谷歌地图API中使用坐标数组绘制多边形
- 如何将数组作为谷歌分析自定义维度发送
- 如何在谷歌工作表中使用数组公式时使用getLastRow.我可以让它忽略一列吗
- 如何用数组而不是值正确填充谷歌图表(地图)
- 将信息从暴雪API提取到谷歌脚本中的数组中
- 将谷歌地图 api latLng 坐标存储到 js 数组
- 谷歌地图API v3动态标记,帮助使数组工作
- PHP 在谷歌地图上显示多维数组值
- 谷歌图表;未捕获错误:不是数组
- 从谷歌图表中的DataView类获取值到javascript数组
- 在谷歌地图组标记
- 如何将谷歌群组嵌入到网页中