Open In App

PHP-My SQL avg() aggregate function

Last Updated : 22 Mar, 2021
Improve
Improve
Like Article
Like
Save
Share
Report

In this article, we are going to find the average of the column in the SQL database using PHP in Xampp server. We are taking an example of a food database to find the average cost of all the items of food. Let’s discuss it one by one.

Requirements – Xampp server

Introduction :
Here, we will see the overview of the MySQL and AVG() function. 

  1. MySQL
    MySQL is a database query language used to manipulate databases.
     
  2. AVG()
    AVG stands for average which is used to find the average value of the specified expression, which accepts only one parameter namely expression.

Approach :
Here, we will discuss the approach to implement to find the average of the column in the SQL database using PHP in Xampp server as follows.

  • Create a table in a database.
  • Insert the records into the table using PHP.
  • PHP’s code to find an average of a particular column.

Steps to implement :

  • Starting xampp server.

  • Create table(food) in a database(geek) and insert records into it by writing PHP code(data1.php).

PHP Code Implementation :

PHP




<?php
//servername
$servername = "localhost";
//username
$username = "root";
//empty password
$password = "";
//geek is the database name
$dbname = "geek";
  
// Create connection by passing these connection parameters
$conn = new mysqli($servername, $username, $password, $dbname);
// Check this connection
if ($conn->connect_error) {
  die("Connection failed: " . $conn->connect_error);
}
//insert records into table
$sql  = "INSERT INTO food VALUES (1,'fry',345,20);";
$sql .= "INSERT INTO food VALUES (2,'cakes',1020,100);";
$sql .= "INSERT INTO food  VALUES (3,'chocos','200',10);";
$sql .= "INSERT INTO food  VALUES (4,'milk',200,50);";
  
if ($conn->multi_query($sql) === TRUE) {
  echo "food data stored successfully";
} else {
  echo "Error: " . $sql . "<br>" . $conn->error;
}
//close the connection
  
$conn->close();
?>


Type “localhost/data1.php” in your browser.

  • Type PHP code(form.php) to find the average cost of food items.

Example-1 :
PHP’s code to find the average weight of items as follows.

PHP




<html>
<body>
<?php
//servername
$servername = "localhost";
//username
$username = "root";
//empty password
$password = "";
//geek is the database name
$dbname = "geek";
  
// Create connection by passing these connection parameters
$conn = new mysqli($servername, $username, $password, $dbname);
  
//sql query to find average cost of food items in food table
$sql = "SELECT  AVG(cost) FROM food";
$result = $conn->query($sql);
//display data on web page
while($row = mysqli_fetch_array($result)){
    echo "Average items :". $row['AVG(cost)'];
      echo "<br />";
}
  
//close the connection
  
$conn->close();
?>
</body>
</html>


Type “localhost/form.php” in the browser.

Output :

Example-2 :
PHP’s code to find the average weight of items as follows.

PHP




<html>
<body>
<?php
//servername
$servername = "localhost";
//username
$username = "root";
//empty password
$password = "";
//geek is the database name
$dbname = "geek";
  
// Create connection by passing these connection parameters
$conn = new mysqli($servername, $username, $password, $dbname);
  
//sql query to find average weight  of food items in food table
$sql = "SELECT  AVG(weight) FROM food";
$result = $conn->query($sql);
//display data on web page
while($row = mysqli_fetch_array($result)){
    echo "Average items :". $row['AVG(weight)'];
      echo "<br />";
}
  
//close the connection
  
$conn->close();
?>
</body>
</html>


Output :

Example-3 :
Code that combines all the average items cost and average item weight.

PHP




<html>
<body>
<?php
//servername
$servername = "localhost";
//username
$username = "root";
//empty password
$password = "";
//geek is the database name
$dbname = "geek";
  
// Create connection by passing these connection parameters
$conn = new mysqli($servername, $username, $password, $dbname);
  
//sql query to find average
$sql = "SELECT  AVG(cost),AVG(weight) FROM food";
$result = $conn->query($sql);
//display data on web page
while($row = mysqli_fetch_array($result)){
    echo "Average items cost  :". $row['AVG(cost)'];
    echo "<br />";
    echo "Average items weight  :". $row['AVG(weight)'];
        
}
  
//close the connection
  
$conn->close();
?>
</body>
</html>


Output :



Like Article
Suggest improvement
Share your thoughts in the comments

Similar Reads