Simple Table sorting in jquery with mysql data

Nowadays, We showed our data in Frid view web content for the client. so for every content we need to use Sorting for Client and also our benefits, But Sorting, searching, pagination is not a easy job in HTML tables with Php and mysql data. So many grid view framework out there, Here we Learn about table sorting in jquery.
DataTable.js is the most popular nowadays. Using jquery and mysql structure we are going to create very simple and easy table sorting functions.

Column sorting is a feature where user can sort the results either in ascending or descending order.

Demo Download

Step1 : 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 TABLE IF NOT EXISTS `exam` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `student` varchar(55) DEFAULT NULL,
  `subject` varchar(255) DEFAULT NULL,
  `mark` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=17 ;

--
-- Dumping data for table `exam`
--

INSERT INTO `exam` (`id`, `student`, `subject`, `mark`) VALUES
(1, 'John', 'English', 75),
(2, 'John', 'Maths', 85),
(3, 'John', 'Physics', 73),
(4, 'John', 'Chemistry', 80),
(5, 'robert', 'English', 60),
(6, 'robert', 'Maths', 65),
(7, 'robert', 'Physics', 69),
(8, 'robert', 'Chemistry', 70),
(9, 'Adam', 'English', 75),
(10, 'Adam', 'Maths', 70),
(11, 'Adam', 'Physics', 85),
(12, 'Adam', 'Chemistry', 77),
(13, 'jack', 'English', 95),
(14, 'jack', 'Maths', 90),
(15, 'jack', 'Physics', 91),
(16, 'jack', 'Chemistry', 75);

For connect database Check this

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

<body>
<table id='table_sort' class='tablesorter'>
<thead>
<tr>
<th>id</th>
<th>student</th>
<th>subject</th>
<th>mark</th>
</tr>
</thead>
<tbody>
<?php
//connect to database
include "../config.php";

//query the database
$sql = "select * from exam";
$rs = mysql_query ( $sql );
while ( $row = mysql_fetch_array ( $rs ) ){   // fetch the data from database
extract ( $row );   // use function to get exact row asc or desc
	?>
<tr>
<td><?php echo $id; ?></td>


<td><?php echo $subject; ?></td>
<td><?php echo $subject; ?></td>
<td><?php echo $mark; ?></td>

</tr>
<?php
}
?>
</tbody>
</table>

Step 3: Include jquery functions for sorting

<!-- include jQuery library and table sorter plugin -->
<script type='text/javascript' src='js/jquery-latest.js'>
</script>
<script type='text/javascript' src='js/jquery.tablesorter.min.js'>
</script>


<script type='text/javascript'>
	$(document).ready(function() { 
		$("#table_sort").tablesorter({ 
			//for example we want to disable the 
			//password column (5th column) from sorting
			//we will specify '4' since it was indexed
			//(count starts at '0')
			//and set its property to 'false'
			headers: { 
				4: {    
					sorter: false 
				}
			}
		}); 
	});
</script>

Step 4 : Add css style for design

.tablesorter {
margin:0px;
padding:0px;
width:100%;	
box-shadow: 10px 10px 5px #888888;

-moz-border-radius-bottomleft:0px;
-webkit-border-bottom-left-radius:0px;
border-bottom-left-radius:0px;

-moz-border-radius-bottomright:0px;
-webkit-border-bottom-right-radius:0px;
border-bottom-right-radius:0px;

-moz-border-radius-topright:0px;
-webkit-border-top-right-radius:0px;
border-top-right-radius:0px;

-moz-border-radius-topleft:0px;
-webkit-border-top-left-radius:0px;
border-top-left-radius:0px;
}

.tablesorter table{
width:100%;
margin:0px;
padding:0px;
}


.tablesorter tr:nth-child(odd){ 

background-color:#D9ECFE; 

}
.tablesorter tr:nth-child(even)    { background-color:#ffffff; }
.tablesorter td{
vertical-align:middle;
text-align:left;
padding:7px;
font-size:16px;
font-family:arial;
font-weight:normal;
color:#000000;
}

table.tablesorter thead tr .header {
    background-image: url(images/bg.gif);
    background-repeat: no-repeat;
    background-position: center right;
    cursor: pointer;
}

table.tablesorter thead tr .headerSortDown {
    background-image: url(images/desc.gif);
}
table.tablesorter thead tr .headerSortUp {
    background-image: url(images/asc.gif);
}

construct the table with id ‘table_srot’ and use this id for sorting. thead tag is required for using tablesorter. tbody tag is also required for using tablesorter. using mysql fetching with array you can get the data and using “exact” function you can access exect row asc or dec.
That’s it enjoy the code.

Awesome, share it: