Group by Clause in PHP

In this article I explain how to use a MySQL "group by" clause in PHP.
  • 2046

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

group-by-clause-in-php.jpg  

© 2020 DotNetHeaven. All rights reserved.