Not able to generate a Google Chart using MySQL table data as the data source
There are several things to adjust. I'll try to itemize them.
- Put everything on the one file, this will reduce confusion and there is no real benefit to splitting the codes.
- Swap out your
mysql_functions for at leastmysqli_functions. - Move your google visualization javascript code block to a line that follows the DOM element which will receive it. (if you try to put content into a non-existent element, you aren't going to see anything.)
- No sample data is provided in your question, so I don't know if
$jsonTableis providing the correct data -- you will need to assess this yourself.
As you implement the above correction, be sure to check your code for server-side errors.
If you have no server-side errors, but the graphic is not being displayed, then:
- Check your page's source code and confirm that the
DateTable()function contains the correct/actual data string from your$jsonTablevariable. - Access your browser's Developer Tools interface and look for any error messages. These will be client-side errors.
If you are still stuck, desire more information, or want to see similar working examples, go here.
And here is the Google Charts Reference Page
Google Chart Tools with PHP & MySQl
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)."]}";
Using Google charts with php/mysqli - cannot get it working
I changed it to this:
<?php echo $jsonTable; ?>
I even more confused now as to why all the examples listed on SO are given and they say they work.
MySQL to google charts PHP
Thanks for all your help in the design, I had several mistakes but the crucial mistake was that I used the wrong format for date, datetime from MySQL database, when I changed it from date / datetime to string the code was complete!
$table['cols']=array(
array('label' => 'Date', type=>'string'),
array('label' => 'Temp 1', type=>'number'),
array('label' =>'Temp 2', type=>'number'),
);
Jason result:
new google.visualization.DataTable({"cols":[{"label":"Date","type":"string"},{"label":"Temp 1","type":"number"},{"label":"Temp 2","type":"number"}],"rows":[{"c":[{"v":"2013-10-05 20:41:00"},{"v":10.75},{"v":4}]}
MySQL to JSON results in empty Google visualisation chart
A datetime-value can't be provided via a string like e.g. a mysql-timestamp, the API requires a JS-Date-object.
Such an object may not be transported via JSON, but the API supports a particular string-format(see: https://developers.google.com/chart/interactive/docs/datesandtimes#dates-and-times-using-the-date-string-representation)
e.g. the Mysql-Date:
2015-08-21 19:00:00
must become the string
Date(2015,7,21,19,0,0,0)
(note that JS starts counting the months with 0, so e.g. August is 7 not 8)
You may build the string directly in your SELECT-statement.
Other issues:
- you should use the mysqli-functions instead of mysql(they are deprecated/outdated)
- you should not run
$.ajaxsynchronously - you didn't populate a rows-array with the columns
Try it like this:
data.php
<?php
ob_start();
header('Content-Type:application/json');
//use your custom data
$dbhost ='localhost';
$dbuser ='username';
$dbpass ='password';
$dbname ='db-name';
$dbtable ='table-name';
//db-connection
$mysqli = new mysqli($dbhost,$dbuser,$dbpass,$dbname);
if (mysqli_connect_errno()) {
printf("Connect failed: %s\n", mysqli_connect_error());
exit();
}
//prepare the datatable
$table = array('cols'=>array(
array('label' => 'DateTime',
'type' => 'datetime'),
array('label' => 'Temperature',
'type' => 'number')),
'rows'=>array()
);
//build the query
$sql="SELECT
CONCAT(
'Date(',
YEAR(datetime),
',',
Month(datetime)-1,
DATE_FORMAT(datetime,',%e,%k,%i,'),
'0)'
)
as `datetime`,
`temp`
FROM `{$dbtable}`";
//run the query
if ($result = $mysqli->query($sql)) {
while ($row = $result->fetch_assoc()) {
//populate the rows-array
$table['rows'][]=array('c'=>array(
array('v'=>$row['datetime']),
array('v'=>$row['temp'])
)
);
}
$result->close();
}
ob_end_clean();
die(json_encode($table));
?>
HTML-File:
<body>
<div id="chart_div" style="height:300px;"></div>
<script src="http://code.jquery.com/jquery-latest.js"></script>
<script type="text/javascript" src="https://www.google.com/jsapi"></script>
<script type="text/javascript">
/*<![CDATA[*/
google.load('visualization', '1', {packages: ['corechart']});
google.setOnLoadCallback(drawChart);
function drawChart() {
$.getJSON('data.php',function(data){
var data = new google.visualization.DataTable(data);
var chart = new google.visualization
.LineChart(document.getElementById('chart_div'));
chart.draw(data, {});
});
}
/*]]>*/
</script>
</body>
Google Table Chart from php created Json
in order to create a DataTable directly from the jsonData,
it must be in a format google understands
following is an example of the standard format,
found in the examples under the reference for the DataTable constructor method
var data = new google.visualization.DataTable({
cols: [
{id: 'task', label: 'Task', type: 'string'},
{id: 'hours', label: 'Hours per Day', type: 'number'}
],
rows: [
{c:[{v: 'Work'}, {v: 11}]},
{c:[{v: 'Eat'}, {v: 2}]},
{c:[{v: 'Commute'}, {v: 2}]},
{c:[{v: 'Watch TV'}, {v:2}]},
{c:[{v: 'Sleep'}, {v:7, f:'7.000'}]}
]
});
if the jsonData is not in this format, then it must be manipulated accordingly
there are several ways to add columns and rows to a DataTable
in the following working snippet,
the keys from the first object {} in the jsonData are used to create the columns
also, recommend not using async: false
google.charts.load('current', { callback: function () {
// get json $.ajax({ url: "momJson.php", dataType:"json" }).done(function (jsonData) { // success drawTable(jsonData); }).fail(function (jq, text) { // failure //console.log(text);
// included here for example purposes drawTable([{"schoolid":"10007","prizeprogram":"APP","datereceived":"20160415","numberstudents":"292","participatingstudents":"69","studentresponse":"0.23","numberbooklets":"91","numbernames":"762","numberorders":"43","orderresponse":"0.05","tov":"1606.52","numbersubscriptions":"64","saleweek":"160122","vpn":"2.10","vps":"5.50","schoolcat":"7","lowgrade":"K","highgrade":"5","schooltype":"E","schoolname":"Iaeger Elementary School","schoolcity":"Iaeger","schoolstate":"WV","dateupdated":"2016-06-06 11:57:32","testcode":"A","mdrcode":"1\r"},{"schoolid":"10013","prizeprogram":"RSR","datereceived":"20160208","numberstudents":"256","participatingstudents":"58","studentresponse":"0.22","numberbooklets":"78","numbernames":"638","numberorders":"54","orderresponse":"0.08","tov":"2111.23","numbersubscriptions":"80","saleweek":"160108","vpn":"3.30","vps":"8.24","schoolcat":"7","lowgrade":"PK","highgrade":"5","schooltype":"E","schoolname":"Mount Hope Elementary School","schoolcity":"Mount Hope","schoolstate":"WV","dateupdated":"2016-06-06 11:57:33","testcode":"","mdrcode":"1\r"},{"schoolid":"10027","prizeprogram":"RBR","datereceived":"20150914","numberstudents":"315","participatingstudents":"49","studentresponse":"0.15","numberbooklets":"57","numbernames":"540","numberorders":"52","orderresponse":"0.09","tov":"1716.61","numbersubscriptions":"73","saleweek":"150904","vpn":"3.17","vps":"5.44","schoolcat":"7","lowgrade":"PK","highgrade":"8","schooltype":"E","schoolname":"Webster Springs Elementary School","schoolcity":"Webster Springs","schoolstate":"WV","dateupdated":"2016-06-06 11:57:32","testcode":"","mdrcode":"1\r"},{"schoolid":"10051","prizeprogram":"WCR","datereceived":"20150923","numberstudents":"240","participatingstudents":"41","studentresponse":"0.17","numberbooklets":"53","numbernames":"459","numberorders":"56","orderresponse":"0.12","tov":"1745.10","numbersubscriptions":"69","saleweek":"150911","vpn":"3.80","vps":"7.27","schoolcat":"7","lowgrade":"PK","highgrade":"4","schooltype":"E","schoolname":"Sutton Elementary School","schoolcity":"Sutton","schoolstate":"WV","dateupdated":"2016-06-06 11:57:33","testcode":"","mdrcode":"1\r"},{"schoolid":"10052","prizeprogram":"RSR","datereceived":"20160217","numberstudents":"675","participatingstudents":"212","studentresponse":"0.31","numberbooklets":"241","numbernames":"2338","numberorders":"287","orderresponse":"0.12","tov":"10415.68","numbersubscriptions":"407","saleweek":"160129","vpn":"4.45","vps":"15.43","schoolcat":"7","lowgrade":"K","highgrade":"5","schooltype":"E","schoolname":"Daniels Elementary School","schoolcity":"Daniels","schoolstate":"WV","dateupdated":"2016-06-06 11:57:33","testcode":"5","mdrcode":"1\r"},{"schoolid":"10053","prizeprogram":"RSWB","datereceived":"20160205","numberstudents":"351","participatingstudents":"204","studentresponse":"0.58","numberbooklets":"231","numbernames":"2250","numberorders":"391","orderresponse":"0.17","tov":"14056.06","numbersubscriptions":"545","saleweek":"160115","vpn":"6.24","vps":"40.04","schoolcat":"7","lowgrade":"K","highgrade":"5","schooltype":"E","schoolname":"Arthur I Boreman Elementary School","schoolcity":"Middlebourne","schoolstate":"WV","dateupdated":"2016-06-06 11:57:33","testcode":"","mdrcode":"1\r"},{"schoolid":"10057","prizeprogram":"RSWS","datereceived":"20160302","numberstudents":"265","participatingstudents":"64","studentresponse":"0.24","numberbooklets":"84","numbernames":"707","numberorders":"106","orderresponse":"0.14","tov":"3932.80","numbersubscriptions":"158","saleweek":"160205","vpn":"5.56","vps":"14.84","schoolcat":"7","lowgrade":"K","highgrade":"5","schooltype":"E","schoolname":"Eastbrook Elementary School","schoolcity":"Winfield","schoolstate":"WV","dateupdated":"2016-06-06 11:57:33","testcode":"","mdrcode":"1\r"},{"schoolid":"10065","prizeprogram":"RBR","datereceived":"20150921","numberstudents":"210","participatingstudents":"26","studentresponse":"0.12","numberbooklets":"34","numbernames":"290","numberorders":"42","orderresponse":"0.14","tov":"1539.27","numbersubscriptions":"60","saleweek":"150911","vpn":"5.30","vps":"7.32","schoolcat":"7","lowgrade":"PK","highgrade":"5","schooltype":"E","schoolname":"Salt Rock Elementary School","schoolcity":"Salt Rock","schoolstate":"WV","dateupdated":"2016-06-06 11:57:32","testcode":"","mdrcode":"1\r"},{"schoolid":"10066","prizeprogram":"WCR","datereceived":"20150828","numberstudents":"220","participatingstudents":"67","studentresponse":"0.30","numberbooklets":"94","numbernames":"743","numberorders":"66","orderresponse":"0.08","tov":"2371.85","numbersubscriptions":"96","saleweek":"150814","vpn":"3.19","vps":"10.78","schoolcat":"7","lowgrade":"PK","highgrade":"5","schooltype":"E","schoolname":"Cox Landing Elementary School","schoolcity":"Lesage","schoolstate":"WV","dateupdated":"2016-06-06 11:57:34","testcode":"","mdrcode":"1\r"},{"schoolid":"10095","prizeprogram":"APP","datereceived":"20160223","numberstudents":"300","participatingstudents":"50","studentresponse":"0.16","numberbooklets":"63","numbernames":"552","numberorders":"49","orderresponse":"0.08","tov":"1857.94","numbersubscriptions":"77","saleweek":"160122","vpn":"3.36","vps":"6.19","schoolcat":"7","lowgrade":"PK","highgrade":"6","schooltype":"E","schoolname":"Beale Elementary School","schoolcity":"Gallipolis Ferry","schoolstate":"WV","dateupdated":"2016-06-06 11:57:32","testcode":"A","mdrcode":"1\r"},{"schoolid":"10104","prizeprogram":"WCR","datereceived":"20150929","numberstudents":"80","participatingstudents":"35","studentresponse":"0.43","numberbooklets":"45","numbernames":"386","numberorders":"48","orderresponse":"0.12","tov":"1708.43","numbersubscriptions":"67","saleweek":"150918","vpn":"4.42","vps":"21.35","schoolcat":"7","lowgrade":"PK","highgrade":"5","schooltype":"E","schoolname":"Hillsboro Elementary School","schoolcity":"Hillsboro","schoolstate":"WV","dateupdated":"2016-06-06 11:57:34","testcode":"","mdrcode":"1\r"},{"schoolid":"10143","prizeprogram":"WCR","datereceived":"20150915","numberstudents":"400","participatingstudents":"172","studentresponse":"0.43","numberbooklets":"206","numbernames":"1899","numberorders":"193","orderresponse":"0.10","tov":"6286.01","numbersubscriptions":"251","saleweek":"150821","vpn":"3.31","vps":"15.71","schoolcat":"7","lowgrade":"PK","highgrade":"2","schooltype":"E","schoolname":"Moorefield Elementary School","schoolcity":"Moorefield","schoolstate":"WV","dateupdated":"2016-06-06 11:57:34","testcode":"","mdrcode":"1\r"},{"schoolid":"10159","prizeprogram":"RSR","datereceived":"20160203","numberstudents":"165","participatingstudents":"68","studentresponse":"0.41","numberbooklets":"89","numbernames":"756","numberorders":"48","orderresponse":"0.06","tov":"1588.47","numbersubscriptions":"65","saleweek":"160108","vpn":"2.10","vps":"9.62","schoolcat":"7","lowgrade":"K","highgrade":"5","schooltype":"E","schoolname":"Prichard Elementary School","schoolcity":"Prichard","schoolstate":"WV","dateupdated":"2016-06-06 11:57:33","testcode":"","mdrcode":"1\r"}]); });
}, packages:['table']});
function drawTable(jsonData) { var data = new google.visualization.DataTable();
jsonData.forEach(function (row, index) { // load columns if (index === 0) { Object.keys(row).forEach(function (key) { data.addColumn({ label: key, type: 'string' }); }); }
// load rows var dataRow = []; // load value for each column for (var i = 0; i < data.getNumberOfColumns(); i++) { // ensure each row has value for key if (row.hasOwnProperty(data.getColumnLabel(i))) { dataRow.push(row[data.getColumnLabel(i)]); } else { dataRow.push(null); } } data.addRow(dataRow); });
var table = new google.visualization.Table(document.getElementById('table_div'));
table.draw(data, {showRowNumber: true, width: '100%', height: '100%'});}<script src="https://ajax.googleapis.com/ajax/libs/jquery/2.1.1/jquery.min.js"></script><script src="https://www.gstatic.com/charts/loader.js"></script><div id="table_div"></div>Building a Google Chart with PHP and MySQL
Try this as your PHP:
$query = mysql_query("
SELECT
'Like' as 'preference',
SUM(IF(Apple = 'Like', 1, 0)) as Apple,
SUM(IF(Orange = 'Like', 1, 0)) as Orange,
SUM(IF(Strawberry = 'Like', 1, 0)) as Strawberry
FROM data
UNION
SELECT
'Dislike' as 'preference',
SUM(IF(Apple = 'Dislike', 1, 0)) as Apple,
SUM(IF(Orange = 'Dislike', 1, 0)) as Orange,
SUM(IF(Strawberry = 'Dislike', 1, 0)) as Strawberry
FROM data
");
$table = array();
$table['cols'] = array(
array('label' => 'preference', 'type' => 'string'),
array('label' => 'Apple', 'type' => 'number'),
array('label' => 'Orange', 'type' => 'number'),
array('label' => 'Strawberry', 'type' => 'number')
);
$rows = array();
while($r = mysql_fetch_assoc($query)) {
$temp = array();
$temp[] = array('v' => $r['preference']);
$temp[] = array('v' => (int) $r['Apple']);
$temp[] = array('v' => (int) $r['Orange']);
$temp[] = array('v' => (int) $r['Strawberry']);
$rows[] = array('c' => $temp);
}
$table['rows'] = $rows;
$jsonTable = json_encode($table);
echo $jsonTable;
The SQL should return two rows of data, one a sum of likes and the other a sum of dislikes, which then gets parsed into the Google Visualization API DataTable format and echo'd as a JSON string. This is good for use as an AJAX data source for the chart, but with a minor modification, it would be suitable for directly outputting the data into the javascript for drawing a chart.
Related Topics
Show Values from a MySQL Database Table Inside a HTML Table on a Webpage
Get Value from Simplexmlelement Object
How to Set HTML Value Attribute (With Spaces)
How to Push Both Value and Key into PHP Array
Getting Dom Elements by Classname
What Does $$ (Dollar Dollar or Double Dollar) Mean in PHP
Force File Download With PHP Using Header()
Reference Assignment Operator in PHP, =&
How to Find the Mime Type of a File With PHP
PHP: How to Handle ≪![Cdata[ With Simplexmlelement