PHP – MYSQL Group By Clause
In this article, we are going to connect PHP code to the database to perform aggregate operations along with the GROUP BY Clause. Here, in this article, we are going to sum the college strength with respect to the department of the college and display it on the web page. Let’s discuss it one by one.
Requirements – xampp server
Overview :
- PHP –
PHP stands for hyper text preprocessor. It is used to create dynamic web pages and can connect with the MySQL database by using the Xampp server.
- MySQL –
MySQL is a query language that is used to manage databases. The GROUP BY statement is used to arrange the data into groups by using aggregate operations.
Note :
- In the SELECT statement query, the GROUP BY clause is used with the SELECT statement.
- In the query, the GROUP BY clause is placed after the WHERE clause.
- GROUP BY will come before and will be placed before the ORDER BY clause if used any.
Aggregate operations :
Aggregate operations include sum(), min(), max(), count() etc.
Syntax :
SELECT column1,column2,.....columnn, function_name(columnn)
FROM table_data
WHERE condition
GROUP BY column1, column2;
Approach :
- Create a database in xampp.
- Create a table in a database
- Insert the records into it by using PHP code.
- PHP’s script to get desired data from a table using group by clause
Steps to implement :
Here, we will implement step by step to perform aggregate operations along with the GROUP BY Clause. Let’s have a look.
- Create a database named sravan and create a table named college_data with 4 columns.
- Open notepad and write the code to insert the records,
- Save the file under xampp folder named data1.php
PHP Code implementation :
Code shows inserting college details in the college database.
PHP
<?php
$servername = "localhost" ;
$username = "root" ;
$password = "" ;
$dbname = "sravan" ;
$conn = new mysqli( $servername , $username , $password , $dbname );
if ( $conn ->connect_error) {
die ( "Connection failed: " . $conn ->connect_error);
}
$sql = "INSERT INTO college_data VALUES (1,'vignan','IT',120);" ;
$sql .= "INSERT INTO college_data VALUES (1,'vignan','BT',190);" ;
$sql .= "INSERT INTO college_data VALUES (1,'vignan','Mech',120);" ;
$sql .= "INSERT INTO college_data VALUES (2,'vvit','IT',220);" ;
if ( $conn ->multi_query( $sql ) === TRUE) {
echo "data stored successfully" ;
} else {
echo "Error: " . $sql . "<br>" . $conn ->error;
}
$conn ->close();
?>
|
Run the file in the browser by typing localhost/data1.php
Output :
Table data –
college_id |
college_name |
department |
strength |
1 |
vignan |
IT |
120 |
1 |
vignan |
BT |
190 |
1 |
vignan |
Mech |
120 |
2 |
vvit |
IT |
220 |
Querying through PHP code :
Now our table contains data.
- Write PHP code to find the sum of the strength of the department using group by clause.
- Save the file as form.php
PHP
<html>
<body>
<center>
<?php
$servername = "localhost" ;
$username = "root" ;
$password = "" ;
$dbname = "sravan" ;
$conn = new mysqli( $servername , $username , $password , $dbname );
$sql = "SELECT department, SUM(strength) FROM college_data GROUP BY department" ;
$result = $conn ->query( $sql );
while ( $row = mysqli_fetch_array( $result )){
echo "<h1>" ; echo "Total strength in " . $row [ 'department' ]. " = " . $row [ 'SUM(strength)' ]; echo "</h1>" ;
echo "<br />" ;
}
$conn ->close();
?>
</center>
</body>
</html>
|
Output :
Last Updated :
27 Dec, 2021
Like Article
Save Article
Share your thoughts in the comments
Please Login to comment...