Creating Google Charts with dynamic data

zetastreak · March 19, 2019

i've to populate google charts with dynamic data.

the data looks llike.

+----+-------+---------+-------+-----------+---------+------+
| id | name  | physics | maths | chemistry | biology | sst  |
+----+-------+---------+-------+-----------+---------+------+
|  1 | Name1 |      10 |    25 |        35 |      42 |   62 |
|  2 | Name2 |      80 |    45 |        45 |      45 |   25 |
|  3 | Name3 |      63 |    25 |        63 |      36 |   36 |
|  4 | Name4 |      82 |    36 |        75 |      48 |   42 |
|  5 | Name5 |      45 |    45 |        78 |      25 |   24 |
|  6 | Name6 |      36 |    36 |        15 |      75 |   36 |
|  7 | Name7 |      99 |    45 |        24 |      24 |   45 |
|  8 | Name8 |      45 |    85 |        85 |      85 |   96 |
+----+-------+---------+-------+-----------+---------+------+

i have to create google charts based on this such that

# namewise - such that when i select a name it displays all subject marks of that particular name in a column chart

#markswise - when i select a subject, it displays all the names with marks in that particular subject.

conisdiering that data may be added and i've to accumulate that also, for namewise i did

// chart.php
<?php

include("connection.php");

$query = "SELECT name FROM csv GROUP BY name DESC";

$statement = $connect->prepare($query);

$statement->execute();

$result = $statement->fetchAll();

?>
<!DOCTYPE html>
<html>
    <head>
        <title>Google charts</title>
        <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css">
        <script src="https://code.jquery.com/jquery-1.12.4.js"></script>
    </head>
    <body>
        <br /><br />
        <div class="container">
          <div class="panel panel-default">
                <div class="panel-heading">
                    <div class="row">
                        <div class="col-md-9">
                            <h3 class="panel-title">Student Wise Marks Data</h3>
                        </div>
                        <div class="col-md-3">
                            <select name="name" class="form-control" id="name">
                                <option value="">Select Student</option>
                            <?php
                            foreach($result as $row)
                            {
                                echo '<option value="'.$row["name"].'">'.$row["name"].'</option>';
                            }
                            ?>
                            </select>
                        </div>
                    </div>
                </div>
                <div class="panel-body">
                    <div id="chart_area" style="width: 1000px; height: 620px;"></div>
                </div>
            </div>
        </div>
    </body>
</html>
<script type="text/javascript" src="https://www.gstatic.com/charts/loader.js"></script>
<script type="text/javascript">
google.charts.load('current', {packages: ['corechart', 'bar']});
google.charts.setOnLoadCallback();

function load_student_data(name, title)
{
    var temp_title = title + ' '+name+'';
    $.ajax({
        url:"fetch.php",
        method:"POST",
        data:{name:name},
        dataType:"JSON",
        success:function(data)
        {
            drawStudentwiseChart(data, temp_title);
        }
    });
}

