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 :