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.
- Start the xampp server
- 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 //servername $servername = "localhost" ;
//username $username = "root" ;
//empty password $password = "" ;
//sravan is the database name $dbname = "sravan" ;
// Create connection by passing these connection parameters $conn = new mysqli( $servername , $username , $password , $dbname );
// Check this connection if ( $conn ->connect_error) {
die ( "Connection failed: " . $conn ->connect_error);
} //insert records into table $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
<html> <body> <center> <?php //servername $servername = "localhost" ;
//username $username = "root" ;
//empty password $password = "" ;
//sravan is the database name $dbname = "sravan" ;
// Create connection by passing these connection parameters $conn = new mysqli( $servername , $username , $password , $dbname );
//sql query to find total strength with respect to the department $sql = "SELECT department, SUM(strength) FROM college_data GROUP BY department" ;
$result = $conn ->query( $sql );
//display data on web page while ( $row = mysqli_fetch_array( $result )){
echo "<h1>" ; echo "Total strength in " . $row [ 'department' ]. " = " . $row [ 'SUM(strength)' ]; echo "</h1>" ;
echo "<br />" ;
} //close the connection $conn ->close();
?> </center> </body> </html> |
Output :