PHP – Mysql GROUP BY HAVING Clause
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.
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.
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.
SELECT column1,column2,columnn FROM table_name GROUP BY column_name HAVING aggregate_function(column_name) condition;
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;
Item : cakes Item : chocoss Item : fry Item : milk
Food items with weight less than 100
SELECT food_item from food GROUP BY(food_item) HAVING SUM(weight)>100;
Item : cakes
- create database in xampp
- create table an insert records into it.
- write php code to perform group by having clause.
- 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 :
- Your table will look like:
- PHP code (form.php)
After typing this code run it in tour web browser by typing “localhost/form.php”
- PHP code (form1.php)
Display food items with average cost greater than 400