This tutorial is about how to create a Pie Graph using PHP/MySQLi. In here, you will be able to learn the simple way of creating a pie graph by fetching the data inside the database. This pie graph is usually use in Analytics sales report to determine what kind of products have the most number of purchase. Whoever has the biggest slice in the pie graph it is the product that has the most number purchased by the customers. Follow the instructions below to see how it works.

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
   
   
   
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
    
    ;
    
36. COMMIT
    
    ;
    

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:30%;hieght:20%;text-align:center"
   
   >
   
10. <
    
    h2 class
    
    =
    
    "page-header"
    
    >
    
    Analytics Sales Report </
    
    h2>
    
11. <
    
    div>
    
    Product </
    
    div>
    
12. <
    
    canvas id=
    
    "chartjs_pie"
    
    ></
    
    canvas>
    
13. </
    
    div>
    
14. </
    
    body>
    
15. </
    
    html>
    

Step 3

Add the following extension to access Chart.js

Libraries.

1. 
   
2. <
   
   script src=
   
   "//code.jquery.com/jquery-1.9.1.js"
   
   >
   
   </script>
   
3. <
   
   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_pie"
   
   )
   
   .getContext
   
   (
   
   '2d'
   
   )
   
   ;
   
3. var
   
   myChart =
   
   new
   
   Chart(
   
   ctx,
   
   {
   
4. type:
   
   'pie'
   
   ,
   
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:30%;hieght:20%;text-align:center">
    
30. <h2 class="page-header" >Analytics Reports </h2>
    
31. <div>Product </div>
    
32. <canvas id="chartjs_pie"></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_pie").getContext('2d');
    
39. var myChart = new Chart(ctx, {
    
40. type: 'pie',
    
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>
    

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