How to Get the Average of One Column in MySQL Database using PHP

Creating our Database

First, we are going to create our sample database. I've included a SQL file in the downloadable of this tutorial. All you have to do is import the said file. If you have no idea on how to import, please visit my tutorial How import .sql file to restore MySQL database. You should be able to create a database named dbtest.

Getting the Average

Finally, we get the average of our desired column. In this tutorial, we are going to display our table data for reference and we are going to get the average of "price" column. Create a new file, name it as index.php and paste the codes below.
  1. <?php
  2. //connection
  3. $conn = new mysqli('localhost', 'root', '', 'dbtest');
  4. ?>
  5. <!DOCTYPE html>
  6. <html>
  7. <head>
  8. <meta charset="utf-8">
  9. <title>How to Get the Average of One Column in MySQL Database using PHP</title>
  10. </head>
  11. <body>
  12.  
  13. <!-- displaying our table -->
  14. <table border="1">
  15. <thead>
  16. <th>ID</th>
  17. <th>Name</th>
  18. <th>Price</th>
  19. </thead>
  20. <tbody>
  21. <?php
  22.  
  23. $sql = "SELECT * FROM products";
  24. $query = $conn->query($sql);
  25.  
  26. while($row = $query->fetch_assoc()){
  27. echo "
  28. <tr>
  29. <td>".$row['id']."</td>
  30. <td>".$row['product_name']."</td>
  31. <td>".$row['product_price']."</td>
  32. </tr>
  33. ";
  34. }
  35. ?>
  36. </tbody>
  37. </table>
  38.  
  39. <!-- getting the average -->
  40. <?php
  41. $sql = "SELECT AVG(product_price) AS average_price FROM products";
  42. $query1 = $conn->query($sql);
  43. $row1 = $query1->fetch_assoc();
  44.  
  45. echo "Average Price: ".$row1['average_price'];
  46. ?>
  47. </body>
  48. </html>
That ends this tutorial. Happy Coding :)

Add new comment