Drawing charts from SQL database?

[HollyMolly]

Can I draw charts with Google Visualization API from data that I have
in SQL database?

[Roni Biran]

Sure thing. All you have to do is render your output datatables into google charts datatable objects.

On Sep 14, 2011, at 1:02 AM, HollyMolly <holly...@gmail.com> wrote:

Can I draw charts with Google Visualization API from data that I have
in SQL database?

--
You received this message because you are subscribed to the Google Groups "Google Visualization API" group.
To post to this group, send email to google-visua...@googlegroups.com.
To unsubscribe from this group, send email to google-visualizati...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/google-visualization-api?hl=en.

[ChartALot]

That is correct. However, this is made a lot simpler if you use the Data Source Library. You probably want to look at the SqlDataSourceServlet.

HTH

[Richard]

Hi Holly,

I'm trying to do the same, i have my google chart working with hard coded values however struggling to connect it to a database. Did you manage to create a graph using data from an SQL database?

Thanks

[Bluestreak2k5]

Loop through an array of data, after creating an array from the
database.

var data = new google.visualization.DataTable();
length = stateArray.length
data.addRows(length);
data.addColumn('string', 'State');
data.addColumn('number', 'State Rank');
for( var i=0; i<length; i++)
{
data.setValue(i, 0, stateArray[i]);
data.setValue(i, 1, stateArray[i][stateRank]);
}

Thats how I do it. There are probably easier ways to do it.

[Roni Biran]

You can create a structured datatable, dataset or even use Linq and structured objects to render a google datatable so that the charts will know how to use them. I, for instance use a webservice that renders a JavaScript object (JSON) and just eval it in my JavaScript. After that I can do whatever I want. 

[Richard]

Hello,

Sorry its taken a while to reply, its been a busy past couple of weeks due to the season.

I'm new to Google charts and wouldnt know where to start in attempting to connect an SQL database to my chart. I successfully have a chart working using hard coded values; but, I've searched the web for help, do you know of any tutorials that show how to connect a Google Chart to an SQL database? 

Many Thanks

[Jinji]

http://code.google.com/apis/chart/interactive/docs/php_example.html

or

http://code.google.com/apis/chart/interactive/docs/queries.html plus http://code.google.com/apis/chart/interactive/docs/dev/implementing_data_source.html

--

You received this message because you are subscribed to the Google Groups "Google Visualization API" group.

To view this discussion on the web visit https://groups.google.com/d/msg/google-visualization-api/-/oZYf8A46uUIJ.

[Richard]

Hello Yuval,

Can Google Charts not read from an SQL database?

Many Thanks

[asgallant]

It cannot read directly from the database - you have to use PHP or other server-side scripting to pull the data out of the database and put it in a format the API can understand.  The easiest way using PHP is to query your database and output the data directly inside the javascript function (in place of the hard-coded data).

[Jinji]

Right. To put it in other words, the Google Visualization API is a javascript library that runs in the browser. As such, all it can do is issue HTTP requests. If your database has some HTTP interface, then you can write javascript code that queries it. Since there's no standard database HTTP interface which many databases support (or at least not one I'm aware of), the library can't provide methods for such HTTP queries.

Bottom line, you'll have to run some server-side code to query the database. It can be done in any way you like it (such as PHP scripting), and that is the first link I mentioned, or in a server-side Java library we wrote and provided, and that's the second link.

On Tue, Jan 10, 2012 at 5:46 PM, asgallant <drew_g...@abtassoc.com> wrote:

It cannot read directly from the database - you have to use PHP or other server-side scripting to pull the data out of the database and put it in a format the API can understand.  The easiest way using PHP is to query your database and output the data directly inside the javascript function (in place of the hard-coded data).

--

You received this message because you are subscribed to the Google Groups "Google Visualization API" group.

To view this discussion on the web visit https://groups.google.com/d/msg/google-visualization-api/-/3k_CWKNeIY0J.

[asgallant]

While I don't generally suggest building the DataTable like this (I find it more productive to learn the JSON format and use that instead - you will be happier down the road if/when you need to make a lot of charts pull from your database), you can save yourself a bit of code and avoid some potential problems here with a slightly different approach:

<?php
$con = mysql_connect("blabla","blabla","passwordblabla");

if (!$con) {
    die('Could not connect: ' . mysql_error());
}

mysql_select_db("robotfriend_org", $con);

$result = mysql_query("SELECT * FROM Users Order by fname ASC");

$output = array();

