PHP-My SQL avg() aggregate function
Last Updated :
22 Mar, 2021
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 :
- Create table(food) in a database(geek) and insert records into it by writing PHP code(data1.php).
PHP Code Implementation :
PHP
<?php
$servername = "localhost" ;
$username = "root" ;
$password = "" ;
$dbname = "geek" ;
$conn = new mysqli( $servername , $username , $password , $dbname );
if ( $conn ->connect_error) {
die ( "Connection failed: " . $conn ->connect_error);
}
$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;
}
$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 = "localhost" ;
$username = "root" ;
$password = "" ;
$dbname = "geek" ;
$conn = new mysqli( $servername , $username , $password , $dbname );
$sql = "SELECT AVG(cost) FROM food" ;
$result = $conn ->query( $sql );
while ( $row = mysqli_fetch_array( $result )){
echo "Average items :" . $row [ 'AVG(cost)' ];
echo "<br />" ;
}
$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 = "localhost" ;
$username = "root" ;
$password = "" ;
$dbname = "geek" ;
$conn = new mysqli( $servername , $username , $password , $dbname );
$sql = "SELECT AVG(weight) FROM food" ;
$result = $conn ->query( $sql );
while ( $row = mysqli_fetch_array( $result )){
echo "Average items :" . $row [ 'AVG(weight)' ];
echo "<br />" ;
}
$conn ->close();
?>
</body>
</html>
|
Output :
Example-3 :
Code that combines all the average items cost and average item weight.
PHP
<html>
<body>
<?php
$servername = "localhost" ;
$username = "root" ;
$password = "" ;
$dbname = "geek" ;
$conn = new mysqli( $servername , $username , $password , $dbname );
$sql = "SELECT AVG(cost),AVG(weight) FROM food" ;
$result = $conn ->query( $sql );
while ( $row = mysqli_fetch_array( $result )){
echo "Average items cost :" . $row [ 'AVG(cost)' ];
echo "<br />" ;
echo "Average items weight :" . $row [ 'AVG(weight)' ];
}
$conn ->close();
?>
</body>
</html>
|
Output :
Share your thoughts in the comments
Please Login to comment...