Open In App

PHP – MYSQL Group By Clause

Improve
Improve
Like Article
Like
Save
Share
Report

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 :

  1. 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.
     
  2. 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 :

  1. In the SELECT statement query, the GROUP BY clause is used with the SELECT statement.
  2. In the query, the GROUP BY clause is placed after the WHERE clause.
  3. 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




<?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

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 :



Last Updated : 27 Dec, 2021
Like Article
Save Article
Previous
Next
Share your thoughts in the comments
Similar Reads