How To Use Table Sorter With A Database

Photo of Mike Dalisay
Modified Sunday, June 19, 2011
by - @ninjazhai
Hi there! Today we're going to do a script that:

1. Get list of data from a database.
2. Arrange those data into a table, so that
3. It will be sortable (without page refresh) once you click its header.

Quick and Easy Sorting Without Page Refresh AJAX

All of these are done with the help of TableSorter. It is a jQuery plugin that lets you sort your tables without refreshing page.
                                                        Flexible client-side table sorting

This one is beautiful when you want to sort hundreds of table rows really quick. A thousand table rows could be sorted a bit slower, but still effective.


Step 1: Download tablesorter and place it inside your js/ directory

Step 2: You can also download a theme here (I used Blue Skin) for your table.

Step 3: Create your database table. You could have this one, just add more records if you want:

  `firstname` varchar(32) NOT NULL,
  `lastname` varchar(32) NOT NULL,
  `email` varchar(32) NOT NULL,
  `username` varchar(32) NOT NULL,
  `password` varchar(32) NOT NULL,
  PRIMARY KEY (`id`)

-- Dumping data for table `users`

INSERT INTO `users` (`id`, `firstname`, `lastname`, `email`, `username`, `password`) VALUES
(1, 'John', 'Doe', '', 'johnny', 'john'),
(2, 'Albert', 'Minston', '', 'albert', 'albert');

Step 4: Create your database configuration file (i got config_open_db.php) and index.php file. Place the following codes inside the index.php file.

        <title>How To Use Table Sorter With A Database</title>
        <!-- we will use the 'blue' theme -->
        <link href='blue/style.css' rel='stylesheet' type='text/css' />
//connect to database
include 'config_open_db.php';

//query the database
$sql = "select * from users";
$rs = mysql_query ( $sql );

//construct the table with id 'your-table'
echo "<table id='your-table' class='tablesorter'>";
    echo "<thead>"; //thead tag is required for using tablesorter
        echo "<tr>";
            echo "<th>Lastname";
            echo "<th>Firstname";
            echo "<th>Email";
            echo "<th>Username";
            echo "<th>Password";
        echo "</tr>";
    echo "</thead>";
echo "<tbody>"; //tbody tag is required for using tablesorter

//looping through retrieved data
while ( $row = mysql_fetch_array ( $rs ) ){
    extract ( $row );
    echo "<tr>";
        echo "<td>$lastname</td>";
        echo "<td>$firstname</td>";
        echo "<td>$email</td>";
        echo "<td>$username</td>";
        echo "<td>******</td>";
    echo "</tr>";
echo "</tbody>";
echo "</table>";
<!-- include jQuery library and table sorter plugin -->
<script type='text/javascript' src='js/tablesorter/jquery-latest.js'>
<script type='text/javascript' src='js/tablesorter/jquery.tablesorter.min.js'>

<script type='text/javascript'>
    $(document).ready(function() { 
            //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 

Hope this helps. :)

The Code of a Ninja Resources

For FREE programming tutorials, click the red button below and subscribe! :)
Thanks for the comments!
"First do it, then do it right, then do it better."
~ Addy Osmani
"Talk is cheap. Show me the code."
~ Linus Torvalds
Let's Stay Connected!
g+ r
Android app on Google Play
© 2011-2014 The Code Of A Ninja. All rights reserved. Proudly Powered by Google Blogger. Images, logos, marks or names mentioned herein are the property of their respective owners.