Transform MySql query result to proper JSON data

Question

I want to visualise my data with Google Column Chart. But I can't get appropriate data structure from MySql.

My current query results in:

 array(6){ 
   [0]=> array(3){["id"]=>"1" ["count"]=>"6" ["day"]=>"15-01-01"} 
   [1]=> array(3){["id"]=>"2" ["count"]=>"3" ["day"]=>"15-01-01" }
   [2]=> array(3){["id"]=>"3" ["count"]=>"1" ["day"]=>"15-01-01" } 
   [3]=> array(3){["id"]=>"1" ["count"]=>"6" ["day"]=>"15-01-02" } 
   [4]=> array(3){["id"]=>"2" ["count"]=>"4" ["day"]=>"15-01-02" }
   [5]=> array(3){["id"]=>"3" ["count"]=>"6" ["day"]=>"15-01-02" } 
}

While Google Charts requires following format:

   var data = google.visualization.arrayToDataTable([
    ['Date', '15-01-01', '15-01-02', { role: 'annotation' } ],
    ['1', 6, 6,],
    ['2', 3, 4,],
    ['3', 1, 6,],
  ]);

How can I transform my data to appropriate format?

UPDATE

I tried this:

$dateArray= array();
$output= "var data = google.visualization.arrayToDataTable([";
$output .= "['Date',";
foreach($mySqlResult as $val) {
   if ( in_array($val['day'], $dateArray) ) {
           continue;
        }
        $dateArray[] = $val['day'];
        $output .= "'".$val['day']."',";
    }
    $output .= " { role: 'annotation' } ],";

    foreach($mySqlResult as $val) {
        $output .= "['".$val['id']."',".$val['count']."],";
    }
    $output .= "]);";

And this gave me following result:

  "var data = google.visualization.arrayToDataTable(
       [['Date','15-01-01','15-01-02', { role: 'annotation' } ], 
        ['1',6,],['2',3,],['3',1,],['1',6,],['2',4,],['3',6,],]);"

Still not what is required. How can create row for every id with all counts:

 ['1', 6, 6,],['2', 3, 4,],['3', 1, 6,],

can you show your code and what is the name of google charts — Sharma Vikram, Feb 17 '16 at 10:45
You need to manipulate the output in PHP, you will find it extremely difficult for MySql to output the data in the correct format Google Charts require. — SK2017, Feb 17 '16 at 11:00
Here is a great starting point - http://stackoverflow.com/questions/12994282/php-mysql-google-chart-json-complete-example — SK2017, Feb 17 '16 at 11:01

score -1 · Answer 1 · answered Feb 17 '16 at 11:00

Try this, it might help you. I have formatted array in php to get desired output for demo script. You will just need to use correct use of loops.

<?php
$data = array(
   0=> array("id"=>"1","count"=>"6","day"=>"15-01-01"),
   1=> array("id"=>"2","count"=>"3","day"=>"15-01-01"),
   2=> array("id"=>"3","count"=>"1","day"=>"15-01-01"), 
   3=> array("id"=>"1","count"=>"6","day"=>"15-01-02"), 
   4=> array("id"=>"2","count"=>"4","day"=>"15-01-02"),
   5=> array("id"=>"3","count"=>"6","day"=>"15-01-02"), 
);

$output= "var data = google.visualization.arrayToDataTable([";
$output .= "['Date',";
foreach($data as $val) {
    $output .= "'".$val['day']."',";
}
$output .= " { role: 'annotation' } ],";

foreach($data as $val) {
    $output .= "['".$val['id']."',".$val['count'].",6,''],";
}
$output .= "]);";

echo $output;
?>

I didn't downvoted your answer but it seems to be the creepiest one over here you need to take care about those `braces` whereas you can simply create an array in PHP and simply use `json_encode`. — Narendrasingh Sisodia, Feb 17 '16 at 11:16
@Uchiha I tried his answer. I think it helped me to come closer to desired output. But still not exactly what I need. I am not very proficient with PHP. Could you show me how create and encode array in required structure? — , Feb 17 '16 at 11:45

Transform MySql query result to proper JSON data

1 Answers1