How to Create a Line Graph in PHP/MySQLi

Submitted by: 
Language: 
Visitors have accessed this post 442 times.

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.


Add new comment

Filtered HTML

  • Web page addresses and e-mail addresses turn into links automatically.
  • You may insert videos with [video:URL]
  • Allowed HTML tags: <a> <em> <strong> <cite> <blockquote> <code> <ul> <ol> <li> <dl> <dt> <dd> <table> <tr> <td> <th> <img> <h1> <h2> <h3> <iframe> [video]
  • You can enable syntax highlighting of source code with the following tags: <code>, <blockcode>, <asp>, <c>, <cpp>, <csharp>, <css>, <html4strict>, <java>, <javascript>, <mysql>, <php>, <python>, <sql>, <vb>, <vbnet>. The supported tag styles are: <foo>, [foo].
  • Lines and paragraphs break automatically.

Plain text

  • No HTML tags allowed.
  • Lines and paragraphs break automatically.
CAPTCHA
This question is for testing whether or not you are a human visitor and to prevent automated spam submissions.