Creating a 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.
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 percentage of the “price” column.
Create a new file, name it as index.php and paste the codes below.
<?php
//connection
$conn = new mysqli('localhost', 'root', '', 'dbtest');
?>
<!DOCTYPE html>
<html>
<head>
<meta charset="utf-8">
<title>How to Get the Average of One Column in MySQL Database using PHP</title>
</head>
<body>
<!-- displaying our table -->
<table border="1">
<thead>
<th>ID</th>
<th>Name</th>
<th>Price</th>
</thead>
<tbody>
<?php
$sql = "SELECT * FROM products";
$query = $conn->query($sql);
while($row = $query->fetch_assoc()){
echo "
<tr>
<td>".$row['id']."</td>
<td>".$row['product_name']."</td>
<td>".$row['product_price']."</td>
</tr>
";
}
?>
</tbody>
</table>
<!-- getting the average -->
<?php
$sql = "SELECT AVG(product_price) AS average_price FROM products";
$query1 = $conn->query($sql);
$row1 = $query1->fetch_assoc();
echo "Average Price: ".$row1['average_price'];
?>
</body>
</html>
That ends this tutorial. Happy Coding!
