Jump to content

Creating Google Charts with dynamic data


zetastreak

Recommended Posts

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

Link to comment
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);
}

 

Link to comment
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

Link to comment
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.

Link to comment
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

Link to comment
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);
}

 

Link to comment
Share on other sites

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 :)

Link to comment
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

Link to comment
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

Link to comment
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

Link to comment
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;
}

 

Link to comment
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

Link to comment
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

 

Link to comment
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 :)

Link to comment
Share on other sites

Archived

This topic is now archived and is closed to further replies.

×
×
  • 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.