Statistical Representation using Chart.js

Submitted by nurhodelta_17 on Friday, December 22, 2017 - 16:15.

Getting Started

I've used CDN for Bootstrap and jQuery in this tutorial so, you need internet connection for them to work. Chart.js used in this tutorial is included in the downloadable file of this tutorial.

Creating our Database

First, we're going to create our database. 1. Open phpMyAdmin. 2. Click databases, create a database and name it as chartjs. 3. After creating a database, click the SQL and paste the below codes. See image below for detailed instruction.

CREATE TABLE `sales` (
  `salesid` INT(11) NOT NULL AUTO_INCREMENT,
  `amount` DOUBLE NOT NULL,
  `sales_date` DATE NOT NULL,
PRIMARY KEY(`salesid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Inserting Data into our Database

Next, we insert sample data to our database to be used in our chart. 1. Click chartjs database that we have created earlier. 2. Click SQL and paste the following codes.

INSERT INTO `sales` (`salesid`, `amount`, `sales_date`) VALUES
(8, 100, '2017-01-01'),
(9, 55, '2016-01-01'),
(10, 200, '2017-02-02'),
(11, 55, '2016-02-02'),
(12, 175, '2017-03-03'),
(13, 150, '2016-03-03'),
(14, 150, '2017-04-04'),
(15, 85, '2016-04-04'),
(16, 99, '2017-04-04'),
(17, 20, '2016-04-04'),
(18, 180, '2017-05-05'),
(19, 70, '2016-05-05'),
(20, 225, '2016-06-06'),
(21, 150, '2017-06-06'),
(22, 120, '2017-07-07'),
(23, 55, '2016-07-07'),
(24, 199, '2017-08-08'),
(25, 45, '2016-08-08'),
(26, 130, '2017-09-09'),
(27, 75, '2016-09-09'),
(28, 300, '2017-10-10'),
(29, 35, '2016-10-10'),
(30, 250, '2017-11-11'),
(31, 20, '2016-11-11'),
(32, 220, '2017-12-12'),
(33, 200, '2016-12-12'),
(34, 45, '2016-01-05');

3. Click Go button below.

index.php

This is our index which contains our simple add form and our statistical representation of data from database. This also contains our barchart script.

<!DOCTYPE html>
<html>
<head>
        <title>Statistical Representation using Chart.js</title>
        <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css" />
        <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.1.0/jquery.min.js"></script>
        <script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/js/bootstrap.min.js"></script>
 
        <!-- ChartJS -->
        <script src="chart.js/Chart.js"></script>
</head>
<body>
<div class="container">
        <h1 class="page-header text-center">Statistical Report using Chart.js</h1>
        <div class="row">
                <div class="col-md-3">
                        <h3 class="page-header text-center">Add Sales</h3>
                        <form method="POST" action="addsales.php">
                                <div class="form-group">
                                        <label>Amount:</label>
                                        <input type="text" class="form-control" name="amount">
                                </div>
                                <div class="form-group">
                                        <label>Date:</label>
                                        <input type="date" class="form-control" name="sales_date">
                                </div>
                                <button type="submit" class="btn btn-primary"><span class="glyphicon glyphicon-floppy-disk"></span> Save</button>
                        </form>
                </div>
                <div class="col-md-9">
                        <div class="box box-success">
            <div class="box-header with-border">
                <?php
                        //set timezone
                                        //date_default_timezone_set('Asia/Manila');
                                        $year = date('Y');
                ?>
              <h3 class="box-title">Sales Report (<?php echo $year-1; ?> vs <?php echo $year; ?>)</h3>
 
            </div>
            <div class="box-body">
              <div class="chart">
                <canvas id="barChart" style="height:230px"></canvas>
              </div>
            </div>
            <!-- /.box-body -->
          </div>
                </div>
        </div>
</div>
<?php include('data.php'); ?>
<script>
  $(function () {
    var barChartData = {
      labels  : ['January', 'February', 'March', 'April', 'May', 'June', 'July', 'August', 'September', 'October', 'November', 'December'],
      datasets: [
        {
          label               : 'Previous Year',
          fillColor           : 'rgba(210, 214, 222, 1)',
          strokeColor         : 'rgba(210, 214, 222, 1)',
          pointColor          : 'rgba(210, 214, 222, 1)',
          pointStrokeColor    : '#c1c7d1',
          pointHighlightFill  : '#fff',
          pointHighlightStroke: 'rgba(220,220,220,1)',
          data                : [ "<?php echo $pjan; ?>",
                                  "<?php echo $pfeb; ?>",
                                  "<?php echo $pmar; ?>",
                                  "<?php echo $papr; ?>",
                                  "<?php echo $pmay; ?>",
                                  "<?php echo $pjun; ?>",
                                  "<?php echo $pjul; ?>",
                                  "<?php echo $paug; ?>",
                                  "<?php echo $psep; ?>",
                                  "<?php echo $poct; ?>",
                                  "<?php echo $pnov; ?>",
                                  "<?php echo $pdec; ?>" 
                                ]
        },
        {
          label               : 'This Year',
          fillColor           : 'rgba(60,141,188,0.9)',
          strokeColor         : 'rgba(60,141,188,0.8)',
          pointColor          : '#3b8bba',
          pointStrokeColor    : 'rgba(60,141,188,1)',
          pointHighlightFill  : '#fff',
          pointHighlightStroke: 'rgba(60,141,188,1)',
          data                : [ "<?php echo $tjan; ?>",
                                  "<?php echo $tfeb; ?>",
                                  "<?php echo $tmar; ?>",
                                  "<?php echo $tapr; ?>",
                                  "<?php echo $tmay; ?>",
                                  "<?php echo $tjun; ?>",
                                  "<?php echo $tjul; ?>",
                                  "<?php echo $taug; ?>",
                                  "<?php echo $tsep; ?>",
                                  "<?php echo $toct; ?>",
                                  "<?php echo $tnov; ?>",
                                  "<?php echo $tdec; ?>" 
                                ]
        }
      ]
    }
    var barChartCanvas                   = $('#barChart').get(0).getContext('2d')
    var barChart                         = new Chart(barChartCanvas)
    var barChartData                     = barChartData
    barChartData.datasets[1].fillColor   = '#00a65a'
    barChartData.datasets[1].strokeColor = '#00a65a'
    barChartData.datasets[1].pointColor  = '#00a65a'
    var barChartOptions                  = {
      //Boolean - Whether the scale should start at zero, or an order of magnitude down from the lowest value
      scaleBeginAtZero        : true,
      //Boolean - Whether grid lines are shown across the chart
      scaleShowGridLines      : true,
      //String - Colour of the grid lines
      scaleGridLineColor      : 'rgba(0,0,0,.05)',
      //Number - Width of the grid lines
      scaleGridLineWidth      : 1,
      //Boolean - Whether to show horizontal lines (except X axis)
      scaleShowHorizontalLines: true,
      //Boolean - Whether to show vertical lines (except Y axis)
      scaleShowVerticalLines  : true,
      //Boolean - If there is a stroke on each bar
      barShowStroke           : true,
      //Number - Pixel width of the bar stroke
      barStrokeWidth          : 2,
      //Number - Spacing between each of the X value sets
      barValueSpacing         : 5,
      //Number - Spacing between data sets within X values
      barDatasetSpacing       : 5,
      //String - A legend template
      legendTemplate          : '<ul class="<%=name.toLowerCase()%>-legend"><% for (var i=0; i<datasets.length; i++){%><li><span style="background-color:<%=datasets[i].fillColor%>"></span><%if(datasets[i].label){%><%=datasets[i].label%><%}%></li><%}%></ul>',
      //Boolean - whether to make the chart responsive
      responsive              : true,
      maintainAspectRatio     : true
    }
 
    barChartOptions.datasetFill = false
    barChart.Bar(barChartData, barChartOptions)
 
  })
</script>
</body>
</html>

addsales.php

This is our PHP code in adding data into our database.

<?php
        $conn = new mysqli("localhost", "root", "", "chartjs");
 
        if ($conn->connect_error) {
            die("Connection failed: " . $conn->connect_error);
        }
 
        $amount=$_POST['amount'];
        $sales_date=$_POST['sales_date'];
 
        $sql="insert into sales (amount, sales_date) values ('$amount', '$sales_date')";
        $conn->query($sql);
 
        header('location:index.php');
?>

data.php

Lastly, this is our PHP code that contains our data that we're gonna be using in our chart.js to make statistical data in the form of bar chart.

<?php
        $conn = new mysqli("localhost", "root", "", "chartjs");
 
        if ($conn->connect_error) {
            die("Connection failed: " . $conn->connect_error);
        }
 
        //set timezone
        //date_default_timezone_set('Asia/Manila');
        $year = date('Y');
        $total=array();
        for ($month = 1; $month <= 12; $month ++){
                $sql="select *, sum(amount) as total from sales where month(sales_date)='$month' and year(sales_date)='$year'";
                $query=$conn->query($sql);
                $row=$query->fetch_array();
 
                $total[]=$row['total'];
        }
 
        $tjan = $total[0];
        $tfeb = $total[1];
        $tmar = $total[2];
        $tapr = $total[3];
        $tmay = $total[4];
        $tjun = $total[5];
        $tjul = $total[6];
        $taug = $total[7];
        $tsep = $total[8];
        $toct = $total[9];
        $tnov = $total[10];
        $tdec = $total[11];
 
        $pyear = $year - 1;
        $pnum=array();
 
        for ($pmonth = 1; $pmonth <= 12; $pmonth ++){
                $sql="select *, sum(amount) as ptotal from sales where month(sales_date)='$pmonth' and year(sales_date)='$pyear'";
                $pquery=$conn->query($sql);
                $prow=$pquery->fetch_array();
 
                $ptotal[]=$prow['ptotal'];
        }
        
        $pjan = $ptotal[0];
        $pfeb = $ptotal[1];
        $pmar = $ptotal[2];
        $papr = $ptotal[3];
        $pmay = $ptotal[4];
        $pjun = $ptotal[5];
        $pjul = $ptotal[6];
        $paug = $ptotal[7];
        $psep = $ptotal[8];
        $poct = $ptotal[9];
        $pnov = $ptotal[10];
        $pdec = $ptotal[11];
?>

That ends this tutorial. Happy Coding :)

Comments

Error

Nice concept nurhodelta_17, I get this error on line 16 of data.php "Fatal error: call to a member function fetch_array() on boolean" been trying to fix it to no avail

Add new comment

Add new comment
810 views