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 :

Start the xampp server

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

PHP code (form.php)

After typing this code run it in tour web browser by typing “localhost/form.php”

PHP

<?php
    // code
?><html>
<body>
<?php
//servername
$servername = "localhost";
//username
$username = "root";
//empty password
$password = "";
//database is the database name
$dbname = "geek";
 
// Create connection by passing these connection parameters
$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 query
$sql = "SELECT  food_item from food GROUP BY(food_item) HAVING SUM(cost)>200";
$result = $conn->query($sql);
//display data on web page
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 query
$sql = "SELECT  food_item from food GROUP BY(food_item) HAVING SUM(weight)>100";
$result = $conn->query($sql);
//display data on web page
while($row = mysqli_fetch_array($result)){
    echo " Item : ". $row['food_item'];
    echo "<br>";
     
}
//close the connection
 
$conn->close();
?>
</body>
</html>

Output:

Example-2:

PHP code (form1.php)

Display food items with average cost greater than 400

PHP

<html>
<body>
<?php
//servername
$servername = "localhost";
//username
$username = "root";
//empty password
$password = "";
//database is the database name
$dbname = "geek";
 
// Create connection by passing these connection parameters
$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 query
$sql = "SELECT  food_item,food_id from food GROUP BY(food_item) HAVING AVG(cost)>400";
$result = $conn->query($sql);
//display data on web page
while($row = mysqli_fetch_array($result)){
    echo " Item : ". $row['food_item'], " ----   Item id : ". $row['food_id'];
    echo "<br>";
     
}
 
//close the connection
 
$conn->close();
?>
</body>
</html>