CODEWITHSUNDEEP
phphtmlmysqlgoogle-visualization
Manos
Manos

Reputation: 1501

Google Charts and MYSQL

i am new with PHP and Google charts. What i am trying to do it to make google charts with arduino data storred in a MYSL database. So far i insert data from arduino to mysql DB successfully but i face difficulties with the google charts.

Here is my PHP code:

<?php


  $mysqli =mysqli_connect('localhost', 'root', '', 'Arduino');
if (mysqli_connect_errno()) {
    echo "Failed to connect to MySQL: ".mysqli_connect_error();
}


  $sql = mysqli_query($mysqli, 'SELECT * FROM analoog0');

  if (!$sql) {
  die("Error running $sql: " . mysql_error());
  }



  $results = array();
  while($row = mysqli_fetch_assoc($sql))
{
   $results[] = array(
      'Date' => $row['Date'],
      'Time' => $row['Time'],
      'Temperature' => $row['Temperature']
   );
}
$json = json_encode($results, JSON_PRETTY_PRINT);
echo $json;

?>

Here is the JSON output:

[ { "Date": "2013-10-24", "Time": "18:15:49", "Temperature": "24" }, 
{ "Date": "2013-10-24", "Time": "18:16:19", "Temperature": "24" }, 
{ "Date": "2013-10-24", "Time": "18:16:49", "Temperature": "24" }, 
{ "Date": "2013-10-24", "Time": "18:17:19", "Temperature": "23" } ]

And finally the HTM code:

<html>
  <head>
    <!--Load the AJAX API-->
    <script type="text/javascript" src="https://www.google.com/jsapi"></script>
    <script type="text/javascript" src="//ajax.googleapis.com/ajax/libs/jquery/1.10.2/jquery.min.js"></script>
    <script type="text/javascript">

    // Load the Visualization API and the piechart package.
    google.load('visualization', '1', {'packages':['corechart']});

    // Set a callback to run when the Google Visualization API is loaded.
    google.setOnLoadCallback(drawChart);

    function drawChart() {
      var jsonData = $.ajax({
          url: "localhost/Charts/chart_ver2.php",
          dataType:"json",
          async: false
          }).responseText;

      // Create our data table out of JSON data loaded from server.
      var data = new google.visualization.DataTable(jsonData);

      // Instantiate and draw our chart, passing in some options.
      var chart = new google.visualization.LineChart(document.getElementById('chart_div'));
      chart.draw(data, {width: 400, height: 240});
    }

    </script>
  </head>

  <body>
    <div id="chart_div"></div>
  </body>
</html>

When i run the HTM code nothing happens - the screen remains blank.

Any help, guidance or redirection will be highly appreciated! Thanks in advance!!

Upvotes: 1

Views: 9064

Answers (2)

asgallant
asgallant

Reputation: 26340

You need to format the data correctly for the Visualization API. This should put your data in the correct format:

$results = array(
    'cols' => array (
        array('label' => 'Date', 'type' => 'datetime'),
        array('label' => 'Temperature', 'type' => 'number')
    },
    'rows' => array()
);
while($row = mysqli_fetch_assoc($sql)) {
    // date assumes "yyyy-MM-dd" format
    $dateArr = explode('-', $row['Date']);
    $year = (int) $dateArr[0];
    $month = (int) $dateArr[1] - 1; // subtract 1 to make month compatible with javascript months
    $day = (int) $dateArr[2];

    // time assumes "hh:mm:ss" format
    $timeArr = explode(':', $row['Time']);
    $hour = (int) $timeArr[0];
    $minute = (int) $timeArr[1];
    $second = (int) $timeArr[2];

    $results['rows'][] = array('c' => array(
        array('v' => "Date($year, $month, $day, $hour, $minute, $second)"),
        array('v' => $row['Temperature'])
    ));
}
$json = json_encode($results, JSON_NUMERIC_CHECK);
echo $json;

In your javascript, I would recommend rearranging the way you handle the AJAX query. There's nothing wrong per say with the way you are doing it, but I think this is a more elegant solution:

function drawChart() {
    $.ajax({
        url: 'localhost/Charts/chart_ver2.php',
        dataType: 'json',
        success: function (jsonData) {
            // Create our data table out of JSON data loaded from server.
            var data = new google.visualization.DataTable(jsonData);

            // Instantiate and draw our chart, passing in some options.
            var chart = new google.visualization.LineChart(document.getElementById('chart_div'));
            chart.draw(data, {width: 400, height: 240});
        }
    });
}

Upvotes: 6

dave
dave

Reputation: 64705

You can't use the .responseText with a JSON response, as far as I know it only works for XML and Text. 

$.ajax({
      url: "localhost/Charts/chart_ver2.php",
      dataType:"json",
      async: false
      }).responseText;

I would change it to:

 $.ajax({
      url: "localhost/Charts/chart_ver2.php",
      dataType:"json",
      async: false ,
      success: function(data) {
           jsonData = data;
       }
      });

Upvotes: 1

Related Questions