1

I'm trying to generate a google chart from mysql data using php. Here is my code so far : get_json.php 

<?php
/* $server = the IP address or network name of the server
 * $userName = the user to log into the database with
 * $password = the database account password
 * $databaseName = the name of the database to pull data from
 * table structure - colum1 is cas: has text/description - column2 is data has the value
 */
$con = mysql_connect('localhost','root','') ;

mysql_select_db('gcm', $con); 

// write your SQL query here (you may use parameters from $_GET or $_POST if you need them)
$query = mysql_query('SELECT count(name) As Subscribers,CAST(`created_at` AS DATE) As Date FROM gcm_users GROUP BY created_at ORDER BY created_at')or die(mysql_error());

$table = array();
$table['cols'] = array(
    /* define your DataTable columns here
     * each column gets its own array
     * syntax of the arrays is:
     * label => column label
     * type => data type of column (string, number, date, datetime, boolean)
     */
    // I assumed your first column is a "string" type
    // and your second column is a "number" type
    // but you can change them if they are not
    array('label' => 'Date', 'type' => 'string'),
    array('label' => 'Subscribers', 'type' => 'number')
);

$rows = array();
while($r = mysql_fetch_assoc($query)) {
    $temp = array();
    // each column needs to have data inserted via the $temp array
    $temp[] = array('v' => $r['Date']);
    $temp[] = array('v' => (int) $r['Subscribers']); 

    // insert the temp array into $rows
    $rows[] = array('c' => $temp);
}

// populate the table with rows of data
$table['rows'] = $rows;

// encode the table as JSON
$jsonTable = json_encode($table);

// set up header; first two prevent IE from caching queries
header('Cache-Control: no-cache, must-revalidate');
header('Content-type: application/json');

// return the JSON data
echo $jsonTable;
?>

It displays right : {"cols":[{"label":"Date","type":"string"},{"label":"Subscribers","type":"number"}],"rows":[{"c":[{"v":"2013-04-14"},{"v":1}]},{"c":[{"v":"2013-04-15"},{"v":1}]}]} This is where I display :

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
  <head>
    <meta http-equiv="content-type" content="text/html; charset=utf-8"/>
    <title>
      Google Visualization API Sample
    </title>
    <script type="text/javascript" src="http://www.google.com/jsapi"></script>
    <script type="text/javascript">
      google.load('visualization', '1', {packages: ['corechart']});
    </script>
    <script type="text/javascript">
      function drawVisualization() {
        var jsonData = null;

        var json = $.ajax({
          url: "get_json.php", // make this url point to the data file
          dataType: "json",
          async: false,
          success: (
        function(data) {
            jsonData = data;
        })
        }).responseText;



        // Create and populate the data table.
        var data = new google.visualization.DataTable(jsonData);


        // Create and draw the visualization.
      var chart= new google.visualization.LineChart(document.getElementById('visualization')).
            draw(data, {curveType: "function",
                        width: 500, height: 400,
                        vAxis: {maxValue: 10}}
                );
      }


      google.setOnLoadCallback(drawVisualization);
    </script>
  </head>
  <body style="font-family: Arial;border: 0 none;">
    <div id="visualization" style="width: 500px; height: 400px;"></div>
  </body>
</html>

But when i run this code I got nothing,it's blank ! Where is my error?

user219241
  • 31
  • 1
  • 4
  • 4
    [**Please, don't use `mysql_*` functions in new code**](http://bit.ly/phpmsql). They are no longer maintained [and are officially deprecated](https://wiki.php.net/rfc/mysql_deprecation). See the [**red box**](http://j.mp/Te9zIL)? Learn about [*prepared statements*](http://j.mp/T9hLWi) instead, and use [PDO](http://php.net/pdo) or [MySQLi](http://php.net/mysqli) - [this article](http://j.mp/QEx8IB) will help you decide which. If you choose PDO, [here is a good tutorial](http://j.mp/PoWehJ). – h2ooooooo Apr 15 '13 at 13:10
  • Have you checked the output in the console of the developer tools of your browser for error messages? – Gerald Schneider Apr 15 '13 at 13:13
  • nope,how to check that? – user219241 Apr 15 '13 at 13:15
  • @user219241 do you have firebug? check there, what is the output of ajax request. – Code Lღver Apr 15 '13 at 13:19

1 Answers1

4

Now tested:

You haven't included jQuery in your page. Add this line in your <head> and it works.

<script src="//ajax.googleapis.com/ajax/libs/jquery/1.9.1/jquery.min.js"></script>

Another thing that can happen if your AJAX call takes a little longer: By the time your success: function() is called after the AJAX call is finished the rest of the script has already run. The script doesn't wait until the AJAX call is finished until the "Create and populate" part is executed. You have to place these lines inside the success: function().

<script type="text/javascript">
  function drawVisualization() {
    var jsonData = null;

    var json = $.ajax({
      url: "get_json.php", // make this url point to the data file
      dataType: "json",
      async: false,
      success: (
    function(data) {
        jsonData = data;

    // Create and populate the data table.
    var data = new google.visualization.DataTable(jsonData);

    // Create and draw the visualization.
  var chart= new google.visualization.LineChart(document.getElementById('visualization')).
        draw(data, {curveType: "function",
                    width: 500, height: 400,
                    vAxis: {maxValue: 10}}
            );

    })
    }).responseText;



  }


  google.setOnLoadCallback(drawVisualization);
</script>

This way you can make sure that all relevant code is only executed after the AJAX call has finished.

Gerald Schneider
  • 17,416
  • 9
  • 60
  • 78