Grouped Bar Chart from mySQL database using ChartJS and PHP

Question

index.php - fetches data from mySQL database

<?php

header('Content-Type: application/json');

$conn = mysqli_connect('localhost', 'root', '', 'registration');

if (!$conn) {
    die("Connection failed: " . mysqli_connect_error());
}

$query = sprintf("SELECT ratingid, variety, quality FROM chartdata ORDER BY ratingid");

$result = mysqli_query($conn, $query);

$data = array();
foreach ($result as $row) {
    $data[] = $row;
}

mysqli_close($conn);

print json_encode($data);
?>

app.js

     $(document).ready(function(){
     $.ajax({
     url: "http://localhost/mychart4/index.php",
     method: "GET",
     success: function(data) {
      console.log(data);
      var rating = [];
      var variety = [];
      var quality = [];

      for(var i in data) {
        rating.push(data[i].ratingid);
        variety.push(data[i].variety);
        quality.push(data[i]).quality);
      }

      var chartdata = {
        labels: rating,
        datasets: [
          {
            label: 'Red',
            backgroundColor: 'rgba(200, 200, 200, 0.75)',
            borderColor: 'rgba(200, 200, 200, 0.75)',
            hoverBackgroundColor: 'rgba(200, 200, 200, 1)',
            hoverBorderColor: 'rgba(200, 200, 200, 1)',
            data: variety
          },
          {
            label: 'Blue',
            backgroundColor: 'rgba(200, 200, 200, 0.75)',
            borderColor: 'rgba(200, 200, 200, 0.75)',
            hoverBackgroundColor: 'rgba(200, 200, 200, 1)',
            hoverBorderColor: 'rgba(200, 200, 200, 1)',
            data: quality
          }
        ]
      };

      var ctx = $("#mycanvas");

      var barGraph = new Chart(ctx, {
        type: 'bar',
        data: chartdata,
        options: {
          barValueSpacing: 20,
          title: {
                display: true,
                text: 'Variety',
                fontSize: 20
          },
          scales: {
            xAxes: [{
              scaleLabel: {
                display: true,
                labelString: 'Rating options'
              }
            }],
            yAxes: [{
              scaleLabel: {
                display: true,
                labelString: 'Amount'
              },
              ticks: {
                beginAtZero: true
              }
            }]
          }
        }
      });
    },
    error: function(data) {
      console.log(data);
     }
     });
    });

bargaph.html

<!DOCTYPE html>
<html>
  <head>
      <title>ChartJS - BarGraph</title>
      <style type="text/css">
      #chart-container {
        width: 640px;
        height: auto;
      }
    </style>
  </head>
  <body>
    <div id="chart-container">
      <canvas id="mycanvas"></canvas>
    </div>

    <!-- javascript -->
    <script type="text/javascript" src="js/jquery.min.js"></script>
    <script type="text/javascript" src="js/Chart.min.js"></script>
    <script type="text/javascript" src="js/app.js"></script>
  </body>
</html>

I'm trying to render a grouped bar chart using ChartJS with values from several columns(variety,quality) in my mySQL database. However, the browser does not display anything when I open the HTML file. I'm not sure what the issue might be since the bar chart rendered correctly when I tried it with one column(variety). This is the output when I print out the JSON array formed from the data:

[{"ratingid":"1","variety":"8","quality":"1"},{"ratingid":"2","variety":"1","quality":"9"},{"ratingid":"3","variety":"1","quality":"0"},{"ratingid":"4","variety":"11","quality":"11"},{"ratingid":"5","variety":"1","quality":"1"}]

Note: The object-oriented interface to mysqli is significantly less verbose, making code easier to read and audit, and is not easily confused with the obsolete mysql_query interface. Before you get too invested in the procedural style it’s worth switching over. Example: $db = new mysqli(…) and $db->prepare("…") The procedural interface is an artifact from the PHP 4 era when mysqli API was introduced and ideally should not be used in new code. — tadman
– tadman, Commented Mar 9, 2019 at 21:18
Is it advisable to mix up both procedural and object-oriented since this is part of a project I'm doing that I have already done in procedural style for the major part — Anthony Weru
– Anthony Weru, Commented Mar 10, 2019 at 3:25
Not ideal, but it's not a big deal if it takes a while to convert over. These two interfaces are 100% inter-operable. The OO form just prevents a lot of mistakes and is way easier to read. — tadman
– tadman, Commented Mar 10, 2019 at 3:32
Okay I'll try it out. I haven't been able to solve the chart rendering issue yet. I have tried removing the extra bracket as advised and opening the HTML file again on Chrome but still no output. — Anthony Weru
– Anthony Weru, Commented Mar 10, 2019 at 3:46

