Transform MySql query result to proper JSON data
<p>I want to visualise my data with <a href="https://developers.google.com/chart/interactive/docs/gallery/columnchart#stacked-column-charts" rel="nofollow">Google Column Chart</a>. But I can't get appropriate data structure from MySql.</p>

<p>My current query results in:</p>

<pre><code> array(6){ 
   [0]=&gt; array(3){["id"]=&gt;"1" ["count"]=&gt;"6" ["day"]=&gt;"15-01-01"} 
   [1]=&gt; array(3){["id"]=&gt;"2" ["count"]=&gt;"3" ["day"]=&gt;"15-01-01" }
   [2]=&gt; array(3){["id"]=&gt;"3" ["count"]=&gt;"1" ["day"]=&gt;"15-01-01" } 
   [3]=&gt; array(3){["id"]=&gt;"1" ["count"]=&gt;"6" ["day"]=&gt;"15-01-02" } 
   [4]=&gt; array(3){["id"]=&gt;"2" ["count"]=&gt;"4" ["day"]=&gt;"15-01-02" }
   [5]=&gt; array(3){["id"]=&gt;"3" ["count"]=&gt;"6" ["day"]=&gt;"15-01-02" } 
} 
</code></pre>

<p>While Google Charts requires following format:</p>

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

<p>How can I transform my data to appropriate format?</p>

<p><strong>UPDATE</strong></p>

<p>I tried this:</p>

<pre><code>$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 .= "]);";
</code></pre>

<p>And this gave me following result:</p>

<pre><code>  "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,],]);"
</code></pre>

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

<pre><code> ['1', 6, 6,],['2', 3, 4,],['3', 1, 6,],
</code></pre>