Process SQL to JSON through PHP for Bubble Chart

I'm working on a Bubble Chart using Highcharts. Here's a sample of my data:

  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

I'm using PHP to query the data and encode to 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);

However, the desired JSON format is as follows in order to properly plot the graph:

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}}]
   }]

My question is, how can I correctly process $query in PHP to get the desired JSON format as above and pass it to series through something like optionsBubble.series = data.xdata? Thanks a lot!

Answers


You'd first have to build the non-db-related parts into your PHP structure, e.g.

$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'
}

Need Your Help

get dynamic property defined in prototype during JSON.stringify

javascript prototype prototypal-inheritance

I've defined an enumerable property in the prototype object and would like it to appear when I convert a prototyped object to JSON.

Flex Tree does not selecting ("highlight”) the selectedIndices

actionscript-3 flex treeview flex4.5 itemrenderer

I have an array of items that should be selected in my tree control. As you can see from my code below,I bind this array to the selectedIndices property of the tree.

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.