Home jQuery

How To Use jQuery Table Sorter With A Database

jquery-tutorial
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.

How To Use Table Sorter With A Database

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

tablesorter
 

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:

CREATE TABLE IF NOT EXISTS `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `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`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;

--
-- Dumping data for table `users`
--

INSERT INTO `users` (`id`, `firstname`, `lastname`, `email`, `username`, `password`) VALUES
(1, 'John', 'Doe', 'john@gmail.com', 'johnny', 'john'),
(2, 'Albert', 'Minston', 'albert@gmail.com', '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.

<html>
   <head>
        <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' />
   </head>
<body>
<?php
//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>
<script type='text/javascript' src='js/tablesorter/jquery.tablesorter.min.js'>
</script>

<script type='text/javascript'>
    $(document).ready(function() { 
        $("#your-table").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>
</body>
</html>

Hope this helps. :)

Download Source Code

You can download all the code used in this tutorial for only $9.99 $5.55!

Thank you for learning from our post about: How To Use jQuery Table Sorter With A Database.