convert mysql resultset into a (name, data) object to be fed into HighCharts

Question

I am using HighCharts Bar graph to plot data from mysql resultset into a bar graph.

Now the resultset of my query is as below:

Name       Expense
-----------------
July       700.0000
August     450.0000  
September  1700.0000

The series property of HighCharts require data in below format to plot the graph

[
 {name:"July",data:[700.0000]},
 {name:"August",data:[450.0000]},
 {name:"September",data:[1700.0000]}
] 

So I thought of coverting my resultset into a JSON object using json_encode($row). But I got the following output:

[{"name":"July","data":"700.0000"},
 {"name":"August","data":"450.0000"},
 {"name":"September","data":"1700.0000"}] 

Doubts:

1. Is there a way to get/convert the resultset in exactly the same format as is required by the series property of HighCharts?

2. Also can I use an object of created in the php block, directly into the javascript? Say I create an object $jsonNameData out of my resultset. Then can I use it in the javascript as

   series: <? echo $jsonNameData ?>

EDIT:

I was able to solve Q1 by doing the following:

$count = 0;
$strSeries = "[";
while($r = mysql_fetch_assoc($result)) {

   if($count == 0){
       $strSeries .= "{name:'" . $r['name'] . "',";
       $strSeries .= "data:[" . $r['data'] . ']}';
       $count = 1;
   }
   else {
       $strSeries .= ",{name:'" . $r['name'] . "',";
       $strSeries .= "data:[" . $r['data'] . ']}';
   }
 $rows[] = $r;
}
$strSeries .= "]";

Got the required string into $strSeries.

Now the problem is the second question. I assigned the value of $strSeries to a variable in javascript but when I use that variable as

series: variableName

It is not plotting the graph properly even though the variable has proper value (checked through alert).

Answer 1

Not got a chance to run the code below, but this should work or something very similar to this

$series=array();
while($item = mysql_fetch_assoc($result)) {
  $serie=array(
     "name" => $item['name'],
     "data" => array(floatval($item['data']))
  );
  array_push($series,$serie);
}
echo json_encode($series);

An advice is to always stick to json_encode() for doing the jsonification. You may want to go through this example on the reference page to learn about how json_encode works with arrays in particular

EDIT: To answer your 2nd question. You may be getting a highchart error #14 in the javascript console? Have a look at this question Highcharts returning error 14

P.S. Please don't mix multiple questions in one, post them separately, also use tools like javascript console and SO search more effectively

Answer 2

I would try this :

$jsonObj = '[';
foreach($mysqlResul as $item){
    $jsonObj .= '{"name":"'.$item['name'].'","data":['.$item['data'].']},';
}
// strip off last comma
$jsonObj = substr(0,strlen($jsonObj)-1,$jsonObj);
$jsonObj .= ']';

Answer 3

select concat('{name:"', name, '",data:[', expense, ']}') as foo
from bar