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
// 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:
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>
Output:
Similar Reads
PHP | MySQL LIMIT Clause
In MySQL the LIMIT clause is used with the SELECT statement to restrict the number of rows in the result set. The Limit Clause accepts one or two arguments which are offset and count.The value of both the parameters can be zero or positive integers. Offset:It is used to specify the offset of the fir
3 min read
PHP | MySQL ORDER BY Clause
The ORDER BY Clause can be used along with the SELECT statement to sort the data of specific fields in an ordered way. It is used to sort the result-set in ascending or descending order. Syntax : The basic syntax of the Order By clause is - Implementation of the Order By Clause : Let us consider the
3 min read
PHP | MySQL ( Creating Table )
What is a table? In relational databases, and flat file databases, a table is a set of data elements using a model of vertical columns and horizontal rows, the cell being the unit where a row and column intersect. A table has a specified number of columns, but can have any number of rows. Creating a
3 min read
PHP - Mysql Joins
In this article, we are going to join two tables using PHP and display them on the web page. Introduction : PHP is a server-side scripting language, which is used to connect with databases. Using this, we can get data from the database using PHP scripts. The database language that can be used to com
9 min read
PHP | mysqli_fetch_array() Function
The mysqli_fetch_array() function is used to fetch rows from the database and store them as an array. The array can be fetched as an associative array, as a numeric array or both. Associative arrays are the arrays where the indexes are the names of the individual columns of the table. On the other h
2 min read
PHP filegroup() Function
The filegroup() is an inbuilt function in PHP that returns the filegroup. This function returns a group id that can resolve using posix_getgrigid() to a name. Syntax: filegroup(string $filename): int|falseParameter: This function has only one parameter. filename: A name of the file or path of the fi
1 min read
PHP - MySQL min(),max() aggregate operations
In this article, we are going to find minimum and maximum details from the table column using PHP from MySQL Xampp server. We are taking an example from employee database to find the minimum and maximum salary of the employee. Requirements -Xampp server Introduction : PHP - It stands for Hyper Text
4 min read
How to count rows in MySQL table in PHP ?
PHP stands for hypertext preprocessor. MySQL is a database query language used to manage databases. In this article, we are going to discuss how to get the count of rows in a particular table present in the database using PHP and MySQL. Requirements: XAMPP Approach: By using PHP and MySQL, one can p
3 min read
PHP-My SQL avg() aggregate function
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 o
4 min read
How to retrieve data from MySQL database using PHP ?
There are steps to understand for retrieving the data from the MySQL database. Approach: Create the database, then create the table for data.Enter the rows in the table. You have to connect to the database. Now we understand each and every step as shown below.  Example 1: In this. we use PHPMyAdmin
2 min read