while($row = mysql_fetch_array($result)) {

    // create a temp array to hold the data
    $temp = array();

     

    // add the data
    $temp[] = '"' . $row['created_date'] . '"';
    $temp[] = '"' . $row['fname'] . '"';

    $temp[] = '"' . $row['lname'] . '"';

    $temp[] = '"' . $row['country'] . '"';

    $temp[] = '"' . $row['gender'] . '"';

    $temp[] = '"' . $row['year'] . '"';

    $temp[] = '"' . $row['month'] . '"';

    $temp[] = '"' . $row['day'] . '"';

    $temp[] = '"' . $row['email_encrypted'] . '"';

    $temp[] = '"' . $row['password'] . '"';

    // implode the temp array into a comma-separated list and add to the output array

    $output[] = '[' . implode(', ', $temp) . ']';
}

// implode the output into a comma-newline separated list and echo
echo implode(",\n", $output);

mysql_close($con);
?>

This avoids the problem of errant commas screwing up your code in IE.  Use it or don't.

On Saturday, June 16, 2012 10:34:24 AM UTC-4, anden1234 wrote:

I created users.php file and added the following code.   DON'T forget to add your own sql username/password and create a Users table in the database!!:

<html>

<head>

<script type='text/javascript' src='https://www.google.com/jsapi'></script>

<script type='text/javascript'>

google.load('visualization', '1', {packages:['table']});

google.setOnLoadCallback(drawTable);

　

function drawTable() {

var data = new google.visualization.DataTable();

data.addColumn('string', 'Created Date');

data.addColumn('string', 'First Name');

data.addColumn('string', 'Last Name');

data.addColumn('string', 'Country');

data.addColumn('string', 'Gender');

data.addColumn('string', 'Birth Year-');

data.addColumn('string', 'Month-');

data.addColumn('string', 'Day');

data.addColumn('string', 'Email Encrypted');

data.addColumn('string', 'Password Encrypted');

data.addRows([

　

<?php

$con = mysql_connect("blabla","blabla","passwordblabla");

if (!$con)

{

die('Could not connect: ' . mysql_error());

}

mysql_select_db("robotfriend_org", $con);

$result = mysql_query("SELECT *

FROM Users

Order by fname ASC");

while($row = mysql_fetch_array($result))

{

echo "['" . $row['created_date'] . "',

'" . $row['fname'] . "',

'" . $row['lname'] . "',

'" . $row['country'] . "',

'" . $row['gender'] . "',

'" . $row['year'] . "',

'" . $row['month'] . "',

'" . $row['day'] . "',

'" . $row['email_encrypted'] . "',

'" . $row['password'] . "'],";

}

mysql_close($con);

　

echo "['', '', '', '', '', '', '', '', '', '']"; // MUST add NO "," in the end (after password encrypted and]!!

?>

　

]);

var table = new google.visualization.Table(document.getElementById('table_div'));

table.draw(data, {showRowNumber: true});

}

</script>

</head>

<body>

<div id='table_div'></div>

</body>

</html>

 

[Lukas]

I'm very new to this. I have an SQL database with some very simple stats:

