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 :
