Re: line graph using google chart api and php

asgallant

unread,

Sep 7, 2012, 1:52:47 PM9/7/12

to google-visua...@googlegroups.com

There is one problem I can see with your script that would cause it to bomb in IE: you leave an errant comma at the end of the array passed to the arrayToDataTable method. To fix it, switch from building your $display variable as a string to building it as an array of arrays, and implode them into a string. You can also save yourself some hassle by only echo-ing the dynamic content in PHP - just enter the rest as you would any other javascript:

google.load("visualization", "1", {packages:["corechart"]});

google.setOnLoadCallback(drawChart);

function drawChart() {
  var data = google.visualization.arrayToDataTable([
  ['Date', 'Score'],
  <?php
  $output = array ();
  while($result = mysql_fetch_array($sql_data)) {
  $t1 = strtotime($result['date']);
  $t2 = date("d",$t1);
  $output[] = "[".$t2.",".$result['sales']."]"; // note there is no comma at the end here
  }
  echo implode(',', $output);
  ?>
  ]);
  var options = {
  title: 'Sales  Analysis',
  vAxis: {title: "sales"},
  hAxis: {title: "date"} // also got rid of an errant comma here
  };

  var chart = new google.visualization.LineChart(document.getElementById('chart_div'));
  chart.draw(data, options);
}

</script>

Incidentally, your date format is just returning the day of the month, so you could end up with duplicate axis values. If you want to use proper dates, then you'll have to change the date format (and switch to another method of building the DataTable as the #arrayToDataTable method does not support Dates).

On Friday, September 7, 2012 6:24:16 AM UTC-4, gnik wrote:

i have a mysql database . i want to plot a dynamic graph between two of my table colums named as date and sales .where date is stored in this format("yyyy-mm-dd") and sales are integers (not float )
please help me with this. though i am successful in plotting data but the graph is not properly drawn . i guess there's an issue with intervals.

i want
x axis interval as : aug 30 , aug 31 , sep 01 , sep 02 and so on.....
y axis interval as : 0 , 10 , 20 ,30 ....

my code goes like this :

<?php
include('dbconnect.php');
$sql_data = mysql_query("SELECT date , sales FROM analytics WHERE
id ='12345' ORDER BY date ASC");

?>

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

<?php
echo "<script type=text/javascript>";
echo "google.load(\"visualization\", \"1\", {packages:[\"corechart\"]});";
echo "google.setOnLoadCallback(drawChart);";

echo "function drawChart() { ";
echo "var data = google.visualization.arrayToDataTable([
['Date', 'Score'],";
while($result = mysql_fetch_array($sql_data))
{
$t1 = strtotime($result['date']);
$t2 = date("d",$t1);
$display .= "[".$t2.",".$result['sales']."],";
}

$display .= " ]);";

echo $display;

echo "var options = {
title: 'Sales Analysis' ,
vAxis: {title: \"sales\"},
hAxis: {title: \"date\"} ,

};";

echo "var chart = new google.visualization.LineChart(document.getElementById('chart_div'));";
echo "chart.draw(data, options);";
echo "}
</script>";

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

gnik

unread,

Sep 8, 2012, 1:43:07 AM9/8/12

to google-visua...@googlegroups.com

Thanks a lot for your reply mate .... it would be really great if you could provide me an exact solution to this.. i've tried a lot but unfortunately nothing worked for me ...i need to print dates ..... these are ever increasing dates .... like begin from say 30 aug to every upcoming days for years ......

asgallant

unread,

Sep 8, 2012, 9:56:29 AM9/8/12

to google-visua...@googlegroups.com

Here's the simplest solution:

function drawChart() {
  // use explicit DataTable construction instead of arrayToDataTable
  var data = new google.visualization.DataTable();
  data.addColumn('date', 'Date');
  data.addColumn('number', 'Score');
  data.addRows([

<?php
$output = array ();

while($result = mysql_fetch_array($sql_data)) {
$t1 = strtotime($result['date']);

  // convert date into javascript date object: new Date(year, month, day)
  $year = date("Y",$t1);
  $month = date("m",$t1) - 1; // need to zero-index the month
  $day = date("d",$t1);
  $output[] = "[new Date($year, $month, $day), {$result['sales']}]";

  }
  echo implode(',', $output);
  ?>
  ]);

  var options = {
  title: 'Sales  Analysis',
  vAxis: {title: "sales"},

hAxis: {title: "date"} // also got rid of an errant comma here
};

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

chart.draw(data, options);

gnik

unread,

Sep 11, 2012, 2:19:39 AM9/11/12

to google-visua...@googlegroups.com

Thanks a ton mate.... it worked like magic...
the only thing .... how can i set more options to it...like changing colors , etc...
and why is the date interval coming of two days instead of one day ......
how to make it daily basis x axis and what will happen to the graph size if i continue for say 1000+ days... ?

asgallant

unread,

Sep 11, 2012, 11:34:27 AM9/11/12

to google-visua...@googlegroups.com

There are two ways to set line colors: set the "colors" option to an array of color strings (the first line uses the first color, the second uses the second, and so on), or set the series.<series index>.color option, which allows you to explicitly define the color of any series. Use like this:

var options = {
  title: 'Sales  Analysis',
  vAxis: {
  title: 'sales'
  },
  hAxis: {
  title: 'date'
  },

  // set the colors option:
  colors: ['#AA00CC'],
  // - or - set the series option
  // the series.<series index>.color option will override the colors option (for the particular series) if both are set
  series: {
  0: {
  color: '#AA00CC'
  }
  }
};

All of the configuration options for LineCharts are listed here. Make sure you use the same format to enter them - entering an option like this won't work:

var options = {
hAxis.title: 'sales'
};

When using dates for the axis, you have a continuous axis, so the axis labels do not necessarily correspond to data points. Labels correspond to the vertical gridlines, so to increase the number of labels, you need to set the hAxis.gridlines.count option. The API may override the settings you choose, though, if it determines that it can't draw things neatly.

If your graph grows to include 1000+ points, I would suggest using the ChartRangeFilter to allow users to pan and zoom the chart, which will make it much easier to read.

Reply all

Reply to author

Forward