CREATE TABLE `self_check_stats`
    (`id` int( 11 ) NOT NULL AUTO_INCREMENT ,0
    `location` varchar( 50 ) DEFAULT NULL ,
    `count` int( 11 ) NOT NULL DEFAULT '0',
    `sessions` int( 11 ) NOT NULL DEFAULT '0',
    `timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ,
    PRIMARY KEY ( `id` ));
   


I want to make a line chart using these stats, but the code I have copied from here is not working for me.

I would like the total of "count" for each day from the timestamp with a max of 30 days. The day should be along the X-axis, the count along the Y-axis. There should be 1 line for each location.

here is the code I have so far, any help would be greatly appreciated:

<html>
  <head>
    <script type="text/javascript" src="https://www.google.com/jsapi"></script>
    <script type="text/javascript">

      google.load("visualization", "1", {packages:["corechart"]});
      google.setOnLoadCallback(drawChart);
      function drawChart() {
        var data = google.visualization.arrayToDataTable([
       
<?php

include_once('config.php'); //for the database logins and passwords


$con = mysql_connect($dbhostname, $dbusername, $dbpassword);

if (!$con) {
    die('Could not connect: ' . mysql_error());
}

mysql_select_db($database, $con);

$result = mysql_query("select * from %s where location='%s' order by timestamp desc");

$output = array();

while($row = mysql_fetch_array($result)) {
    // create a temp array to hold the data
    $temp = array();
    
    // add the data

    $temp[] = '"' . $row['count'] . '"';
    $temp[] = '"' . $row['timestamp'] . '"';

    // implode the temp array into a comma-separated list and add to the output array
    $output[] = '[' . implode(', ', $temp) . ']';
}

// implode the output into a comma-newline separated list and echo
echo implode(",\n", $output);

mysql_close($con);
?>       
   

        ]);

        var options = {
          title: 'Selfcheck Stats'
        };

        var chart = new google.visualization.LineChart(document.getElementById('chart_div'));
        chart.draw(data, options);
      }
    </script>
  </head>
  <body>
    <div id="chart_div" style="width: 900px; height: 500px;"></div>
  </body>
</html>



Thanks!

[asgallant]

This is a bit tricky to do, since the chart's require one column of data for each line, so we have to split out the data so that each location gets its own column.  There are several ways to handle this; I attached a PHP file that uses one way that does the hard part in javascript and is capable of handling an arbitrary number of locations (you don't have to know in advance how many you have or want to use).

I can't test this without a local copy of your database, so you'll have to give it a try and report back if there are any problems.

[Lukas]

Thanks!

I currently only have 1 location, (there will be more in the future).

I am getting the following error: Not enough columns given to draw the requested chart.

[asgallant]

Open the page in a browser, view the source, and paste it here so I can see it.

[Lukas]

here is the source, thanks.

<html>

	<head>
		<script type="text/javascript" src="https://www.google.com/jsapi"></script>
		<script type="text/javascript">

			google.load("visualization", "1", {packages:["corechart"]});
			google.setOnLoadCallback(drawChart);
			function drawChart() {
				var data = google.visualization.arrayToDataTable([

					['Location', 'Timestamp', 'Count'],
				]);

				// parse the data table for a list of locations
				var locations = google.visualization.data.group(data, [0], []);
				// build an array of data column definitions
				var columns = [1];
				for (var i = 0; i < locations.getNumberOfRows(); i++) {
					var loc = locations.getValue(i, 0);
					columns.push({
						label: loc,
						type: 'number',
						calc: function (dt, row) {
							// include data in this column only if the location matches
							return (dt.getValue(row, 0) == loc) ? dt.getValue(row, 2) : null;
						}
					});
				}
				
				// create a DataView based on the DataTable to get the correct snapshot of the data for the chart
				var view = new google.visualization.DataView(data);
				// set the columns in the view to the columns we constructed above
				view.setColumns(columns);

				
				var options = {
					title: 'Selfcheck Stats'
				};

				var chart = new google.visualization.LineChart(document.getElementById('chart_div'));

				// draw the chart using the DataView instead of the DataTable
				chart.draw(view, options);
			}
		

</script>
	</head>
	<body>
		<div id="chart_div" style="width: 900px; height: 500px;"></div>
	</body>
</html>

[asgallant]

Your query isn't returning any data, which isn't surprising when I look at the query string:

$result = mysql_query("select * from %s where location='%s' order by timestamp desc");

You need to pass that string through the sprintf function to replace the %s's before creating a query with it (or use some alternative method to build the string).

[Atik Bayraktar]

Guys, Ive been working on that too.. I got my datas from database and send them to chart but I see a blank page when I try it in WAMP... no errors but there is not something to see.

code:

<html>
  <head>
    <script type="text/javascript" src="https://www.google.com/jsapi"></script>
    <script type="text/javascript">

      google.load("visualization", "1", {packages:["corechart"]});
      google.setOnLoadCallback(drawChart);
      function drawChart() {
        var data = google.visualization.arrayToDataTable([
       
<?php

$con = mysql_connect("localhost:3306", "reverse", "");

if (!$con) {
    die('Could not connect: ' . mysql_error());
}

mysql_select_db($database, $con);

$result = mysql_query("SELECT * FROM `test` LIMIT 0 , 30");

$output = array();

while($row = mysql_fetch_array($result)) {
    // create a temp array to hold the data
    $temp = array();
    
    // add the data

    $temp[] = '"' . $row['Temp'] . '"';
    $temp[] = '"' . $row['Humidity'] . '"';
    $temp[] = '"' . $row['Light'] . '"';

    // implode the temp array into a comma-separated list and add to the output array
    $output[] = '[' . implode(', ', $temp) . ']';
}

// implode the output into a comma-newline separated list and echo
echo implode(",\n", $output);

mysql_close($con);
?>       
   

        ]);

        var options = {
          title: 'Selfcheck Stats'
        };

        var chart = new google.visualization.LineChart(document.getElementById('chart_div'));
        chart.draw(data, options);
      }
    </script>
  </head>
  <body>
    <div id="chart_div" style="width: 900px; height: 500px;"></div>
  </body>
</html>

---

Please help ;)

[asgallant]

Nothing sticks out at me immediately.  Open the page in a browser, view the source, and post it here.  I want to see what your PHP is rendering as javascript.

[John Garcia]

What about SQL? can you please help me to get data from SQL and create Chart please?

[Pascal Segoete]

https://blog.programster.org/php-converting-data-for-google-charts