How to Create a Doughnut Graph Using PHP/MySQLi

Submitted by janobe on Wednesday, December 26, 2018 - 22:52.
If you want to be creative in creating statistics sales report you can try this simple program that I’m going to share to you. This time, I will teach you how to create your own custom doughnut graph using Chart.js and PHP/MySQLi. This kind of graph demonstrates the products sale in different slices depending on the sales of the products. It also automatically set a colour to easily identify each products. Let’s begin.

Creating Database

Create a database named “salesdb”. Execute the following query for adding 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
  6.  
  7. --
  8. -- Dumping data for table `tblsales`
  9. --
  10.  
  11. INSERT INTO `tblsales` (`SalesId`, `Product`, `TotalSales`) VALUES
  12. (1,'Cellphone', 1400),
  13. (2,'Laptop', 800),
  14. (3,'Desktop', 5052),
  15. (4,'Ipod', 8030),
  16. (5,'Tablet', 10000);

Creating HTML and PHP Script

Step 1

Create and 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:40%;hieght:20%;text-align:center">
  10.             <h2 class="page-header" >Analytics Sales Report </h2>
  11.             <div>Gadgets</div>
  12.             <canvas  id="chartjs_doughnut"></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. ?>

Step 5

Create a JavaScript script for displaying a graph.
  1. <script type="text/javascript">
  2.       var ctx = document.getElementById("chartjs_doughnut").getContext('2d');
  3.                 var myChart = new Chart(ctx, {
  4.                     type: 'doughnut',
  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. <!DOCTYPE html>
  20. <html lang="en">
  21.     <head>
  22.         <meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
  23.         <meta name="viewport" content="width=device-width, initial-scale=1.0">
  24.         <title>Graph</title>
  25.     </head>
  26.     <body>
  27.         <div style="width:40%;hieght:20%;text-align:center">
  28.             <h2 class="page-header" >Analytics Sales Report </h2>
  29.             <div>Gadgets</div>
  30.             <canvas  id="chartjs_doughnut"></canvas>
  31.         </div>    
  32.     </body>
  33.   <script src="//code.jquery.com/jquery-1.9.1.js"></script>
  34.   <script src="//cdnjs.cloudflare.com/ajax/libs/Chart.js/2.4.0/Chart.min.js"></script>
  35.     <script type="text/javascript">
  36.       var ctx = document.getElementById("chartjs_doughnut").getContext('2d');
  37.                 var myChart = new Chart(ctx, {
  38.                     type: 'doughnut',
  39.                     data: {
  40.                         labels:<?php echo json_encode($productname); ?>,
  41.                         datasets: [{
  42.                             backgroundColor: [
  43.                                "#5969ff",
  44.                                 "#ff407b",
  45.                                 "#25d5f2",
  46.                                 "#ffc750",
  47.                                 "#2ec551",
  48.                                 "#7040fa",
  49.                                 "#ff004e"
  50.                             ],
  51.                             data:<?php echo json_encode($sales); ?>,
  52.                         }]
  53.                     },
  54.                     options: {
  55.                            legend: {
  56.                         display: true,
  57.                         position: 'bottom',
  58.  
  59.                         labels: {
  60.                             fontColor: '#71748d',
  61.                             fontFamily: 'Circular Std Book',
  62.                             fontSize: 14,
  63.                         }
  64.                     },
  65.  
  66.                    
  67.                 }
  68.                 });
  69.     </script>
  70. </html>
For any questions about this article. You can contact me @ Email – [email protected] Mobile No. – 09305235027 – TNT Or feel free to comment below.
Tags
Doughnut Graph
chart.js
php
MYSQL
MySQLi
Statistic Reports

Add new comment

About text formats
  • 658 views