How to Create a Bar Graph using PHP/MySQLi and ChartJS

Submitted by janobe on Thursday, February 4, 2021 - 15:35.

In this tutorial, I will teach you how to create a Bar Graph using PHP/MySQLi. Bar graphs are used to compare products and track the changes of product sales over time. It is determined by the category of data that can be viewed in rectangular bars with heights equivalent to the values presented. You can also check my previous tutorial which is How to Create a Pie Graph.

Let’s Begin

First, download and install the latest version of XAMPP. Then open the XAMPP's Control Panel and start "Apache" and "MySQL".

Creating Database

Create a database named “salesdb”.Execute the following query for adding the table and inserting data in the table.

  1. CREATE TABLE `tblsales` (
  2.   `SalesId` int(11) NOT NULL,
  3.   `Product` varchar(90) NOT NULL,
  4.   `TotalSales` double NOT NULL
  5. ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
  6.  
  7. --
  8. -- Dumping data for table `tblsales`
  9. --
  10.  
  11. INSERT INTO `tblsales` (`SalesId`, `Product`, `TotalSales`) VALUES
  12. (1, 'Surf Powder', 1400),
  13. (2, 'Mr. Clean Powder', 800),
  14. (3, 'Tide Powder', 5052),
  15. (4, 'Ariel Powder', 8030);
  16.  
  17. --
  18. -- Indexes for dumped tables
  19. --
  20.  
  21. --
  22. -- Indexes for table `tblsales`
  23. --
  24. ALTER TABLE `tblsales`
  25.   ADD PRIMARY KEY (`SalesId`);
  26.  
  27. --
  28. -- AUTO_INCREMENT for dumped tables
  29. --
  30.  
  31. --
  32. -- AUTO_INCREMENT for table `tblsales`
  33. --
  34. ALTER TABLE `tblsales`
  35.   MODIFY `SalesId` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=5;

Creating HTML and PHP Script

Step 1

Create a landing page and name it “index.php”.

Step 2

Do the following codes for the index page. 

  1. <!DOCTYPE html>
  2. <html lang="en"> 
  3.     <head>
  4.         <meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
  5.         <meta name="viewport" content="width=device-width, initial-scale=1.0">
  6.         <title>Graph</title> 
  7.     </head>
  8.     <body>
  9.         <div style="width:30%;hieght:20%;text-align:center">
  10.             <h2 class="page-header" >Analytics Reports </h2>
  11.             <div>Product </div>
  12.             <canvas  id="chartjs_bar"></canvas> 
  13.         </div>    
  14.     </body>
  15. </html>

Step 3

Add the following extension to access Chart.js Libraries.

  1.   <script src="//code.jquery.com/jquery-1.9.1.js"></script>
  2.   <script src="//cdnjs.cloudflare.com/ajax/libs/Chart.js/2.4.0/Chart.min.js"></script>

Step 4

Create a php script for fetching the data in the database.

  1. <?php
  2. $con  = mysqli_connect("localhost","root","","salesdb");
  3.  if (!$con) {
  4.      # code...
  5.     echo "Problem in database connection! Contact administrator!" . mysqli_error();
  6.  }else{
  7.          $sql ="SELECT * FROM tblsales";
  8.          $result = mysqli_query($con,$sql);
  9.          $chart_data="";
  10.          while ($row = mysqli_fetch_array($result)) { 
  11.  
  12.             $productname[]  = $row['Product']  ;
  13.             $sales[] = $row['TotalSales'];
  14.         }
  15.  
  16.  
  17.  }
  18.  
  19.  
  20. ?>

Step 5

Create a JavaScript script for displaying a graph.

  1. <script type="text/javascript">
  2.       var ctx = document.getElementById("chartjs_bar").getContext('2d');
  3.                 var myChart = new Chart(ctx, {
  4.                     type: 'bar',
  5.                     data: {
  6.                         labels:<?php echo json_encode($productname); ?>,
  7.                         datasets: [{
  8.                             backgroundColor: [
  9.                                "#5969ff",
  10.                                 "#ff407b",
  11.                                 "#25d5f2",
  12.                                 "#ffc750",
  13.                                 "#2ec551",
  14.                                 "#7040fa",
  15.                                 "#ff004e"
  16.                             ],
  17.                             data:<?php echo json_encode($sales); ?>,
  18.                         }]
  19.                     },
  20.                     options: {
  21.                            legend: {
  22.                         display: true,
  23.                         position: 'bottom',
  24.  
  25.                         labels: {
  26.                             fontColor: '#71748d',
  27.                             fontFamily: 'Circular Std Book',
  28.                             fontSize: 14,
  29.                         }
  30.                     },
  31.  
  32.  
  33.                 }
  34.                 });
  35.     </script>

Full Source Code

  1. <?php
  2. $con  = mysqli_connect("localhost","root","","salesdb");
  3.  if (!$con) {
  4.      # code...
  5.     echo "Problem in database connection! Contact administrator!" . mysqli_error();
  6.  }else{
  7.          $sql ="SELECT * FROM tblsales";
  8.          $result = mysqli_query($con,$sql);
  9.          $chart_data="";
  10.          while ($row = mysqli_fetch_array($result)) { 
  11.  
  12.             $productname[]  = $row['Product']  ;
  13.             $sales[] = $row['TotalSales'];
  14.         }
  15.  
  16.  
  17.  }
  18.  
  19.  
  20. ?>
  21. <!DOCTYPE html>
  22. <html lang="en"> 
  23.     <head>
  24.         <meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
  25.         <meta name="viewport" content="width=device-width, initial-scale=1.0">
  26.         <title>Graph</title> 
  27.     </head>
  28.     <body>
  29.         <div style="width:60%;hieght:20%;text-align:center">
  30.             <h2 class="page-header" >Analytics Reports </h2>
  31.             <div>Product </div>
  32.             <canvas  id="chartjs_bar"></canvas> 
  33.         </div>    
  34.     </body>
  35.   <script src="//code.jquery.com/jquery-1.9.1.js"></script>
  36.   <script src="//cdnjs.cloudflare.com/ajax/libs/Chart.js/2.4.0/Chart.min.js"></script>
  37. <script type="text/javascript">
  38.       var ctx = document.getElementById("chartjs_bar").getContext('2d');
  39.                 var myChart = new Chart(ctx, {
  40.                     type: 'bar',
  41.                     data: {
  42.                         labels:<?php echo json_encode($productname); ?>,
  43.                         datasets: [{
  44.                             backgroundColor: [
  45.                                "#5969ff",
  46.                                 "#ff407b",
  47.                                 "#25d5f2",
  48.                                 "#ffc750",
  49.                                 "#2ec551",
  50.                                 "#7040fa",
  51.                                 "#ff004e"
  52.                             ],
  53.                             data:<?php echo json_encode($sales); ?>,
  54.                         }]
  55.                     },
  56.                     options: {
  57.                            legend: {
  58.                         display: true,
  59.                         position: 'bottom',
  60.  
  61.                         labels: {
  62.                             fontColor: '#71748d',
  63.                             fontFamily: 'Circular Std Book',
  64.                             fontSize: 14,
  65.                         }
  66.                     },
  67.  
  68.  
  69.                 }
  70.                 });
  71.     </script>
  72. </html>

And that's the end of our tutorial. I hope this tutorial will help you with what you are looking for.

Enjoy Coding:)

