Using Excel or MySQL as data source
192 views
Skip to first unread message
Shivakumar Chandrashekar
Oct 17, 2019, 6:49:47 PM10/17/19
to Google Visualization API
Friends, I am newbie for this. Just wanted to know if anyone used excel (preferably) or MySQL as data source and used this API. If yes, could you please share how to use ? any challenges faced ?
Michael Brown
Oct 18, 2019, 8:47:42 AM10/18/19
to google-visua...@googlegroups.com
I am trying to use MySQL as a data source for Gauge, but it's not working yet.
All of the examples I see have the Javascript in your Chart web page call a PHP script on the database server. The PHP accesses the MySQL database and formats the return data as JSON. The Javascript then feeds the JSON to the Google chart API. It's too bad that Google didn't include direct SQL handling in the Chart API.
While the Google documentation shows the expected format of the JSON data with row and col definitions, whatever I feed to the gauge causes it to not display *anything*, the gauge just vanishes on the page. And there is no error code or message being returned by the gauge, so I have no idea what is wrong, short of trying dozens of slightly different formats.
This is my general frustration with anything from Google, and I've done a LOT of Android coding for my own needs. They provide a powerful and useful system, but it's unnecessarily difficult to program to, and it's not documented well enough to show examples for all use cases and error handling is non-existent. And then they deprecate it a few years later and you're screwed.
When I get it working I'll come back and paste in the relevant code.
--
Mike Brown
"Driving a gasoline sports car is going to feel like a steam engine with a side of quiche." Elon Musk
Mike Brown
"Driving a gasoline sports car is going to feel like a steam engine with a side of quiche." Elon Musk
On Thu, Oct 17, 2019 at 6:50 PM Shivakumar Chandrashekar <csh...@gmail.com> wrote:
Friends, I am newbie for this. Just wanted to know if anyone used excel (preferably) or MySQL as data source and used this API. If yes, could you please share how to use ? any challenges faced ?
--
You received this message because you are subscribed to the Google Groups "Google Visualization API" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-visualizati...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-visualization-api/b9cffd23-1f20-4dfe-b0b2-6c0f2988a44d%40googlegroups.com.
Shivakumar Chandrashekar
Oct 18, 2019, 1:50:09 PM10/18/19
to Google Visualization API
Thanks Mike. Will wait :-)
To unsubscribe from this group and stop receiving emails from it, send an email to google-visualization-api+unsub...@googlegroups.com.
Jim Shaffer
Oct 18, 2019, 2:19:26 PM10/18/19
to google-visua...@googlegroups.com
Here's my use of MySQL (in PHP) for a simple line graph:
<html>
<?php
// Start MySQL Connection
include('connect.php');
$SQL="SELECT DATE(recdate) AS 'RecordDate',FORMAT(AVG(Temperature),1) AS 'Temp',FORMAT(AVG(Humidity),1) AS 'Humidity' ,FORMAT(AVG(AmbientTemp),1) AS 'Ambient' FROM ResDataLog WHERE ABS(DATEDIFF(recdate,NOW())) < 375 GROUP BY DATE(recdate) ORDER BY recdate"; //,FORMAT(AVG(Pressure),1) AS Pressure
$result=mysqli_query($dbh,$SQL);
$rowCount=mysqli_num_rows($result);
while ($row = mysqli_fetch_array($result)) {
$entry .= "['".$row{'RecordDate'}."',".$row{'Temp'}.",".$row{'Humidity'}.",".$row{'Ambient'}."],";
};
//echo "Entry Array: </br>" . $entry . "</br>";
//close the connection
mysqli_close($dbh);
?>
<head>
<script type="text/javascript" src="https://www.gstatic.com/charts/loader.js"></script>
<script type="text/javascript">
google.charts.load('current', {'packages':['corechart']});
google.charts.setOnLoadCallback(drawChart);
function drawChart() {
var data = google.visualization.arrayToDataTable([
['Date', 'Temp', 'Humidity', 'Ambient'],
<?php echo $entry ?>
]);
var options = {
title: 'Average Telemetry by Day',
curveType: 'function',
legend: { position: 'top' },
colors: ['black', 'blue', 'red']
};
var chart = new google.visualization.LineChart(document.getElementById('curve_chart'));
chart.draw(data, options);
}
</script>
</head>
<body>
<div id="curve_chart" style="width: 1200px; height: 600px"></div>
</body>
</html>
<?php
// Start MySQL Connection
include('connect.php');
$SQL="SELECT DATE(recdate) AS 'RecordDate',FORMAT(AVG(Temperature),1) AS 'Temp',FORMAT(AVG(Humidity),1) AS 'Humidity' ,FORMAT(AVG(AmbientTemp),1) AS 'Ambient' FROM ResDataLog WHERE ABS(DATEDIFF(recdate,NOW())) < 375 GROUP BY DATE(recdate) ORDER BY recdate"; //,FORMAT(AVG(Pressure),1) AS Pressure
$result=mysqli_query($dbh,$SQL);
$rowCount=mysqli_num_rows($result);
while ($row = mysqli_fetch_array($result)) {
$entry .= "['".$row{'RecordDate'}."',".$row{'Temp'}.",".$row{'Humidity'}.",".$row{'Ambient'}."],";
};
//echo "Entry Array: </br>" . $entry . "</br>";
//close the connection
mysqli_close($dbh);
?>
<head>
<script type="text/javascript" src="https://www.gstatic.com/charts/loader.js"></script>
<script type="text/javascript">
google.charts.load('current', {'packages':['corechart']});
google.charts.setOnLoadCallback(drawChart);
function drawChart() {
var data = google.visualization.arrayToDataTable([
['Date', 'Temp', 'Humidity', 'Ambient'],
<?php echo $entry ?>
]);
var options = {
title: 'Average Telemetry by Day',
curveType: 'function',
legend: { position: 'top' },
colors: ['black', 'blue', 'red']
};
var chart = new google.visualization.LineChart(document.getElementById('curve_chart'));
chart.draw(data, options);
}
</script>
</head>
<body>
<div id="curve_chart" style="width: 1200px; height: 600px"></div>
</body>
</html>
-------- Connect.php:
<?php
$MyUsername = ""; // enter your username for mysql
$MyPassword = ""; // enter your password for mysql
$MyHostname = ""; // this is usually "localhost" unless your database resides on a different server
$MyDBName = "";
//mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
$dbh = mysqli_connect($MyHostname , $MyUsername, $MyPassword, $MyDBName);
// Check connection
if (mysqli_connect_errno()) {
die('<br><p>{Connect.php} Failed to connect to MySQL: '.mysqli_connect_error().'</p>');
}
// else {
// echo '<br><p>{Connect.php} Connection to MySQL server successfully established.</p >';
// }
?>
$MyUsername = ""; // enter your username for mysql
$MyPassword = ""; // enter your password for mysql
$MyHostname = ""; // this is usually "localhost" unless your database resides on a different server
$MyDBName = "";
//mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
$dbh = mysqli_connect($MyHostname , $MyUsername, $MyPassword, $MyDBName);
// Check connection
if (mysqli_connect_errno()) {
die('<br><p>{Connect.php} Failed to connect to MySQL: '.mysqli_connect_error().'</p>');
}
// else {
// echo '<br><p>{Connect.php} Connection to MySQL server successfully established.</p >';
// }
?>
To unsubscribe from this group and stop receiving emails from it, send an email to google-visualizati...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-visualization-api/b9cffd23-1f20-4dfe-b0b2-6c0f2988a44d%40googlegroups.com.
--
You received this message because you are subscribed to the Google Groups "Google Visualization API" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-visualizati...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-visualization-api/78de08b8-f76b-4995-9717-d3884170fb9f%40googlegroups.com.
Jim
Kathleen Boilek
Oct 18, 2019, 2:31:05 PM10/18/19
to google-visua...@googlegroups.com
I am very interested in this as well. I experience the same except with SharePoint 2013 on-prem.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-visualization-api/CAC5J33SvR2sKtsxrd0EjyVq4tGjsGQys%3Dr38m-k-YGzT_6FcWQ%40mail.gmail.com.
Shiva
Oct 18, 2019, 7:23:07 PM10/18/19
to google-visua...@googlegroups.com
Thank you !
To view this discussion on the web visit https://groups.google.com/d/msgid/google-visualization-api/CAEo%2BXt2aH_BKpbLe5dD1AOmgYkddssujjsqz6909%2B8W7wnC%2BEQ%40mail.gmail.com.
Sent from Gmail Mobile
Michael Brown
Nov 25, 2019, 9:19:16 PM11/25/19
to google-visua...@googlegroups.com
Thanks for the guidance Jim. I changed my code from a separate php file and web page, into a single web page that includes the php MySQL access, javascript for the Google Charts API, CSS to center the Gauge and HTML for the DIVs to display the graphs. The use of
google.visualization.arrayToDataTable() was the key to getting it working.
--
Mike Brown
"Driving a gasoline sports car is going to feel like a steam engine with a side of quiche." Elon Musk
Mike Brown
"Driving a gasoline sports car is going to feel like a steam engine with a side of quiche." Elon Musk
To view this discussion on the web visit https://groups.google.com/d/msgid/google-visualization-api/CAEo%2BXt2aH_BKpbLe5dD1AOmgYkddssujjsqz6909%2B8W7wnC%2BEQ%40mail.gmail.com.
Reply all
Reply to author
Forward
0 new messages