php - 通过 PHP 将 SQL 处理为 JSON 以用于气泡图

Question

我正在开发 Bubble Chart使用 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);

但是，为了正确绘制 graph ，所需的 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 .series = data.xdata？非常感谢!

Answer 1

您首先必须将与数据库无关的部分构建到您的 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'
}