How to Create a Line Graph in PHP/MySQLi

Line graph is one of the most commonly used chart types and is very useful especially in the fields of statistics. It is composed of vertical y-axis and a horizontal x-axis which displays series information of data points connected by lines. Each of this axes is named with a data type, this will help you in monitoring your sales and determine the result accurately. Take a look at the procedure below on how to make Line graph program.

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. `TRANSDATE` date NOT NULL,
  4. `Product` varchar(90) NOT NULL,
  5. `TotalSales` double NOT NULL
  6.  
  7. --
  8. -- Dumping data for table `tblsales`
  9. --
  10.  
  11. INSERT INTO `tblsales` (`SalesId`, `TRANSDATE`, `Product`, `TotalSales`) VALUES
  12. (1, '2018-01-30', 'Surf Powder', 1400),
  13. (2, '2018-02-28', 'Surf Powder', 800),
  14. (3, '2018-03-31', 'Surf Powder', 5052),
  15. (4, '2019-04-30', 'Surf Powder', 8030),
  16. (5, '2019-05-31', 'Surf Powder', 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:50%;hieght:20%;text-align:center">
  10. <h2 class="page-header" >Analytics Sales Report </h2>
  11. <div><?php echo $productname; ?> </div>
  12. <canvas id="chartjs_line"></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. $month[] = date_format(date_create( $row['TRANSDATE']),"M d, Y") ;
  14. $sales[] = $row['TotalSales'];
  15. }
  16.  
  17.  
  18. }
  19. ?>

Step 5

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

Comments

Submitted byneomeris (not verified)on Tue, 04/21/2020 - 21:24

nice
Submitted byAnonymous (not verified)on Mon, 05/23/2022 - 13:17

good
Submitted byAnonymous (not verified)on Mon, 05/29/2023 - 13:35

good
Submitted byesraafadel (not verified)on Tue, 06/27/2023 - 21:15

very good as anotice if you want to not apper any color in dody charts use this code labels:, datasets: [{ backgroundColor: [ " #FFFFFF00", // "blue", // "yellow", // "pink", // "green", // "silver" ], borderColor: [ " red", // "blue", // "yellow", // "pink", // "green", // "silver" ],

Add new comment