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 pagewhile($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 pagewhile($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 pagewhile($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:
