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,
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>
Google chart tools are powerful, simple to use, and free. Try out our rich gallery of interactive charts and data tools.
You can easily fetch data from the database server using PHP and use them for creating a chart based on dynamic data. If you want to create a more attractive chart and faster download, then you can use any popular client-side library.
From simple line charts to complex hierarchical tree maps, the chart gallery provides a large number of ready-to-use chart types. The most common way to use Google Charts is with simple JavaScript that you embed in your web page.
Google Charts is an interactive web service that creates graphical charts from user-supplied information. Chart. js is an open-source JavaScript library that allows you to draw different types of charts by using the HTML5 canvas element.
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)."]}";
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With