Column Sorting with php and mysql

by  
Share:
Column Sorting with php and mysql

Column sorting is a feature where user can sort the results either in ascending or descending order. Although it sounds a simple hack in your code, but for end-users this feature is a gem when they go through large reports. In this tutorial I will explain how to make a Column Sorting feature with php and mysql.

View Demo

Setup your database and table in MySQL

Before you run this script make sure you have created a database and table in MySQL. Below is the sample sql command to create a database, table, and insert some dummy rows.

CREATE DATABASE shahrukh;

CREATE TABLE IF NOT EXISTS `tbl_players` (
  `p_id` int(11) NOT NULL AUTO_INCREMENT,
  `p_name` varchar(255) CHARACTER SET utf8 NOT NULL,
  `p_age` int(11) NOT NULL,
  `p_country` varchar(255) CHARACTER SET utf8 NOT NULL,
  `p_club` varchar(255) CHARACTER SET utf8 NOT NULL,
  PRIMARY KEY (`p_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=14 ;

INSERT INTO `tbl_players` (`p_id`, `p_name`, `p_age`, `p_country`, `p_club`) VALUES
(1, 'Robin van Persie', 34, 'Netherlands', 'Manchester United F.C.'),
(2, 'Cesc Fàbregas', 27, 'Spain', 'FC Barcelona'),
(3, 'Cristiano Ronaldo', 29, 'Portugal', 'Real Madrid C.F'),
(4, 'Wayne Rooney', 28, 'United Kingdom', 'Manchester United F.C.'),
(5, 'Mesut Özil', 25, 'Germany', 'Arsenal F.C'),
(6, 'Sergio Ramos', 28, 'Spain', 'Real Madrid C.F.'),
(7, 'Lionel Messi', 26, 'Argentina', 'FC Barcelona'),
(8, 'Andrés Iniesta', 29, 'Spain', 'FC Barcelona'),
(9, 'Fernando Torres', 30, 'Spain', 'Chelsea F.C'),
(10, 'Luis Suárez', 27, 'Uruguay', 'Liverpool F.C.'),
(11, 'Marouane Fellaini', 26, 'Belgium', 'Manchester United F.C.'),
(12, 'Steven Gerrard', 33, 'United Kingdom', 'Liverpool F.C.'),
(13, 'David Beckham', 39, 'United Kingdom', 'Paris Saint-Germain');

Fetch records from table and display

Now the next step is simple, fetch all records from MySQL table and display.

<?php
$sql = "SELECT `p_id`, `p_name`, `p_age`, `p_country`, `p_club` FROM `tbl_players` WHERE 1";
?>
<table>
<tr>
  <th>ID</th>
  <th>Name</th>
  <th>Age</th>
  <th>Country</th>
  <th>Club</th>
</tr>
<?php 
$query = mysql_query($sql);
while($rs = mysql_fetch_array($query) ) {
?>
<tr>
  <td><?php echo $rs["p_id"] ?></td>
  <td><?php echo $rs["p_name"]; ?></td>
  <td><?php echo $rs["p_age"] ?></td>
  <td><?php echo $rs["p_country"]; ?></td>
  <td><?php echo $rs["p_club"]; ?></td>
</tr>
<?php } ?>
</table>

Adding column Sorting Code

It’s time for the main event, adding the main logic for column sorting. All you have to do is to add these three snippet given below. Firstly, bind the column header with anchor tag, on which user will click to sort the field.

<!-- Replace p_age with your database table field name -->
<th>
<a title="Click to sort by Age" href="index.php?sort_element=p_age&sort_type=<?php echo ($_REQUEST["sort_element"] == "p_age"  && $_REQUEST["sort_type"] == "asc") ? "desc" : "asc"; ?>">Age
        <?php if ($_REQUEST["sort_element"] == "p_age" ) {  if($_REQUEST["sort_type"] == "desc" ) { ?>
        <img class="sorting" src="arrowtop.png" alt="asc">
        <?php } else { ?>
          <img class="sorting" src="arrowbottom.png" alt="desc"> 
        <?php } } ?>
</a>
</th>

Secondly, add this code at the top of your page.
$sortingCode = "";
if (isset($_REQUEST['sort_element']) && $_REQUEST['sort_element'] != "") {
    $sort_element = " ORDER BY ".$_REQUEST['sort_element']." ";
} 
if (isset($_REQUEST['sort_type']) && $_REQUEST['sort_type'] != "") {
    $sort_type = " ".$_REQUEST['sort_type']." ";
} 
$sortingCode = "$sort_element $sort_type";

Thirdly, add the sorting variable to your main $sql variable.
<?php
$sql = "SELECT `p_id`, `p_name`, `p_age`, `p_country`, `p_club` FROM `tbl_players` WHERE 1 $sortingCode";
?>

View Demo
Share to download the Source Codes for FREE!
We're glad to give free downloads, but we need your love to carry on making that.
Please support us by sharing the page.

Get the latest updates directly in your inbox for FREE: Subscribe here

Share your feedback / let me know your doubts regarding this tutorial in the comment box given below.

Share:
Hasan

Shahrukh Khan (Hasan)

A software engineer who's a die-hard coder, blogger, dreamer and mentor with years of expertise in web development. Know more...

Related Posts

Maxi Capodacqua on

Why dont you use PDO instead of mysql functions?
As well, should scape the strings from the request.

Reply
Shahrukh Khan on

I do use pdo for my personal development..but when i write tutorial for specific task i make it simple for every one understand ( so that non-pdo user get it.). BTW thanks for ur comment will post a tutorial on PDO soon.

Reply
afzal on

undefine index:sort_type…please resolve this error

Reply
Shahrukh Khan on

its not an error its a notice, see if you set a variable without defining it.

Reply
Husam on

can you help me in simple issue ?

Reply

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.