php - How to put data from MySQL table to Google Chart API? -

i'm struggling work out how make chart out of data have, heres example.

my chart dummy data should this:

function drawchart() {     var data = google.visualization.arraytodatatable([     ['month', 'margarita murphy', 'lora gonzales', 'mario moran', 'wefico local faire', 'zegko collection', 'saxux program youth', 'test new location venue'],     ['4/12', 9, 74, 10, 8, 93, 33, 90],      ['5/12', 10, 168, 0, 10, 198, 108, 154],      ['6/12', 9, 174, 12, 12, 165, 96, 261],      ['7/12', 12, 288, 8, 36, 180, 264, 140],      ['8/12', 40, 275, 15, 30, 275, 395, 170],      ['9/12', 54, 534, 30, 48, 240, 246, 552],      ['10/12', 28, 518, 63, 28, 182, 672, 98],      ['11/12', 56, 520, 8, 64, 424, 568, 704],      ['12/12', 45, 675, 9, 63, 864, 567, 756],      ['1/13', 90, 570, 40, 70, 350, 510, 150],      ['2/13', 55, 946, 110, 55, 253, 429, 88],      ['3/13', 96, 684, 12, 96, 528, 1140, 468],      ['4/13', 52, 832, 104, 130, 1261, 1235, 663],      ['5/13', 28, 756, 70, 70, 1050, 910, 728],      ['6/13', 105, 930, 15, 60, 1440, 660, 690],      ['7/13', 144, 1600, 96, 64, 1312, 1488, 1120],  ]); 

so can see has list of items, how many views has had per month going in time.

the problem having when data mysql want loop though views, going vertically down in column, instead of accross. how go making go horizontal so:

['4/12', item1.views, item2.view] ['5/12', item1.views, item2.view] 

i'm getting confused this...

example data

------------------- |date|views|ref_id| |----|-----|------| |4/12|123  |2     | |5/12|526  |7     | |6/12|2    |1     | |7/12|46   |3     | ------------------- 

edit:

playing around setting dates variables first , looping though , adding data correct one?

$month4_12 = "['4/12', "; $month5_12 = "['5/12', ";  foreach($views_data $data){     ${"month_$data->date"} .= $data->views . ', '; }  $month4_12 .= "],"; $month5_12 .= "],"; 

edit2:

so here's have now, has few problems though, if views table doesn't contain record, doesn't count goes off finds in database... no doesn't work doesn't have correct amount of columns compared titles.

// views chart $views_data = $this->content_model->get_chart_view_data();  // first make months $month = 1; while($month <= 16){     $month_text = date('d/m/y');     $month_text = strtotime($month_text . ' -'.$month.' months');     $month_text_display = date('n/y', $month_text);     $month_text_variable = str_replace('/', '_', $month_text_display);     ${"month_$month_text_variable"} = "['".$month_text_display."', ";      // add data     foreach($views_data $row){         ${"month_$month_text_variable"} .= $row->views . ', ';     }     ${"month_$month_text_variable"} = rtrim(${"month_$month_text_variable"}, ", ");      // finish lines     ${"month_$month_text_variable"} .= "],\n";      $month++; }  // join lot! $month = 1; $chart_data = ''; while($month <= 16){     $month_text = date('d/m/y');     $month_text = strtotime($month_text . ' -'.$month.' months');     $month_text_display = date('n/y', $month_text);     $month_text_variable = str_replace('/', '_', $month_text_display);     $chart_data .= ${"month_$month_text_variable"};     $month++; }  $data['chart_data'] = rtrim($chart_data, ",\n");  echo $data['chart_data']; 

this gives output:

function drawchart() {         var data = google.visualization.arraytodatatable([         ['month', 'margarita murphy', 'lora gonzales', 'mario moran', 'wefico local faire', 'zegko collection', 'saxux program youth', 'test new location venue'],         ['7/13', 2, 1, 1],         ['6/13', 2, 1, 1],         ['5/13', 2, 1, 1],         ['4/13', 2, 1, 1],         ['3/13', 2, 1, 1],         ['2/13', 2, 1, 1],         ['1/13', 2, 1, 1],         ['12/12', 2, 1, 1],         ['11/12', 2, 1, 1],         ['10/12', 2, 1, 1],         ['9/12', 2, 1, 1],         ['8/12', 2, 1, 1],         ['7/12', 2, 1, 1],         ['6/12', 2, 1, 1],         ['5/12', 2, 1, 1],         ['4/12', 2, 1, 1] ]); 

edit 3

heres how views data stored in database, can see day no views has no record

you need pivot data in sql query. since mysql doesn't support pivots natively, have cheat bit. each pivoted column in final output in form:

if(ref_id = <this column's reference id>, views, 0) <column name> 

and group date column, this:

select     data,     if(ref_id = 327, views, 0) column_327,     if(ref_id = 329, views, 0) column_329,     // etc... <table name> <conditions> group date 

then can iterate on output build datatable object.

if don't know of ref_id values ahead of time (or there lot of them), can query list of ref_id's , build query programmatically:

select distinct ref_id <table name>