Google Chart Tools with PHP & MySQl

Question

I'm trying to create a chart using the Google Visualization API, with PHP & MySQL in the background.

What I'm doing is:

getting the data from db using PHP / SQL

$sth = mysql_query("SELECT * FROM Chart");

creating JSON with PHP

$rows = array();

while($r = mysql_fetch_assoc($sth)) {
  $rows[] = $r;
}

$jdata = json_encode($rows);

and then feeding Google Visualization API with JSON

var data = new google.visualization.DataTable(<?php echo $jdata ?>);

Just to make sure the JSON is actually in the correct format I did:

$jdata = json_encode($rows);
print $jdata;

which returned:

[{"id":"1","quarters":"1","salary":"1250"},{"id":"2","quarters":"2","salary":"2500"},{"id":"3","quarters":"3","salary":"4526"},{"id":"4","quarters":"4","salary":"4569"}]

So,

db connection is OK.
creating JSON from PHP array is OK.
JSON format is OK.

Firebug is returning an error saying:

Table has no columns. [Break On This Error] b,Sl),[b]}function Zq(a,b){var c=a[xc]..."].")):d(l("Table has no columns."))}

The question is how can I create columns from JSON data?

UPDATE:

Code used to create the graph below:

// SQL Query
$sth = mysql_query("SELECT * FROM Chart");
//$rows = array();

while($r = mysql_fetch_assoc($sth)) {
   if(!isset($google_JSON)){    
     $google_JSON = "{cols: [";    
     $column = array_keys($r);
     foreach($column as $key=>$value){
         $google_JSON_cols[]="{id: '".$key."', label: '".$value."'}";
     }    
     $google_JSON .= implode(",",$google_JSON_cols)."],rows: [";       
   }
   $google_JSON_rows[] = "{c:[{v: '".$r['id']."'}, {v: '".$r['quarters']."'}, {v: '".$r['salary']."'}]}";
}    

// you may need to change the above into a function that loops through rows, with $r['id'] etc, referring to the fields you want to inject..

$data = $google_JSON.implode(",",$google_JSON_rows)."]}";

Output HTML CODE:

        <!-- load Google AJAX API -->
    <script type="text/javascript" src="http://www.google.com/jsapi"></script>
    <script type="text/javascript">  
        //load the Google Visualization API and the chart  
        google.load('visualization', '1', {'packages':['columnchart']});  

        //set callback  
        google.setOnLoadCallback (createChart);  

        //callback function  
        function createChart() {  

            //create data table object  
            var data = new google.visualization.DataTable({cols: [{id: '0', label: 'id'},{id: '1', label: 'quarters'},{id: '2', label: 'salary'}],rows: [{c:[{v: '1'}, {v: '1'}, {v: '1250'}]},{c:[{v: '2'}, {v: '2'}, {v: '2500'}]},{c:[{v: '3'}, {v: '3'}, {v: '4526'}]},{c:[{v: '4'}, {v: '4'}, {v: '4569'}]}]});  

            //instantiate our chart objects  
            var chart = new google.visualization.ColumnChart (document.getElementById('chart'));  

            //define options for visualization  
            var options = {width: 400, height: 240, is3D: true, title: 'Company Earnings'};  

            //draw our chart  
            chart.draw(data, options);  

        }  
    </script>

    <div id="chart"></div>

When using the code above the script is creating the graph, but something is wrong there

SW4 · Accepted Answer · 2011-10-07T11:26:39.700

5

Per the docs, have you tried establishing the column references and data seperately?

var data = new google.visualization.DataTable();
data.addColumn('string', 'Task');
data.addColumn('number', 'Hours per Day');
data.addRows([
  ['Work', 11],
  ['Eat', 2],
  ['Commute', 2],
  ['Watch TV', 2],
  ['Sleep', {v:7, f:'7.000'}]
]);

To format into the correct JSON for the object, you can set it up as follows:

while($r = mysql_fetch_assoc($sth)) {
   if(!isset($google_JSON)){    
     $google_JSON = "{cols: [";    
     $column = array_keys($r);
     foreach($column as $key=>$value){
         $google_JSON_cols[]="{id: '".$key."', label: '".$value."'}";
     }    
     $google_JSON .= implode(",",$google_JSON_cols)."],rows: [";       
   }
   $google_JSON_rows[] = "{c:[{v: '".$r['id']."'}, {v: ".$r['quarters']."}, {v: ".$r['salary']."}]}";
}    
// you may need to change the above into a function that loops through rows, with $r['id'] etc, referring to the fields you want to inject..
echo $google_JSON.implode(",",$google_JSON_rows)."]}";

edited Oct 07 '11 at 11:26

answered Oct 07 '11 at 10:13

SW4

69,876
20
132
137

Yep, I did. And it does work. The quastion how to create column from JSON? – Iladarsda Oct 07 '11 at 10:16
JSON needs to be in the format: http://code.google.com/apis/chart/interactive/docs/reference.html#dataparam – SW4 Oct 07 '11 at 10:17
Any idea how can I convert that JSON into the correct format in JS / PHP ? – Iladarsda Oct 07 '11 at 10:21
I've updated the answer, you may need to tweak but it'll give you an approximate idea – SW4 Oct 07 '11 at 10:30
Does this mean the columns need to hard-coded? Can't they be extracted from JSON? I must admit it does look complicated. Thanks! – Iladarsda Oct 07 '11 at 10:45
No, not at all- you can spit them out during the routine- I've edited the above – SW4 Oct 07 '11 at 10:52
Ok, the script is creating the graph, but something is wrong there. Have a look http://awesomescreenshot.com/05flx352a. – Iladarsda Oct 07 '11 at 10:59
orry- I was referencing to the output code, e.d. the source you get when you right click the output page and click 'view source' – SW4 Oct 07 '11 at 11:16
OK, I've updated the code, though note that the graph is showing 'quarters' and 'salary' together, one is on a scale of tens, the other on thousands...so the graph isnt a great output – SW4 Oct 07 '11 at 11:27
If you only have four rows (one for each quarter), you may wish to change your select SQL to only show quarters and salary. – SW4 Oct 07 '11 at 11:29
Ok. It is working! Thank you very much for your time and effort! I will do some code analysing now! As you code is very mysterious to me ;) Thanks! – Iladarsda Oct 07 '11 at 11:33

score 0 · Answer 2 · edited May 23 '17 at 10:30

0

Complete Working Example: PHP/MYSQL/Google Chart/JSON

PHP MySQL Google Chart JSON - Complete Example

edited May 23 '17 at 10:30

Community

1
1

answered Nov 07 '12 at 22:20

Anam

11,999
9
49
63

This is a link-only answer. If the link goes down, the only useful information here is that there might be something called "PHP MYSQL Google Chart JSON Complete Example" that might be worth searching for, which does not answer the problem. – abarnert Sep 08 '14 at 19:20

Google Chart Tools with PHP & MySQl

2 Answers2

Linked