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!

has been added to your cart!

Powered by Easy Digital Downloads

Thank you!

have been added to your cart!

Powered by Easy Digital Downloads

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