function drawStudentwiseChart(chart_data, chart_main_title)
{
    var jsonData = chart_data;
    var data = new google.visualization.DataTable();
    data.addColumn('number', 'Physics');
    data.addColumn('number', 'Maths');
    data.addColumn('number', 'Chemistry');
    data.addColumn('number', 'Biology');
    data.addColumn('number', 'SST');
    $.each(jsonData, function(i, jsonData){
        var Physics = jsonData.Physics;
        var Maths = jsonData.Maths;
        var Chemistry = jsonData.Chemistry;
        var Biology = jsonData.Biology;
        var SST = jsonData.SST;

        data.addRows([[Physics,Maths,Chemistry,Biology,SST]]);
    });
    var options = {
        title:chart_main_title,
        hAxis: {
            title: "Subjects"
        },
        vAxis: {
            title: 'Percentage'
        }
    };

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

</script>

<script>

$(document).ready(function(){

    $('#name').change(function(){
        var name = $(this).val();
        if(name != '')
        {
            load_student_data(name, 'Student wise marks data');
        }
    });

});

</script>

and in order to fetch data

// fetch.php

<?php

//fetch.php

include('connection.php');

if(isset($_POST["name"]))
{
 $query = "
 SELECT * FROM csv
 WHERE name = '".$_POST["name"]."'
 ORDER BY id ASC
 ";
 $statement = $connect->prepare($query);
 $statement->execute();
 $result = $statement->fetchAll();
 foreach($result as $row)
 {
  $output[] = array(
   'Physics'   => $row["Physics"],
   'Maths'   => $row["Maths"],
   'Chemistry'   => $row["Chemistry"],
   'Biology'   => $row["Biology"],
   'SST'   => $row["SST"],

  );
 }
 echo json_encode($output);
}

?>

it diplays a blank page.

however when i play with singular values i.e one subject at a time it displays fine

Edited March 19, 2019 by zetastreak

Barand · March 19, 2019

Don't use "SELECT star", specify the columns you need.

Now you can just encode the results as it contains the columns required.

Also you don't seem to have the hang of prepared queries. You don't put post data directly into the query (ever) but use placeholders and pass the values as parameters.

if(isset($_POST["name"]))
{
    $query = "
         SELECT physics
              , maths
              , chemistry
              , biology
              , sst
         FROM zeta
         WHERE name = ?
         ORDER BY id ASC
         ";
    $statement = $connect->prepare($query);
    $statement->execute( [ $_POST['name'] ] );
    $result = $statement->fetchAll();
    echo json_encode($result);
}

zetastreak · March 19, 2019

34 minutes ago, Barand said:
Don't use "SELECT star", specify the columns you need.

Now you can just encode the results as it contains the columns required.

Also you don't seem to have the hang of prepared queries. You don't put post data directly into the query (ever) but use placeholders and pass the values as parameters.
if(isset($_POST["name"]))
{
    $query = "
         SELECT physics
              , maths
              , chemistry
              , biology
              , sst
         FROM zeta
         WHERE name = ?
         ORDER BY id ASC
         ";
    $statement = $connect->prepare($query);
    $statement->execute( [ $_POST['name'] ] );
    $result = $statement->fetchAll();
    echo json_encode($result);
}

@Barand yes mate ._.' it's my first project which involves such use of queries sorry about that. thanks for pointing it out , i made the required changes in the query and the charts still won't load i always get blank page

gizmola · March 20, 2019

Are you looking at the console in the Chrome developer tools for javascript errors? How about the server logs for php errors?

zetastreak · March 20, 2019

1 minute ago, gizmola said:

Are you looking at the console in the Chrome developer tools for javascript errors? How about the server logs for php errors?

@gizmola Yes i've checked everything, i'm not getting any errors

gizmola · March 20, 2019

So you need to start verifying what has actually worked. For example, var_dump($result) in the php script and see what is being returned. Tools like Postman are good for testing this type of thing if you can't figure out how to do it manually. You need to follow each step and assumption until you determine what isn't working the way you expect.

On the javascript side of course do things like use the debugger or even simple alert() statements.

One thing I didn't see in your fetch PHP script is you using header() to set the mimetype to be json since that is what you are trying to return.

zetastreak · March 20, 2019

3 hours ago, gizmola said:

So you need to start verifying what has actually worked. For example, var_dump($result) in the php script and see what is being returned. Tools like Postman are good for testing this type of thing if you can't figure out how to do it manually. You need to follow each step and assumption until you determine what isn't working the way you expect.

On the javascript side of course do things like use the debugger or even simple alert() statements.

One thing I didn't see in your fetch PHP script is you using header() to set the mimetype to be json since that is what you are trying to return.

sure i'll go through the process

Barand · March 20, 2019

I had play with google charts and made a couple changes to your function

function drawStudentwiseChart(chart_data, chart_main_title)
{
    var jsonData = chart_data;
    var data = new google.visualization.DataTable();
    data.addColumn('string', 'Name');                                           // ADD A LABEL COLUMN
    data.addColumn('number', 'Physics');
    data.addColumn('number', 'Maths');
    data.addColumn('number', 'Chemistry');
    data.addColumn('number', 'Biology');
    data.addColumn('number', 'SST');
    $.each(jsonData, function(i, jsonData){
        var Physics = jsonData['physics'];                                      // USE ARRAY NOTATION INSTEAD OF OBJECT NOTATION TO GET VALUES
        var Maths = jsonData['maths'];                                          // (Note: my table's field names were in lowercase)
        var Chemistry = jsonData['chemistry'];
        var Biology = jsonData['biology'];
        var SST = jsonData['sst'];

        data.addRows([["",Physics,Maths,Chemistry,Biology,SST]]);               // ADD VALUE FOR LABEL
    });
    var options = {
        title:chart_main_title,
        hAxis: {
            title: "Subjects"
        },
        vAxis: {
            title: 'Percentage'
        }
    };

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

zetastreak · March 20, 2019

4 hours ago, Barand said:

I had play with google charts and made a couple changes to your function


function drawStudentwiseChart(chart_data, chart_main_title)
{
    var jsonData = chart_data;
    var data = new google.visualization.DataTable();
    data.addColumn('string', 'Name');                                           // ADD A LABEL COLUMN
    data.addColumn('number', 'Physics');
    data.addColumn('number', 'Maths');
    data.addColumn('number', 'Chemistry');
    data.addColumn('number', 'Biology');
    data.addColumn('number', 'SST');
    $.each(jsonData, function(i, jsonData){
        var Physics = jsonData['physics'];                                      // USE ARRAY NOTATION INSTEAD OF OBJECT NOTATION TO GET VALUES
        var Maths = jsonData['maths'];                                          // (Note: my table's field names were in lowercase)
        var Chemistry = jsonData['chemistry'];
        var Biology = jsonData['biology'];
        var SST = jsonData['sst'];

        data.addRows([["",Physics,Maths,Chemistry,Biology,SST]]);               // ADD VALUE FOR LABEL
    });
    var options = {
        title:chart_main_title,
        hAxis: {
            title: "Subjects"
        },
        vAxis: {
            title: 'Percentage'
        }
    };

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

@Barand Well first of all many thanks to you for sharing your methods and improving my knowledge regarding these topics and other small issues. I adjusted my code accordingly with your specified edits, but i still get blank page i think i'm still having issues with parsing json data. And Thanks again for those extra efforts you made

Edited March 20, 2019 by zetastreak

Barand · March 20, 2019

It was working for me, except I didn't get any x-axis labels under the column (more reading required).

Here's my full code which is vitually the same as yours except for the ajax bit. Note I use the same php script for the ajax processing too.

<?php
include("db_inc.php");
$connect = pdoConnect();

//
//  HAVE WE RECEIVED AN AJAX REQUEST?
//
    if (isset($_GET['ajax'])) {
        if(isset($_GET["name"]))
        {
            $query = "
                 SELECT physics
                      , maths
                      , chemistry
                      , biology
                      , sst
                 FROM csv
                 WHERE name = ?
                 ORDER BY id ASC
                 ";
            $statement = $connect->prepare($query);
            $statement->execute( [ $_GET['name'] ] );
            $result = $statement->fetchAll();
            exit(json_encode($result));
        }
        else exit("Error");
    }

    
    function nameOptions($connect)
{
    $query = "SELECT DISTINCT name 
              FROM zeta 
              ORDER BY name DESC
             ";
    $statement = $connect->query($query);
    $opts = '';
    foreach ($statement as $s) {
        $opts .= "<option>{$s['name']}</option>\n";
    }
    return $opts;
}
?>
<!DOCTYPE html>
<html>
    <head>
        <title>Google charts</title>
        <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css">
        <script src="https://code.jquery.com/jquery-1.12.4.js"></script>
    </head>
    <body>
        <br /><br />
        <div class="container">
          <div class="panel panel-default">
                <div class="panel-heading">
                    <div class="row">
                        <div class="col-md-9">
                            <h3 class="panel-title">Student Wise Marks Data</h3>
                        </div>
                        <div class="col-md-3">
                            <select name="name" class="form-control" id="name">
                                <option value="">Select Student</option>
                                <?=nameOptions($connect)?>
                            </select>
                        </div>
                    </div>
                </div>
                <div class="panel-body">
                    <div id="chart_area" style="width: 1000px; height: 620px;"></div>
                </div>
            </div>
        </div>
    </body>
</html>
<script type="text/javascript" src="https://www.gstatic.com/charts/loader.js"></script>
<script type="text/javascript">
google.charts.load('current', {packages: ['corechart', 'bar']});
google.charts.setOnLoadCallback();

function load_student_data(name, title)
{
    var temp_title = title + ' '+name+'';
    $.ajax({
        url:"",
        method:"GET",
        data:{ajax:1, name:name},
        dataType:"JSON",
        success:function(data)
        {
            drawStudentwiseChart(data, temp_title);
        }
    });
}

function drawStudentwiseChart(chart_data, chart_main_title)
{
    var jsonData = chart_data;
    var data = new google.visualization.DataTable();
    data.addColumn('string', 'Name');                                           // ADD A LABEL COLUMN
    data.addColumn('number', 'Physics');
    data.addColumn('number', 'Maths');
    data.addColumn('number', 'Chemistry');
    data.addColumn('number', 'Biology');
    data.addColumn('number', 'SST');
    $.each(jsonData, function(i, jsonData){
        var Physics = jsonData['physics'];                                      // USE ARRAY NOTATION INSTEAD OF OBJECT NOTATION TO GET VALUES
        var Maths = jsonData['maths'];                                          // (Note: my table's field names were in lowercase)
        var Chemistry = jsonData['chemistry'];
        var Biology = jsonData['biology'];
        var SST = jsonData['sst'];

        data.addRows([["",Physics,Maths,Chemistry,Biology,SST]]);               // ADD VALUE FOR LABEL
    });
    var options = {
        title:chart_main_title,
        hAxis: {
            title: "Subjects"
        },
        vAxis: {
            title: 'Percentage'
        }
    };

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

</script>

<script>

$(document).ready(function(){

    $('#name').change(function(){
        var name = $(this).val();
        if(name != '')
        {
            load_student_data(name, 'Student wise marks data');
        }
    });

});

</script>

Gives

zetastreak · March 20, 2019

37 minutes ago, Barand said:

It was working for me, except I didn't get any x-axis labels under the column (more reading required).

Here's my full code which is vitually the same as yours except for the ajax bit. Note I use the same php script for the ajax processing too.


<?php
include("db_inc.php");
$connect = pdoConnect();

//
//  HAVE WE RECEIVED AN AJAX REQUEST?
//
    if (isset($_GET['ajax'])) {
        if(isset($_GET["name"]))
        {
            $query = "
                 SELECT physics
                      , maths
                      , chemistry
                      , biology
                      , sst
                 FROM csv
                 WHERE name = ?
                 ORDER BY id ASC
                 ";
            $statement = $connect->prepare($query);
            $statement->execute( [ $_GET['name'] ] );
            $result = $statement->fetchAll();
            exit(json_encode($result));
        }
        else exit("Error");
    }

    
    function nameOptions($connect)
{
    $query = "SELECT DISTINCT name 
              FROM zeta 
              ORDER BY name DESC
             ";
    $statement = $connect->query($query);
    $opts = '';
    foreach ($statement as $s) {
        $opts .= "<option>{$s['name']}</option>\n";
    }
    return $opts;
}
?>
<!DOCTYPE html>
<html>
    <head>
        <title>Google charts</title>
        <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css">
        <script src="https://code.jquery.com/jquery-1.12.4.js"></script>
    </head>
    <body>
        <br /><br />
        <div class="container">
          <div class="panel panel-default">
                <div class="panel-heading">
                    <div class="row">
                        <div class="col-md-9">
                            <h3 class="panel-title">Student Wise Marks Data</h3>
                        </div>
                        <div class="col-md-3">
                            <select name="name" class="form-control" id="name">
                                <option value="">Select Student</option>
                                <?=nameOptions($connect)?>
                            </select>
                        </div>
                    </div>
                </div>
                <div class="panel-body">
                    <div id="chart_area" style="width: 1000px; height: 620px;"></div>
                </div>
            </div>
        </div>
    </body>
</html>
<script type="text/javascript" src="https://www.gstatic.com/charts/loader.js"></script>
<script type="text/javascript">
google.charts.load('current', {packages: ['corechart', 'bar']});
google.charts.setOnLoadCallback();

function load_student_data(name, title)
{
    var temp_title = title + ' '+name+'';
    $.ajax({
        url:"",
        method:"GET",
        data:{ajax:1, name:name},
        dataType:"JSON",
        success:function(data)
        {
            drawStudentwiseChart(data, temp_title);
        }
    });
}

function drawStudentwiseChart(chart_data, chart_main_title)
{
    var jsonData = chart_data;
    var data = new google.visualization.DataTable();
    data.addColumn('string', 'Name');                                           // ADD A LABEL COLUMN
    data.addColumn('number', 'Physics');
    data.addColumn('number', 'Maths');
    data.addColumn('number', 'Chemistry');
    data.addColumn('number', 'Biology');
    data.addColumn('number', 'SST');
    $.each(jsonData, function(i, jsonData){
        var Physics = jsonData['physics'];                                      // USE ARRAY NOTATION INSTEAD OF OBJECT NOTATION TO GET VALUES
        var Maths = jsonData['maths'];                                          // (Note: my table's field names were in lowercase)
        var Chemistry = jsonData['chemistry'];
        var Biology = jsonData['biology'];
        var SST = jsonData['sst'];

        data.addRows([["",Physics,Maths,Chemistry,Biology,SST]]);               // ADD VALUE FOR LABEL
    });
    var options = {
        title:chart_main_title,
        hAxis: {
            title: "Subjects"
        },
        vAxis: {
            title: 'Percentage'
        }
    };

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

</script>

<script>

$(document).ready(function(){

    $('#name').change(function(){
        var name = $(this).val();
        if(name != '')
        {
            load_student_data(name, 'Student wise marks data');
        }
    });

});

</script>

Gives

That's a really optimised way of doing this. thanks for that! I owe you for this project! .-.

i modified my code to the same but for some odd reason i'm getting

Parse error: syntax error, unexpected '$opts' (T_VARIABLE) in charts.php on line 37

i've checked for unclosed semi-colons there are none

Barand · March 20, 2019

Have you got a missing ";" at the end of line 36?

zetastreak · March 20, 2019

1 minute ago, Barand said:

Have you got a missing ";" at the end of line 36?

I've thoroughly checked the whole file for missing ; i found none still the error persists

Barand · March 20, 2019

Post your nameOptions function that is giving the error.

zetastreak · March 20, 2019

1 minute ago, Barand said:

Post your nameOptions function that is giving the error.

function nameOptions($connect)
{
  $query = "SELECT DISTINCT name
              FROM csv
              ORDER BY name DESC
             ";
$statement = $connect->query($query);
$opts = ' ';
foreach ($statement as $s)
{
$opts .= "<option>{$s['name']}</option>\n";
}
    return $opts;
}

here ,it's the same as yours

zetastreak · March 20, 2019

@Barand i think it's because of the query not being able to execute correctly.

i copy pasted your code and modified required parameters still issue persists.

can you post your db_inc.php?

Barand · March 20, 2019

Here's the relevant bit

<?php
define("HOST",'localhost');
define("USERNAME",'????');
define("PASSWORD",'????');
define("DATABASE", "????");

function pdoConnect() 
{
    $dsn = "mysql:dbname=".DATABASE."; host=".HOST."; charset=utf8";

    $db = new pdo($dsn, USERNAME, PASSWORD, 
        [
            PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
            PDO::ATTR_EMULATE_PREPARES => false,
            PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
        ]);
    return $db;
}

zetastreak · March 20, 2019

12 minutes ago, Barand said:

Here's the relevant bit


<?php
define("HOST",'localhost');
define("USERNAME",'????');
define("PASSWORD",'????');
define("DATABASE", "????");

function pdoConnect() 
{
    $dsn = "mysql:dbname=".DATABASE."; host=".HOST."; charset=utf8";

    $db = new pdo($dsn, USERNAME, PASSWORD, 
        [
            PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
            PDO::ATTR_EMULATE_PREPARES => false,
            PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
        ]);
    return $db;
}

well tried this too , error still persists

atm , it's exactly your modified code in my files.

reading through error documentations if i could find something

Barand · March 20, 2019

Try deleting line 36 and then reentering it in case there's a hidden character lurking.

zetastreak · March 20, 2019

17 minutes ago, Barand said:

Try deleting line 36 and then reentering it in case there's a hidden character lurking.

did that, re-wrote the whole function again

still getting the error but now it's

Parse error: syntax error, unexpected '}' on line 35

zetastreak · March 20, 2019

23 minutes ago, Barand said:

Try deleting line 36 and then reentering it in case there's a hidden character lurking.

Rewriting the function did the thing, you were right! there were hidden characters lurking around

only this timme while rewriting it i missed ; in

$opts .= "<option>{$s['name']}</option>\n";

that's why i was getting

Parse error: syntax error, unexpected '}' on line 35

finally got it working.

Thank you for helping me

Barand · March 20, 2019

We've got out sync. My line 35 hasn't got one of those.

image.png.cc8ec9bfeb5d732bb3af705209802b2c.png

zetastreak · March 20, 2019

3 minutes ago, Barand said:

We've got out sync. My line 35 hasn't got one of those.

yea while re-writing my line numbers got changed '-' . well relating to line numbers in your snippet the error was at line 40.

Sign In

Creating Google Charts with dynamic data

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information