tadman · Accepted Answer · 2019-03-10 03:48:25Z

1

There's a very small mistake in the code that needs to be removed.

Current Code:

quality.push(data[i]).quality);

Correction:

quality.push(data[i].quality);

There's just an additional ')' that needs to be removed and it worked...

Bar Chart

edited Mar 10, 2019 at 3:48

tadman

212k23 gold badges237 silver badges266 bronze badges

answered Mar 9, 2019 at 21:09

Kamran Feroz

1167 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Anthony Weru Over a year ago

I've removed the extra closing bracket and saved the file but it still won't render my chart. I'm using Chrome as my browser if that helps.

Kamran Feroz · Accepted Answer · 2019-03-11 07:45:37Z

Complete working code as below;

<!-- index.php  -->

<?php

header('Content-Type: application/json');

$conn = mysqli_connect('localhost', 'root', '', 'registration');

if (!$conn) {
    die("Connection failed: " . mysqli_connect_error());
}

$query = sprintf("SELECT ratingid, variety, quality FROM chartdata ORDER BY
ratingid");

$result = mysqli_query($conn, $query);

$data = array();
foreach ($result as $row) {
    $data[] = $row;
}

mysqli_close($conn);

print json_encode($data);

//-- app.js

$(document).ready(function () {
    $.ajax({
            url: "http://localhost/test/barChart/index.php",
            method: "GET",
            success: function (data) {
                console.log(data);
                var rating = [];
                var variety = [];
                var quality = [];

                for (var i in data) {
                    rating.push(data[i].ratingid);
                    variety.push(data[i].variety);
                    quality.push(data[i].quality);
            }

            var chartdata = {
                labels: rating,
                datasets: [{
                        label: 'Red',
                        backgroundColor: 'rgb(255, 0, 0)',
                        borderColor: 'rgba(200, 200, 200, 0.75)',
                        hoverBackgroundColor: 'rgba(200, 200, 200, 1)',
                        hoverBorderColor: 'rgba(200, 200, 200, 1)',
                        data: variety
                    },
                    {
                        label: 'Blue',
                        backgroundColor: 'rgb(0, 0, 255)',
                        borderColor: 'rgba(200, 200, 200, 0.75)',
                        hoverBackgroundColor: 'rgba(200, 200, 200, 1)',
                        hoverBorderColor: 'rgba(200, 200, 200, 1)',
                        data: quality
                    }
                ]
            };

            var ctx = $("#mycanvas");

            var barGraph = new Chart(ctx, {
                type: 'bar',
                data: chartdata,
                options: {
                    barValueSpacing: 20,
                    title: {
                        display: true,
                        text: 'Variety',
                        fontSize: 20
                    },
                    scales: {
                        xAxes: [{
                            scaleLabel: {
                                display: true,
                                labelString: 'Rating options'
                            }
                        }],
                        yAxes: [{
                            scaleLabel: {
                                display: true,
                                labelString: 'Amount'
                            },
                            ticks: {
                                beginAtZero: true
                            }
                        }]
                    }
                }
            });
        },
        error: function (data) {
            console.log(data);
        }
    });
});

<!-- bargraph.html -->

<!DOCTYPE html>
<html>
<head>
  <title>ChartJS - BarGraph</title>
  <style type="text/css">
  #chart-container {
    width: 640px;
    height: auto;
  }
</style>
</head>
<body>
<div id="chart-container">
  <canvas id="mycanvas"></canvas>
</div>

<!-- javascript -->
<script type="text/javascript" src="https://code.jquery.com/jquery-3.3.1.min.js"></script>
<script type="text/javascript" src="https://cdnjs.cloudflare.com/ajax/libs/Chart.js/2.7.3/Chart.min.js"></script>
<script type="text/javascript" src="app.js"></script>
</body>
</html>

Collectives™ on Stack Overflow

Grouped Bar Chart from mySQL database using ChartJS and PHP

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related