PHP Mysql Group By
PHP Mysql Group By is used to establish a relation between the PHP on the
server side and Mysql database on the backend.
Understand with Example
The Tutorial illustrate an example from PHP Mysql Group By. To understand and
grasp the example we create a table 'Stu' that specifies the required fieldnames
and datatypes respectively.
Create table Stu:
CREATE TABLE `stu` (
`Id` int(11) default NULL,
`Name` varbinary(15) default NULL,
`Marks` int(11) default NULL
);
|
Insert data into Stu:
The insert into keyword is used to add the records or rows to the
table 'Stu'.
Insert Into Stu values(1,'Ajay',89);
Insert Into Stu values(2,'Bhanu',67);
Insert Into Stu values(4,'Rakesh',67);
Insert Into Stu values(5,'Santosh',90);
Insert Into Stu values(3,'Komal',78);
Insert Into Stu values(1,'Ajay',45);
Insert Into Stu values(2,'Bhanu',67);
Insert Into Stu values(4,'Rakesh',87);
Insert Into Stu values(5,'Santosh',40);
Insert Into Stu values(3,'Komal',71);
|
PHP_SQL_group_by.php:
The PHP_SQL_group by code include the php server side scripting that
uses host name, user, password and database to return the maximum marks, Id and
name records to group the result set by column id from table 'Stu' in your
browser.
<html>
<body>
<?php
$host = "localhost";
$user = "root";
$password = "root";
$database = "komal";
$connection = mysql_connect($host,$user,$password)
or die("Could not connect: ".mysql_error());
mysql_select_db($database,$connection)
or die("Error in selecting the database:".mysql_error());
$sql="select Id, Name ,max(Marks) from stu group by id";
$sql_result=mysql_query($sql,$connection)
or exit("Sql Error".mysql_error());
$sql_num=mysql_num_rows($sql_result);
echo "<table bgcolor=\"#F5F5FF\">";
echo "<tr>";
echo "<td ><b>Id</b></td> <td><b>Name</b></td>
<td><b>Max(Marks)</b></td>";
echo "</tr>";
while($sql_row=mysql_fetch_array($sql_result))
{
$id=$sql_row["Id"];
$name=$sql_row["Name"];
$marks=$sql_row["max(Marks)"];
echo "<tr><td>".$id."</td>";
echo "<td>".$name."</td>";
echo "<td>".$marks."</td></tr>";
}
echo "</table>"
?>
</body>
</html>
|
Result
| Id | Name |
Max(Marks) | | 1 | Ajay | 89 | | 2 | Bhanu | 67 | | 3 | Komal | 78 | | 4 | Rakesh | 87 | | 5 | Santosh | 90 |
|
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