For any questions about this article. You can contact me @
Email – [email protected]
Mobile No. – 09305235027 – TNT
Or feel free to comment below.

Tags

Comments

Submitted byhassane (not verified)on Mon, 12/24/2018 - 18:49

your site is very useful
Submitted byjanobeon Sat, 12/29/2018 - 01:46

Please visit the site everyday for more of my tutorials.
Submitted byjere (not verified)on Wed, 09/16/2020 - 20:43

thanks for the example
Submitted byFaHADDD (not verified)on Sat, 05/01/2021 - 17:23

i don't need the the label 'undefined' given in it. But i didn't found anywhere it is mentioned explicitely . How can i removeit?

IN JAVASCRIPT, SET DISPLAY TO FALSE: options: { legend: { display: false, position: 'bottom', labels: { fontColor: '#71748d', fontFamily: 'Circular Std Book', fontSize: 14, } }, }
Submitted byShantanu Bhusari (not verified)on Sat, 12/04/2021 - 21:41

Great Work!!!
Submitted byAnonymous (not verified)on Mon, 03/07/2022 - 19:02

IT WAS A SUCCESS! HOWEVER, AS I OPEN again after 2 weeks the graph didn't display anymore, no error is showing. can anyone help me how to fix this?
Submitted byAnonymous (not verified)on Thu, 11/17/2022 - 14:39

thank you
Submitted byJOSE VERA (not verified)on Sun, 09/03/2023 - 20:38

TO REMOVE THE 'UNDEFINED', SET DISPLAY OPTION IN JAVASCRIPT TO FALSE: options: { legend: { display: false, position: 'bottom', labels: { fontColor: '#71748d', fontFamily: 'Circular Std Book', fontSize: 14, } }, }
Submitted byDeveloper shona (not verified)on Sun, 09/17/2023 - 06:53

Thank you very much!!. But how do I create multiple graphs / charts only the first chart is displaying the correct details

Add new comment

About text formats