trouble drawing line chart using datetime column from mySQL table

3,323 views
Skip to first unread message

Steve

unread,
Feb 19, 2014, 2:57:38 PM2/19/14
to google-visua...@googlegroups.com
I have a mySQL table that I've been populating with 3 columns every 5 minutes:  DateTime, Temperature, Humidity.  I can view the data in the table and I can see that it's being recorded properly.  But now I'd like to graph it with an annotated line chart, mainly so I can use the zoom buttons to select different time periods for the x axis.  I used the example html code from the annotated line chart example on the visualization playground ,  which works fine on my server with the sample data, but when I add a php script to get my data from the mySQL table and add it to the data table for the Google chart, I get the following error : 

Uncaught Error: Type mismatch. Value 2014-02-07 22:37:03 does not match type datetime in column index 0

Here is the section of my php code when viewed from the browser page source that shows the data it grabbed from the mySQL table:

data.addColumn('datetime', 'Date');
data.addColumn('number', 'Temp');
data.addColumn('number', 'Humid');
data.addRows([
['2014-02-07 22:37:03',6.0,19.6],['2014-02-07 22:42:03',5.5,21.4],['2014-02-07 22:47:03',5.6,20.2],.........


I'm guessing I just don't have the date and time part formatted correctly and that's why it's not working, but I haven't found any examples of people who successfully graphed data from a mySQL table when that column is specified as a "datetime" and not just a "string".

Here's the entire code I wrote that grabs the data and attempts to graph it with the annotated line chart.

<script type='text/javascript' src='http://www.google.com/jsapi'></script>
    <script type='text/javascript'>
      google.load('visualization', '1.1', {'packages':['annotationchart']});
      google.setOnLoadCallback(drawChart);
      function drawChart() {
      var data = new google.visualization.DataTable();
      data.addColumn('datetime', 'Date');
      data.addColumn('number', 'Temperature');
      data.addColumn('number', 'Humidity');

data.addRows([
<?php

$db="my_dbname";
$link = mysql_connect('localhost', 'db_username', 'password');

mysql_query('SET NAMES utf8');
mysql_select_db($db , $link) or die("Couldn't open $db: ".mysql_error());

$result = mysql_query("SELECT Date, Temp, Humid FROM datatable Order By Date");
if ($result !== false) {
$num=mysql_numrows($result);
$i=0;
echo"";

while ($i < $num) {

$Date=mysql_result($result,$i,"Date");
$Temp=mysql_result($result,$i,"Temp");
//$CTDtemp=mysql_result($result,$i,"CTDtemp");
$Humid=mysql_result($result,$i,"Humid");

echo "['";
echo "$Date";
echo "',";
echo "$Temp";
echo ",";
echo "$Humid";
echo "]";
if ($i < ($num - 1))
{
echo ",";
}
$i++;

}
}

?>
]);

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

        var options = {
          displayAnnotations: false,
        };

        chart.draw(data, options);
      }
    </script>
  </head>

  <body>
 
  Graph should be here.
 
    <div id='chart_div' style='width: 900px; height: 500px;'></div>
   
  </body>
</html>

asgallant

unread,
Feb 19, 2014, 5:46:59 PM2/19/14
to google-visua...@googlegroups.com
You need to input the dates as Date objects, not as strings.  The simplest fix given your code structure is to replace your while loop with this:

$output = Array();
while ($i < $num) {
    $DateTimeArray=explode(' ', mysql_result($result,$i,"Date"));

    $Temp=mysql_result($result,$i,"Temp");
    //$CTDtemp=mysql_result($result,$i,"CTDtemp");
    $Humid=mysql_result($result,$i,"Humid");
    
    $dateArray = explode('-', $DateTimeArray[0]);
    $year = $dateArray[0];
    $month = $dateArray[1] - 1; // adjust for javascript's 0-indexed months
    $day = $dateArray[2];
    
    $timeArray = explode(':', $DateTimeArray[0]);
    $hours = $timeArray[0];
    $minutes = $timeArray[1];
    $seconds = $timeArray[2];
    
    $output[] = "[new Date($year, $month, $day, $hours, $minutes, $seconds), $Temp, $Humid]";
    $i++;
}
echo implode(',', $output);

Steve

unread,
Feb 19, 2014, 8:03:51 PM2/19/14
to google-visua...@googlegroups.com

Wow, thanks so much, that worked perfectly!  I didn't realize the date and time needed to be exploded in order for the google chart to be able to handle them. 

One correction to your code though in case someone else is looking at this for help.

$timeArray = explode(':', $DateTimeArray[0]);

should be changed to

$timeArray = explode(':', $DateTimeArray[1]);

Thanks for the help!

Steel Vargas

unread,
May 7, 2014, 5:40:59 PM5/7/14
to google-visua...@googlegroups.com
hello can you help me out i can not understand what im doing wrong i have only datetime and temp can you help me out plz

Andrew Gallant

unread,
May 7, 2014, 7:48:31 PM5/7/14
to google-visua...@googlegroups.com
Can you be more specific?  What does your code look like?  What part doesn't work?

Steel Vargas

unread,
May 10, 2014, 8:00:15 PM5/10/14
to google-visua...@googlegroups.com
im trying find a example code i can use i have been trying for multi days and had no luck my table looks like datetime,temp_c,temp_f,temp_k can you help me

Andrew Gallant

unread,
May 11, 2014, 12:07:59 AM5/11/14
to google-visua...@googlegroups.com
I would like to help you, but I need something to work from.  I made an example (http://jsfiddle.net/asgallant/79qZ8/) that might help, but without specifics from you, I cannot assist more.

Steel Vargas

unread,
May 11, 2014, 1:48:21 PM5/11/14
to google-visua...@googlegroups.com

here is my code
===============================================================================
<html>

<head>

<title>AC Temperature</title>

<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([

['Time', 'Temperature'],

<?php

$con = mysqli_connect("host", "user", "password", "database");

 

$query = "SELECT datatime,temp_c FROM temp";

$result = mysqli_query($con, $query);

 

mysqli_close($con);

 

while ($row = mysqli_fetch_array($result))

{

$time = $row['datatime'];

$temp = $row['temp_c'];

echo "['$time', $temp],";

}

?>

]);

 

var options = {

title: 'AC Temperature',

vAxis: { title: "Degrees Celsius" }

};

 

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>
================================================================================================================
it is one file named index.php what i need is a way to chose the day or something like that and a way to change to temp_c,temp_f,temp_k in a dropdown menu do you think you can help me out

On Wednesday, February 19, 2014 1:57:38 PM UTC-6, Steve wrote:

Andrew Gallant

unread,
May 11, 2014, 6:56:06 PM5/11/14
to google-visua...@googlegroups.com
Are you looking to query the whole data set and filter it in the browser, or do you want to send the parameters to the server to fetch smaller data sets?

Steel Vargas

unread,
May 12, 2014, 12:33:36 AM5/12/14
to google-visua...@googlegroups.com
I query in to get smaller sections right now but I want to beable to pick the date to show from a datepicker can u help me

Andrew Gallant

unread,
May 12, 2014, 1:02:18 AM5/12/14
to google-visua...@googlegroups.com
There are no date pickers in the Visualization API.  You will need to find a 3rd-party tool that has one that you like.

Steel Vargas

unread,
May 12, 2014, 1:22:38 PM5/12/14
to google-visua...@googlegroups.com
so you don think i could use a java daepicker and call the results in my mysql query

Andrew Gallant

unread,
May 12, 2014, 4:18:34 PM5/12/14
to google-visua...@googlegroups.com
You can use whatever tools you want; I just said that the Visualization API doesn't provide any for you.

Steel Vargas

unread,
May 12, 2014, 9:20:39 PM5/12/14
to google-visua...@googlegroups.com

do you think you could help me with in example code with that way

Andrew Gallant

unread,
May 12, 2014, 11:21:15 PM5/12/14
to google-visua...@googlegroups.com
You have to pick the tool you want to use, I can't make that choice for you.

Romain Bernard

unread,
Jul 23, 2014, 11:42:25 AM7/23/14
to google-visua...@googlegroups.com
Hi Andrew,

I tried all the above example to make an annotation chart working, but I miserably failed to have it working...

I always get this error, and even if mysql_num_rows() is deprecated I cannot find a way to have it replaced

Warning: mysql_num_rows() expects parameter 1 to be resource, object given in /Applications/XAMPP/xamppfiles/htdocs/server1/Result_console4.php on line 11
Are you able to provide me some help?

Hope you can help me..
many thanks.

Here is my entire code of my php page.

<?php

//DB connection parameters

require_once("mysql_connect.php");


$result = mysqli_query($con,"select date,value1,value2 from DATABASE");


if ($result !== false) {

$num=mysql_num_rows($result);

$i=0;

echo"";


$output = Array();

while ($i < $num) {

    $DateTimeArray=explode(' ', mysql_result($result,$i,"date"));

    $MYvalue1=mysql_result($result,$i,"value1");

    $MYvalue2=mysql_result($result,$i,"value2");

    

    $dateArray = explode('-', $DateTimeArray[0]);

    $year = $dateArray[0];

    $month = $dateArray[1] - 1; // adjust for javascript's 0-indexed months

    $day = $dateArray[2];

    

    $timeArray = explode(':', $DateTimeArray[1]);

    $hours = $timeArray[0];

    $minutes = $timeArray[1];

    $seconds = $timeArray[2];

    

    $output[] = "[new Date($year, $month, $day, $hours, $minutes, $seconds), $MYvalue1, $MYvalue2]";

    $i++;

}

}


?>



<html>

  <head>

  </head>

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

    <script type='text/javascript'>

      google.load('visualization', '1.1', {'packages':['annotationchart']});

      google.setOnLoadCallback(drawChart);

      function drawChart() {

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

        data.addColumn('datetime', 'Date');

        data.addColumn('number', 'value1');

        data.addColumn('number', 'value2');

        data.addRows([ <?php echo implode(',', $output); ?>]);


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


        var options = {

          displayAnnotations: true,

        };


        chart.draw(data, options);

      }

    </script>

Andrew Gallant

unread,
Jul 23, 2014, 7:50:47 PM7/23/14
to google-visua...@googlegroups.com
You are using mysqli instead of the base mysql library (which is a good thing!), so you need to use the mysqli* functions.  Try this:

<?php
//DB connection parameters
require_once("mysql_connect.php");

$result = mysqli_query($con,"select date,value1,value2 from DATABASE");

if ($result !== false) {
    $output = Array();
    while ($row = mysqli_fetch_assoc($result)) {
        $DateTimeArray = $row["date"];
        $MYvalue1 = $row["value1"];
        $MYvalue2 = $row["value2"];
        
        $dateArray = explode('-', $DateTimeArray[0]);
        $year = $dateArray[0];
        $month = $dateArray[1] - 1; // adjust for javascript's 0-indexed months
        $day = $dateArray[2];
        
        $timeArray = explode(':', $DateTimeArray[1]);
        $hours = $timeArray[0];
        $minutes = $timeArray[1];
        $seconds = $timeArray[2];
        
        $output[] = "[new Date($year, $month, $day, $hours, $minutes, $seconds), $MYvalue1, $MYvalue2]";
    }
}
?>

Romain Bernard

unread,
Jul 24, 2014, 5:28:28 PM7/24/14
to google-visua...@googlegroups.com

It works, thanks for this quick turn around. Also, I don't know why but my date format "2014-07-23 20:00:00" was not properly changed, so I had to do some minor change with the first script I used.

Thanks a million again,

Here is what he looks like:


<?php

//DB connection parameters

require_once("mysql_connect.php");

$result = mysqli_query($con,"select date,value1,value2 from DATABASE");


if ($result !== false) {

    $output = Array();

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

        $DateTimeArray = $row["date"];

        $MYvalue1 = $row["value1"];

        $MYvalue2 = $row["value2"];

    

        $date = date('Y-m-d', strtotime($DateTimeArray));

        $time = date('H:i:s', strtotime($DateTimeArray));


        $dateArray = explode('-', $date);

        $year = $dateArray[0];

        $month = $dateArray[1] - 1; // adjust for javascript's 0-indexed months

        $day = $dateArray[2];

        

        $timeArray = explode(':', $time);

        $hours = $timeArray[0];

        $minutes = $timeArray[1];

        $seconds = $timeArray[2];

   

        $output[] = "[new Date($year,$month,$day,$hours,$minutes,$seconds), $MYvalue1, $MYvalue2]";

    }

}

?>

Cheers,
Romain
Reply all
Reply to author
Forward
0 new messages