Group by Clause in PHP
In this article I explain how to use a MySQL "group by" clause in PHP.
Introduction
I have described in my previous article how aggregate functions work. Basically, aggregate functions provide all the data in a single result set. The aggregate functions group data together. To do that you can use two new clauses of the select statement, "group by" and "having" clauses.
Group by clause
The "group" clause is used to group the rows of a result set based on one or more column expressions.
Group by clause determines how the selected rows are grouped. The group by clause follows the where clause.
If you want, more than one column or expression will be included in the "group by" clause, separated by commas.
Syntax
SELECT columns Name....... from table Name
Where condition
Group by group_by_list
Having condition
Order by order_by_list |
Example of "Group by" clause in PHP
<?php
$con=mysql_connect("localhost","root","");
if (!$con)
{
die('Could not connect: ' . mysql_error());
}
mysql_select_db("mysql", $con);
print "<h2>MySQL: Simple select statement</h2>";
$result = mysql_query("select * from emp_dtl"); // First query
echo "<table border='1'>
<tr>
<th>EmpId</th>
<th>Firstname</th>
<th>Lastname</th>
<th>Role</th>
<th>Salary</th>
</tr>";
while($row = mysql_fetch_array($result))
{
echo "<tr>";
echo "<td>" . $row['id'] . "</td>";
echo "<td>" . $row['Firstname'] . "</td>";
echo "<td>" . $row['Lastname'] . "</td>";
echo "<td>" . $row['role'] . "</td>";
echo "<td>" . $row['salary'] . "</td>";
echo "</tr>";
}
echo "</table>";
//Group by in PHP
print "<h2>MySQL: Group by clause in PHP</h2>";
$result = mysql_query("select id, avg(salary)as totalsal from emp_dtl group by id"); // Second query
echo "<table border='1'>
<tr>
<th>EmpId</th>
<th>Salary</th>
</tr>";
while($row = mysql_fetch_array($result))
{
echo "<tr>";
echo "<td>" . $row['id'] . "</td>";
echo "<td>" . $row['totalsal'] . "</td>";
echo "</tr>";
}
echo "</table>";
print "<h2>MySQL: Group by with having clause</h2>";
$result = mysql_query("select id, count(*) as total from emp_dtl group by id having count(*)>1"); // Third query
echo "<table border='1'>
<tr>
<th>EmpId</th>
<th>Duplicate Records</th>;
</tr>";
while($row = mysql_fetch_array($result))
{
echo "<tr>";
echo "<td>" . $row['id'] . "</td>";
echo "<td>" . $row['total'] . "</td>";
echo "</tr>";
}
mysql_close($con);
?>
echo "</table>";
Output