[adinserter block=”34″]Hi there! Today we’re going to do a script that:
- Get list of data from a database.
- Arrange those data into a table, so that
- It will be sortable (without page refresh) once you click its header.
All of these are done with the help of TableSorter. It is a jQuery plugin that lets you sort your tables without refreshing page.
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', '[email protected]', 'johnny', 'john'), (2, 'Albert', 'Minston', '[email protected]', '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!
[purchase_link id=”12344″ text=”Download Now” style=”button” color=”green”]
Thank you for learning from our post about: How To Use jQuery Table Sorter With A Database.
Leave a Reply