Skip to main content

Creating an Area Chart using Chart.js with PHP/MySQLi

Screenshot
Body

This tutorial tackles how to create a statistical representation of data in MySQL Database using Chart.js with PHP to fetch data from the database. In this tutorial, I'm going to show an area chart representation of data from our database. I've also added a form that can save data to our database to practice this tutorial.

Getting Started

I've used CDN for Bootstrap and jQuery in this tutorial so, you need an 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. I used XAMPP as he virtual server in this tuorial for my end to run the PHP script and store my database.

  1. Open PHPMyAdmin.
  2. Click databases, create a database and name it as chartjs.
  3. After creating a database, click the SQL and copy/paste the below codes. See the image below for detailed instructions.
  1. CREATE TABLE `sales` (
  2. `salesid` INT(11) NOT NULL AUTO_INCREMENT,
  3. `amount` DOUBLE NOT NULL,
  4. `sales_date` DATE NOT NULL,
  5. PRIMARY KEY(`salesid`)
database sql

Inserting Data into our Database

Next, we insert sample data into 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.
    1. INSERT INTO `sales` (`salesid`, `amount`, `sales_date`) VALUES
    2. (8, 100, '2021-01-01'),
    3. (9, 55, '2020-01-01'),
    4. (10, 200, '2021-02-02'),
    5. (11, 55, '2020-02-02'),
    6. (12, 175, '2021-03-03'),
    7. (13, 150, '2020-03-03'),
    8. (14, 150, '2021-04-04'),
    9. (15, 85, '2020-04-04'),
    10. (16, 99, '2021-04-04'),
    11. (17, 20, '2020-04-04'),
    12. (18, 180, '2021-05-05'),
    13. (19, 70, '2020-05-05'),
    14. (20, 225, '2020-06-06'),
    15. (21, 150, '2021-06-06'),
    16. (22, 120, '2021-07-07'),
    17. (23, 55, '2020-07-07'),
    18. (24, 199, '2021-08-08'),
    19. (25, 45, '2020-08-08'),
    20. (26, 130, '2021-09-09'),
    21. (27, 75, '2020-09-09'),
    22. (28, 300, '2021-10-10'),
    23. (29, 35, '2020-10-10'),
    24. (30, 250, '2021-11-11'),
    25. (31, 20, '2020-11-11'),
    26. (32, 220, '2021-12-12'),
    27. (33, 200, '2020-12-12'),
    28. (34, 45, '2020-01-05');
  3. Click Go button below.

Note: The below content are the instructions, functions, and simple explanations of the scripts. Save the files according to the file names above the scripts.

Creating the Interface

This is our index which contains our simple add form and our area chart representation of data from the database. This also contains our area chart script.

index.php
  1. <!DOCTYPE html>
  2. <html>
  3. <head>
  4. <title>Area Chart using Chart.js with PHP/MySQLi</title>
  5. <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css" />
  6. <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.1.0/jquery.min.js"></script>
  7. <script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/js/bootstrap.min.js"></script>
  8.  
  9. <!-- ChartJS -->
  10. <script src="chart.js/Chart.js"></script>
  11. </head>
  12. <body>
  13. <div class="container">
  14. <h1 class="page-header text-center">Area Chart using Chart.js with PHP/MySQLi</h1>
  15. <div class="row">
  16. <div class="col-md-3">
  17. <h3 class="page-header text-center">Add Sales</h3>
  18. <form method="POST" action="addsales.php">
  19. <div class="form-group">
  20. <label>Amount:</label>
  21. <input type="text" class="form-control" name="amount" required>
  22. </div>
  23. <div class="form-group">
  24. <label>Date:</label>
  25. <input type="date" class="form-control" name="sales_date" required>
  26. </div>
  27. <button type="submit" class="btn btn-primary"><span class="glyphicon glyphicon-floppy-disk"></span> Save</button>
  28. </form>
  29. </div>
  30. <div class="col-md-9">
  31. <div class="box box-success">
  32. <div class="box-header with-border">
  33. <?php
  34. //set timezone
  35. //date_default_timezone_set('Asia/Manila');
  36. $year = date('Y');
  37. ?>
  38. <h3 class="box-title">Sales Report (<?php echo $year-1; ?> vs <?php echo $year; ?>)</h3>
  39.  
  40. </div>
  41. <div class="box-body">
  42. <div class="chart">
  43. <canvas id="areaChart" style="height:250px"></canvas>
  44. </div>
  45. </div>
  46. <!-- /.box-body -->
  47. </div>
  48. </div>
  49. </div>
  50. </div>
  51. <?php include('data.php'); ?>
  52. <script>
  53. $(function () {
  54. var areaChartCanvas = $('#areaChart').get(0).getContext('2d')
  55. // This will get the first returned node in the jQuery collection.
  56. var areaChart = new Chart(areaChartCanvas)
  57.  
  58. var areaChartData = {
  59. labels : ['January', 'February', 'March', 'April', 'May', 'June', 'July', 'August', 'September', 'October', 'November', 'December'],
  60. datasets: [
  61. {
  62. label : 'Previous Year',
  63. fillColor : 'rgba(210, 214, 222, 1)',
  64. strokeColor : 'rgba(210, 214, 222, 1)',
  65. pointColor : 'rgba(210, 214, 222, 1)',
  66. pointStrokeColor : '#c1c7d1',
  67. pointHighlightFill : '#fff',
  68. pointHighlightStroke: 'rgba(220,220,220,1)',
  69. data : [ "<?php echo $pjan; ?>",
  70. "<?php echo $pfeb; ?>",
  71. "<?php echo $pmar; ?>",
  72. "<?php echo $papr; ?>",
  73. "<?php echo $pmay; ?>",
  74. "<?php echo $pjun; ?>",
  75. "<?php echo $pjul; ?>",
  76. "<?php echo $paug; ?>",
  77. "<?php echo $psep; ?>",
  78. "<?php echo $poct; ?>",
  79. "<?php echo $pnov; ?>",
  80. "<?php echo $pdec; ?>"
  81. ]
  82. },
  83. {
  84. label : 'This Year',
  85. fillColor : 'rgba(60,141,188,0.9)',
  86. strokeColor : 'rgba(60,141,188,0.8)',
  87. pointColor : '#3b8bba',
  88. pointStrokeColor : 'rgba(60,141,188,1)',
  89. pointHighlightFill : '#fff',
  90. pointHighlightStroke: 'rgba(60,141,188,1)',
  91. data : [ "<?php echo $tjan; ?>",
  92. "<?php echo $tfeb; ?>",
  93. "<?php echo $tmar; ?>",
  94. "<?php echo $tapr; ?>",
  95. "<?php echo $tmay; ?>",
  96. "<?php echo $tjun; ?>",
  97. "<?php echo $tjul; ?>",
  98. "<?php echo $taug; ?>",
  99. "<?php echo $tsep; ?>",
  100. "<?php echo $toct; ?>",
  101. "<?php echo $tnov; ?>",
  102. "<?php echo $tdec; ?>"
  103. ]
  104. }
  105. ]
  106. }
  107. var areaChartOptions = {
  108. //Boolean - If we should show the scale at all
  109. showScale : true,
  110. //Boolean - Whether grid lines are shown across the chart
  111. scaleShowGridLines : false,
  112. //String - Colour of the grid lines
  113. scaleGridLineColor : 'rgba(0,0,0,.05)',
  114. //Number - Width of the grid lines
  115. scaleGridLineWidth : 1,
  116. //Boolean - Whether to show horizontal lines (except X axis)
  117. scaleShowHorizontalLines: true,
  118. //Boolean - Whether to show vertical lines (except Y axis)
  119. scaleShowVerticalLines : true,
  120. //Boolean - Whether the line is curved between points
  121. bezierCurve : true,
  122. //Number - Tension of the bezier curve between points
  123. bezierCurveTension : 0.3,
  124. //Boolean - Whether to show a dot for each point
  125. pointDot : false,
  126. //Number - Radius of each point dot in pixels
  127. pointDotRadius : 4,
  128. //Number - Pixel width of point dot stroke
  129. pointDotStrokeWidth : 1,
  130. //Number - amount extra to add to the radius to cater for hit detection outside the drawn point
  131. pointHitDetectionRadius : 20,
  132. //Boolean - Whether to show a stroke for datasets
  133. datasetStroke : true,
  134. //Number - Pixel width of dataset stroke
  135. datasetStrokeWidth : 2,
  136. //Boolean - Whether to fill the dataset with a color
  137. datasetFill : true,
  138. //String - A legend template
  139. legendTemplate : '<ul class="<%=name.toLowerCase()%>-legend"><% for (var i=0; i<datasets.length; i++){%><li><span style="background-color:<%=datasets[i].lineColor%>"></span><%if(datasets[i].label){%><%=datasets[i].label%><%}%></li><%}%></ul>',
  140. //Boolean - whether to maintain the starting aspect ratio or not when responsive, if set to false, will take up entire container
  141. maintainAspectRatio : true,
  142. //Boolean - whether to make the chart responsive to window resizing
  143. responsive : true
  144. }
  145.  
  146. //Create the line chart
  147. areaChart.Line(areaChartData, areaChartOptions)
  148.  
  149. })
  150. </script>
  151. </body>
  152. </html>

Creating the Form's Save Script

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

addsales.php
  1. <?php
  2. $conn = new mysqli("localhost", "root", "", "chartjs");
  3.  
  4. if ($conn->connect_error) {
  5. die("Connection failed: " . $conn->connect_error);
  6. }
  7.  
  8. $amount=$_POST['amount'];
  9. $sales_date=$_POST['sales_date'];
  10.  
  11. $sql="insert into sales (amount, sales_date) values ('$amount', '$sales_date')";
  12. $conn->query($sql);
  13.  
  14. header('location:index.php');
  15. ?>

Fetching The Data in the Database

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 an area chart.

data.php
  1. <?php
  2. $conn = new mysqli("localhost", "root", "", "chartjs");
  3.  
  4. if ($conn->connect_error) {
  5. die("Connection failed: " . $conn->connect_error);
  6. }
  7.  
  8. //set timezone
  9. //date_default_timezone_set('Asia/Manila');
  10. $year = date('Y');
  11. $total=array();
  12. for ($month = 1; $month <= 12; $month ++){
  13. $sql="select *, sum(amount) as total from sales where month(sales_date)='$month' and year(sales_date)='$year'";
  14. $query=$conn->query($sql);
  15. $row=$query->fetch_array();
  16.  
  17. $total[]=$row['total'];
  18. }
  19.  
  20. $tjan = $total[0];
  21. $tfeb = $total[1];
  22. $tmar = $total[2];
  23. $tapr = $total[3];
  24. $tmay = $total[4];
  25. $tjun = $total[5];
  26. $tjul = $total[6];
  27. $taug = $total[7];
  28. $tsep = $total[8];
  29. $toct = $total[9];
  30. $tnov = $total[10];
  31. $tdec = $total[11];
  32.  
  33. $pyear = $year - 1;
  34. $pnum=array();
  35.  
  36. for ($pmonth = 1; $pmonth <= 12; $pmonth ++){
  37. $sql="select *, sum(amount) as ptotal from sales where month(sales_date)='$pmonth' and year(sales_date)='$pyear'";
  38. $pquery=$conn->query($sql);
  39. $prow=$pquery->fetch_array();
  40.  
  41. $ptotal[]=$prow['ptotal'];
  42. }
  43.  
  44. $pjan = $ptotal[0];
  45. $pfeb = $ptotal[1];
  46. $pmar = $ptotal[2];
  47. $papr = $ptotal[3];
  48. $pmay = $ptotal[4];
  49. $pjun = $ptotal[5];
  50. $pjul = $ptotal[6];
  51. $paug = $ptotal[7];
  52. $psep = $ptotal[8];
  53. $poct = $ptotal[9];
  54. $pnov = $ptotal[10];
  55. $pdec = $ptotal[11];
  56. ?>

Demo

That ends this tutorial. I hope this tutorial will be useful for your future projects. Explore more on this website for more tutorials and free source codes.

I also included the working sample source code that I have made for this tutorial. Feel Free to download and modify it the way wanted.

Happy Coding :)

Add new comment