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

June 1, 2020
PHP
average in column

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!

Download Code
Facebook Comments

Leave a Reply

Your email address will not be published.