Facebook



How to Create a Doughnut Graph Using PHP/MySQLi

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. 

  2. 
`SalesId` int(11) NOT NULL,
    

    3. 

  3. 
`Product` varchar(90) NOT NULL,
    

    4. 

  4. 
`TotalSales` double NOT NULL
    

    5. 

  5. 
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
    

    6. 

  6. 

    

    7. 

  7. 
--
    

    8. 

  8. 
-- Dumping data for table `tblsales`
    

    9. 

  9. 
--
    

    10. 

  10. 

    

    11. 

  11. 
INSERT INTO `tblsales` (`SalesId`, `Product`, `TotalSales`) VALUES
    

    12. 

  12. 
(1,'Cellphone', 1400),
    

    13. 

  13. 
(2,'Laptop', 800),
    

    14. 

  14. 
(3,'Desktop', 5052),
    

    15. 

  15. 
(4,'Ipod', 8030),
    

    16. 

  16. 
(5,'Tablet', 10000);
    

    17.

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. 

  2. 
<html lang="en"> 
    

    3. 

  3. 
<head>
    

    4. 

  4. 
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
    

    5. 

  5. 
<meta name="viewport" content="width=device-width, initial-scale=1.0">
    

    6. 

  6. 
<title>Graph</title> 
    

    7. 

  7. 
</head>
    

    8. 

  8. 
<body>
    

    9. 

  9. 
<div style="width:40%;hieght:20%;text-align:center">
    

    10. 

  10. 
<h2 class="page-header" >Analytics Sales Report </h2>
    

    11. 

  11. 
<div>Gadgets</div>
    

    12. 

  12. 
<canvas  id="chartjs_doughnut"></canvas> 
    

    13. 

  13. 
</div>    
    

    14. 

  14. 
</body>
    

    15. 

  15. 
</html>
    

    16.

Step 3

Add the following extension to access Chart.js Libraries.
    

  1. 
<script src="//code.jquery.com/jquery-1.9.1.js">
    

    2.

  • <script src="//cdnjs.cloudflare.com/ajax/libs/Chart.js/2.4.0/Chart.min.js">

    • Step 4

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

    1. 

      

      2. 

    2. 
$con  = mysqli_connect("localhost","root","","salesdb");
      

      3. 

    3. 
if (!$con) {
      

      4. 

    4. 
# code...
      

      5. 

    5. 
echo "Problem in database connection! Contact administrator!" . mysqli_error();
      

      6. 

    6. 
}else{
      

      7. 

    7. 
$sql ="SELECT * FROM tblsales";
      

      8. 

    8. 
$result = mysqli_query($con,$sql);
      

      9. 

    9. 
$chart_data="";
      

      10. 

    10. 
while ($row = mysqli_fetch_array($result)) { 
      

      11. 

    11. 

      

      12. 

    12. 
$productname[]=$row['Product'];
      

      13. 

    13. 
$sales[] = $row['TotalSales'];
      

      14. 

    14. 
}
      

      15. 

    15. 

      

      16. 

    16. 

      

      17. 

    17. 
}
      

      18. 

    18. 
?>
      

      19.

    Step 5

    Create a JavaScript script for displaying a graph.
      

    1. 
<script type="text/javascript">
      

      2. 

    2. 
var ctx = document.getElementById("chartjs_doughnut").getContext('2d');
      

      3. 

    3. 
var myChart = new Chart(ctx, {
      

      4. 

    4. 
type: 'doughnut',
      

      5. 

    5. 
data: {
      

      6. 

    6. 
labels:php echo json_encode($productname); ?>,
      

      7. 

    7. 
datasets: [{
      

      8. 

    8. 
backgroundColor: [
      

      9. 

    9. 
"#5969ff",
      

      10. 

    10. 
"#ff407b",
      

      11. 

    11. 
"#25d5f2",
      

      12. 

    12. 
"#ffc750",
      

      13. 

    13. 
"#2ec551",
      

      14. 

    14. 
"#7040fa",
      

      15. 

    15. 
"#ff004e"
      

      16. 

    16. 
],
      

      17. 

    17. 
data:php echo json_encode($sales); ?>,
      

      18. 

    18. 
}]
      

      19. 

    19. 
},
      

      20. 

    20. 
options: {
      

      21. 

    21. 
legend: {
      

      22. 

    22. 
display: true,
      

      23. 

    23. 
position: 'bottom',
      

      24. 

    24. 

      

      25. 

    25. 
labels: {
      

      26. 

    26. 
fontColor: '#71748d',
      

      27. 

    27. 
fontFamily: 'Circular Std Book',
      

      28. 

    28. 
fontSize: 14,
      

      29. 

    29. 
}
      

      30. 

    30. 
},
      

      31. 

    31. 

      

      32. 

    32. 

      

      33. 

    33. 
}
      

      34. 

    34. 
});
      

      35. 

    35. 
</script>
      

      36.

    Full Source Code

      

    1. 

      

      2. 

    2. 
$con  = mysqli_connect("localhost","root","","salesdb");
      

      3. 

    3. 
if (!$con) {
      

      4. 

    4. 
# code...
      

      5. 

    5. 
echo "Problem in database connection! Contact administrator!" . mysqli_error();
      

      6. 

    6. 
}else{
      

      7. 

    7. 
$sql ="SELECT * FROM tblsales";
      

      8. 

    8. 
$result = mysqli_query($con,$sql);
      

      9. 

    9. 
$chart_data="";
      

      10. 

    10. 
while ($row = mysqli_fetch_array($result)) { 
      

      11. 

    11. 

      

      12. 

    12. 
$productname[]=$row['Product'];
      

      13. 

    13. 
$sales[] = $row['TotalSales'];
      

      14. 

    14. 
}
      

      15. 

    15. 

      

      16. 

    16. 

      

      17. 

    17. 
}
      

      18. 

    18. 
?>
      

      19. 

    19. 

      

      20. 

    20. 
 
      

      21. 

    21. 

      

      22. 

    22. 

      

      23. 

    23. 

      

      24. 

    24. 
Graph 
      

      25. 

    25. 

      

      26.

  • labels: echo json_encode($productname); ?>,
  • data: echo json_encode($sales); ?>,
    • For any questions about this article. You can contact me
    Previous Post Next Post