Confused by Google Chart from Database

Question

May be I am asking more, but had created a simple Chart from GoogleCharts. The issue is that I have to manually enter the values, which I want to populate from the Database. I know that there is JSON to do that but I have wasted my 4 hours struggling. The Code is :

<!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="//www.google.com/jsapi"></script>
    <script type="text/javascript">
      google.load('visualization', '1', {packages: ['corechart']});
    </script>
    <script type="text/javascript">
      function drawVisualization() {
        // Create and populate the data table.
        var data = google.visualization.arrayToDataTable([
          ['x', 'Cats', 'Blanket 1', 'Blanket 2'],
          ['A',   1,       1,           0.5],
          ['B',   2,       0.5,         1],
          ['C',   4,       1,           0.5],
          ['D',   8,       0.5,         1],
          ['E',   7,       1,           0.5],
          ['F',   7,       0.5,         1],
          ['G',   8,       1,           0.5],
          ['H',   4,       0.5,         1],
          ['I',   2,       1,           0.5],
          ['J',   3.5,     0.5,         1],
          ['K',   3,       1,           0.5],
          ['L',   3.5,     0.5,         1],
          ['M',   1,       1,           0.5],
          ['N',   1,       0.5,         1]
        ]);

        // Create and draw the visualization.
        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>

Now I wan the data to be fetched from my database instead of manually writting it. For this I had created a PHP File and to fetch the data from the MySQL DB.

mysql_connect("localhost","root","");
mysql_select_db("db_graph");

$return_arr = array();
$fetch = mysql_query("SELECT * FROM tbl_graph");

Any sort of help is appreciated!

score 1 · Answer 1 · answered May 02 '14 at 12:48

Please use at least mysqli (or PDO), not mysql as that is deprecated. http://us3.php.net/manual/en/mysqli.query.php

You need to give the API an 'array of arrays' (numerically indexed, although this will not matter until you get to the JS layer). And, you have to provide the column names as the first row:

<?php
$rows = array(
    array('Column 1', 'Column 2', 'Column 3'),
);

// Add the data
// With mysqli_query, the link argument is first and is required
$result = mysqli_query($conn, 'SELECT a, b, c FROM some_table');
while (($row = mysqli_fetch_array($result, MYSQLI_NUM)) {
    // should be array('column a value', 'column b value', 'column c value');
    $rows[] = $row;
}

// You can force the array to be numeric, but this should not be necessary
// because MYSQLI_NUM was used, and [] was used to append which means use
// numeric indices
// This is just more extra defensive coding if preferred
//foreach ($rows as $k => $v) {
//    $rows[$k] = array_values($v);
//}
//$rows = array_values($rows);

// Give the data to the front-end, with correct content type (assuming JSON here)
header('Content-Type: application/json');
print(json_encode($rows));

Now you need to get that data. I suggest using an AjAX library like jQuery's. In your drawVisualization() function:

$.getJSON('/some-endpoint/', function (data) {
    // Since this is the success callback, the data argument is your data from the server
    // Since $.getJSON() is used, there is no need to parse JSON here
    new google.visualization.LineChart(document.getElementById('visualization')).
        draw(data, {curveType: "function",
                        width: 500,
                        height: 400,
                        vAxis: {maxValue: 10}});
}).fail(function () { /* do something */ });

If you fail to make the array numeric in every case, then you will get a JSON stream with an object somewhere (example: {'0': [1]} instead of [[1]]) and the visualisation will fail to generate.

score 1 · Answer 2 · answered May 02 '14 at 12:57

First, don't use the mysql_* functions, as they are unsafe and deprecated. Use PDO or mysqli instead. Here's a PDO example:

<?php
// $databaseName is the database to access
// $username is the user name to log in with
// $password is the user name's password for logging in
$databaseName = 'db_graph';
$username = 'root';
$password = '';
try {
    $db = new PDO("mysql:dbname=$databaseName", $username, $password);
}
catch (PDOException $e) {
    echo $e->getMessage();
}
$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

// use bound parameters in the query to avoid SQL injection
$queryStr = "SELECT foo, bar, baz FROM myTable WHERE cad = :cadValue";
$params['cadValue'] = $_GET['cad'];

$query = $db->prepare($queryStr);
$query->execute($params);
$results = $query->fetchAll(PDO::FETCH_ASSOC);

$table = array(
    'cols' => array(
        // each element in this array is 1 column in the DataTable
        // you must specify a type for each column, the label is optional
        array('label' => 'foo', 'type' => 'string'),
        array('label' => 'bar', 'type' => 'number'),
        array('label' => 'baz', 'type' => 'number')
    ),
    'rows' => array()
);

foreach ($results as $row) {
    $table['rows'][] = array('c' => array(
        array('v' => $row['foo']),
        array('v' => $row['bar']),
        array('v' => $row['baz'])
    ));
}

// gracefully exit the database
$results = null;
$query = null;
$db = null;

$json = json_encode($table, JSON_NUMERIC_CHECK);

// you can output $json directly to javascript, or set this up as a data source and use AJAX to query it

score 0 · Answer 3 · edited May 23 '17 at 12:28

0

The data as it is coming out of the database won't be in JSON format, so you'll need to do some work to JSON encode it then use that in the google API. So do a document write or something so you can see what is coming back from the SQL query, then compare that with the JSON you are currently using in javascript. You basically need to json_encode the array see: JSON encode MySQL results

edited May 23 '17 at 12:28

Community

1
1

answered May 02 '14 at 12:25

LightningShield

680
3
13

Confused by Google Chart from Database

3 Answers3