PHP – Mysql GROUP BY HAVING Clause
Last Updated :
29 Jun, 2022
Problem Statement :
In this article, we are going to perform database operations with GROUP BY HAVING operation through PHP through xampp server.
In this scenario, we are going to consider the food database.
Requirements :
xampp server
Introduction :
PHP is a server side scripting language that can communicate with Mysql web server through xampp tool.
MySQL is a query language that can communicate with php through xampp.
- GROUP BY Clause –
The GROUP BY Statement in database is an SQL which used to arrange identical data into groups by using aggregate operations like SUM(), MIN(), MAX() etc.
Syntax –
SELECT column1,column2,columnn, function_name(column2)
FROM table_name
WHERE condition
GROUP BY column1, column2,columnn;
- GROUP BY HAVING Clause –
Having Clause is just the aggregate function used with the GROUP BY clause. The HAVING clause is used instead of WHERE with aggregate functions. While the GROUP BY Clause groups rows that have the same values into summary rows. The having clause is used with the where clause in order to find rows with certain conditions. The having clause is always used after the group By clause.
Syntax –
SELECT column1,column2,columnn
FROM table_name
GROUP BY column_name
HAVING aggregate_function(column_name) condition;
Example Query:
Consider the food database:
Select food items with cost greater than 200
SELECT food_item
from food
GROUP BY(food_item)
HAVING SUM(cost) > 200;
Result:
Item : cakes
Item : chocoss
Item : fry
Item : milk
Query:
Food items with weight less than 100
SELECT food_item
from food
GROUP BY(food_item)
HAVING SUM(weight)>100;
Result:
Item : cakes
Approach:
- create database in xampp
- create table an insert records into it.
- write php code to perform group by having clause.
Steps :
- Create database named geek
- Create a table named food and insert records into it.
Refer this for insert records into xampp :
https://www.geeksforgeeks.org/performing-database-operations-in-xampp/
- Your table will look like:
table columns-structure
After typing this code run it in tour web browser by typing “localhost/form.php”
PHP
<?php
?><html>
<body>
<?php
$servername = "localhost" ;
$username = "root" ;
$password = "" ;
$dbname = "geek" ;
$conn = new mysqli( $servername , $username , $password , $dbname );
echo "<h1>" ; echo "GROUP BY HAVING Demo " ; echo "</h1>" ;
echo "<br>" ;
echo "food items with cost greater than 200" ;
echo "<br>" ;
echo "<br>" ;
$sql = "SELECT food_item from food GROUP BY(food_item) HAVING SUM(cost)>200" ;
$result = $conn ->query( $sql );
while ( $row = mysqli_fetch_array( $result )){
echo " Item : " . $row [ 'food_item' ];
echo "<br>" ;
}
echo "<br>" ;
echo "food items with weight less than 100" ;
echo "<br>" ;
echo "<br>" ;
$sql = "SELECT food_item from food GROUP BY(food_item) HAVING SUM(weight)>100" ;
$result = $conn ->query( $sql );
while ( $row = mysqli_fetch_array( $result )){
echo " Item : " . $row [ 'food_item' ];
echo "<br>" ;
}
$conn ->close();
?>
</body>
</html>
|
Output:
Example-2:
Display food items with average cost greater than 400
PHP
<html>
<body>
<?php
$servername = "localhost" ;
$username = "root" ;
$password = "" ;
$dbname = "geek" ;
$conn = new mysqli( $servername , $username , $password , $dbname );
echo "<h1>" ; echo "GROUP BY HAVING Demo " ; echo "</h1>" ;
echo "<br>" ;
echo "food items with average cost greater than 400" ;
echo "<br>" ;
echo "<br>" ;
$sql = "SELECT food_item,food_id from food GROUP BY(food_item) HAVING AVG(cost)>400" ;
$result = $conn ->query( $sql );
while ( $row = mysqli_fetch_array( $result )){
echo " Item : " . $row [ 'food_item' ], " ---- Item id : " . $row [ 'food_id' ];
echo "<br>" ;
}
$conn ->close();
?>
</body>
</html>
|
Output:
Like Article
Suggest improvement
Share your thoughts in the comments
Please Login to comment...