PHP-My SQL avg() aggregate function

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.

MySQL –
MySQL is a database query language used to manipulate databases.
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 :

Article Tags :

PHP

SQL

Web Technologies

PHP-MySQL