Jump to content
zetastreak

Creating Google Charts with dynamic data

Recommended Posts

Posted (edited)

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 by zetastreak

Share this post


Link to post
Share on other sites

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);
}

 

  • Thanks 1

Share this post


Link to post
Share on other sites
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

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites
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

 

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites
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

Share this post


Link to post
Share on other sites

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);
}

 

  • Thanks 1

Share this post


Link to post
Share on other sites
Posted (edited)
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 by zetastreak

Share this post


Link to post
Share on other sites

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

Capture.PNG

Share this post


Link to post
Share on other sites
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

Capture.PNG

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

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites
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

Share this post


Link to post
Share on other sites

Post your nameOptions function that is giving the error.

Share this post


Link to post
Share on other sites
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

Share this post


Link to post
Share on other sites

@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?

Share this post


Link to post
Share on other sites

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;
}

 

Share this post


Link to post
Share on other sites
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

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites
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

 

Share this post


Link to post
Share on other sites
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 :)

Share this post


Link to post
Share on other sites

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

image.png.cc8ec9bfeb5d732bb3af705209802b2c.png

Share this post


Link to post
Share on other sites
3 minutes ago, Barand said:

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

image.png.cc8ec9bfeb5d732bb3af705209802b2c.png

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

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.


×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.