Check Out The Latest Themes And Exclusive Deals And Offers On Themes
In Our Deals For Web Developers Section


Join Today And Get All The 87 Themes In Just $69! Join Today

Sort MySQL Table Using PHP

Friday, Jan 27, 2017

Tags:- PHP MySQL


Table Sorting is very important if you have some data and you want to display it in tabular form you may have to add table sorting functionality because it gives freedom to user to view data in different orders as per his need. So, in this tutorial we will show you how to sort mysql table using PHP.You may also like sort table using jQuery.


See Demo
Sort MySQL Table Using PHP


To Sort MySQL Table It Takes Only Two Steps:-

  1. Make a PHP file to sort mysql table
  2. Make a CSS file and define styling


Step 1.Make a PHP file to sort mysql table

We make a PHP file and save it with a name sort_table.php

// Database Structure 
CREATE TABLE 'employee' (
 'Name' text NOT NULL,
 'Age' int NOT NULL,
 'Salary' int NOT NULL,
) ENGINE=MyISAM AUTO_INCREMENT=5 DEFAULT CHARSET=latin1

<html>
<head>
<link href="sort_style.css" type="text/css" rel="stylesheet"/>
</head>
<body>
<div id="wrapper">
<div id="table_div">
<?php
$host="localhost";
$username="root";
$password="";
$databasename="sample";
$connect=mysql_connect($host,$username,$password);
$db=mysql_select_db($databasename);	 

$order="asc";
if($_GET['orderby']=="name" && $_GET['order']=="asc")
{
 $order="desc";
}
if($_GET['orderby']=="age" && $_GET['order']=="asc")
{
 $order="desc";
}
if($_GET['orderby']=="salary" && $_GET['order']=="asc")
{
 $order="desc";
}

if($_GET['orderby'])
{
 $orderby="order by ".$_GET['orderby'];
}
if($_GET['order'])
{
 $sort_order=$_GET['order'];
}
 
$get_result=mysql_query("select * from employee ".$orderby." ".$sort_order."");
echo "<table align=center border=1 cellpadding=10>";
echo "<tr>";
 echo "<th><a href='?orderby=name&order=".$order."'>Name</a></th>";
 echo "<th><a href='?orderby=age&order=".$order."'>Age</a></th>";
 echo "<th><a href='?orderby=salary&order=".$order."'>Salary</a></th>";
echo "</tr>";
while($row=mysql_fetch_array($get_result))
{
 echo "<tr>";
  echo "<td>".$row['Name']."</td>";
  echo "<td>".$row['Age']."</td>";
  echo "<td>".$row['Salary']."</td>";
 echo "</tr>";
}
echo "</table>";
?>
</div>

</div>
</body>
</html>

In this step we create a database 'employee' and store some sample rows in it for sorting. Then we create an HTML table and insert our database data in it and create three links to sort Name, Age and Salary in ascending and desceding orders.When user clicks on column header we get the value of orderby and order and sort the table according to the values



Step 2.Make a CSS file and define styling

We make a CSS file and save it with a name sort_style.css

body
{
 margin:0 auto;
 padding:0px;
 text-align:center;
 width:100%;
 font-family: "Myriad Pro","Helvetica Neue",Helvetica,Arial,Sans-Serif;
 background-color:#D0ECE7;
}
#wrapper
{
 margin:0 auto;
 padding:0px;
 text-align:center;
 width:995px;	
}
#wrapper h1
{
 margin-top:50px;
 font-size:45px;
 color:#117A65;
}
#wrapper h1 p
{
font-size:18px;
}
#table_div table
{
 border-collapse:collapse;
 text-align:center;
 border:grey;
}
#table_div table a
{
 color:#117A65;
 font-size:18px;
}
#table_div td
{
 width:100px;
 color:#212F3D;
}

That's all, this is how to sort mysql table using PHP.You can customize this code further as per your requirement. And please feel free to give comments on this tutorial.

Join Us With Our 21000+ Subscribers And Get Our Latest Tutorials Update Via Email