我正在使用Highcharts制作气泡图。这是我的数据样本:
name | price | quantity | count
--------+-------+----------+-------
Female | 2 | 3 | 5
Female | 3 | 12 | 10
Female | 5 | 6 | 15
Female | 1 | 7 | 25
Male | 3 | 5 | 7
Male | 2 | 9 | 11
Male | 5 | 7 | 23
Male | 4 | 4 | 14
我使用PHP查询数据并编码为JSON:
$query = "SELECT name, price, quantity, count FROM sales WHERE id = $1";
$result = pg_prepare($db, "report", $query);
$result = pg_execute($db, "report", array($ID));
while ($row = pg_fetch_array($result, NULL, PGSQL_ASSOC))
{
$response['xdata'][$row['name']]['x'][] = $row['price'];
$response['xdata'][$row['name']]['y'][] = $row['quantity'];
$response['xdata'][$row['name']]['radius'][] = $row['count'];
}
echo json_encode($response);
然而,为了正确绘制图形,所需的JSON格式如下:
series: [{
name: 'Female',
marker:{
symbol:'circle',
fillColor:'rgba(24,90,169,.5)',
lineColor:'rgba(24,90,169,.75)',
lineWidth:1,
color:'rgba(24,90,169,1)',
states:{
hover:{
enabled:false
}
}
},
data: [{x:2,y:3,marker:{radius:5}},
{x:3,y:12,marker:{radius:10}},
{x:5,y:6,marker:{radius:15}},
{x:1,y:7,marker:{radius:25}}]
},{
name: 'Male',
marker:{
symbol:'circle',
fillColor:'rgba(238,46,47,.5)',
lineColor:'rgba(238,46,47,.75)',
lineWidth:1,
color:'rgba(238,46,47,1)',
states:{
hover:{
enabled:false
}
}
},
data: [{x:3,y:5,marker:{radius:7}},
{x:2,y:9,marker:{radius:11}},
{x:5,y:7,marker:{radius:23}},
{x:4,y:4,marker:{radius:14}}]
}]
我的问题是,如何在PHP中正确处理$query
,以获得如上所述的所需JSON格式,并通过类似optionsBubble.series = data.xdata
的东西将其传递给series
?非常感谢!
您首先必须将非数据库相关的部分构建到PHP结构中,例如
$data = array(
0 => array(
'name' => 'Female',
'marker' => array (
'symbol': 'circle'
etc....),
'data' => array() // database insertion occurs here
),
1 => array(
'name' => 'Male',
etc...
)
);
$locations = array('Female' => 0, 'Male' => 1, etc...) // reverse map your 'name' fields
while(...) {
$data[$locations[$row['name']]][data]['x'][] = $row['price'];
$data[$locations[$row['name']]][data]['y'][] = $row['quantity'];
^^^^^^^^^^^^^^^^^^^^^^^^--- reverse lookup to get right array index for 'name'
}
首先,我建议您查看SQL查询,尤其是WHERE id=$1
部分。如果我没有错的话(在这一点上,我相当确定。)您的查询将返回一(1)行,这与您可能想要的不太一样。我建议删除WHERE
子句,看看这是否能解决您的问题。
如果不给我写信,我会看看我还能看到什么,我们可以从那里出发。