✖

All TalkersCode Topics

Follow TalkersCode On Social Media

Sort MySQL Table Using PHP

Last Updated : Apr 15, 2022

IN - 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.

Sort MySQL Table Using PHP

CHECK OUT THIS TUTORIAL LIVE DEMO →

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.

CHECK OUT THIS TUTORIAL LIVE DEMO →

Latest Tutorials