Sorting Data in the Table with jQuery, PHP and MySQL

In this tutorial, I will teach you how to sort list of data in the table with jQuery, PHP and MySQL database.

This method provide primitives for sorting slices and user-defined collection of records. It is also efficient and quick for sorting of data in the table.

Let’s begin:

Sorting Data in the Table with jQuery, PHP and MySQL

Create a database in the MySQL and name it “dbsort“
Make a query for creating table in the database that you have created.

CREATE TABLE IF NOT EXISTS `subject` (
 `SUBJ_ID` int(11) NOT NULL AUTO_INCREMENT,
 `SUBJ_CODE` varchar(30) NOT NULL,
 `SUBJ_DESCRIPTION` varchar(255) NOT NULL,
 `UNIT` int(2) NOT NULL,
 `YR` varchar(30) NOT NULL,
 `AY` varchar(30) NOT NULL,
 PRIMARY KEY (`SUBJ_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Make a query for inserting data in the table.

INSERT INTO `subject` (`SUBJ_ID`, `SUBJ_CODE`, `SUBJ_DESCRIPTION`, `UNIT`, `YR`, `AY`) VALUES
(11, 'Theology 1', 'Faith and Creed and Basic Catholic Doctrine', 3, 'Grade 7', '2013-2014'),
(12, 'English 0', 'English Plus ', 3, 'Grade 7', '2013-2014'),
(13, 'English 1', 'Developmental Reading', 3, 'Grade 7', '2013-2014'),
(14, 'Theology 1', 'Faith and Creed and Basic Catholic Doctrine', 3, 'Gade 8', '2013-2014'),
(15, 'History 1', 'Philippine History and Culture', 3, 'Grade 7', '2013-2014'),
(16, 'Psychology 1a', 'General Psychology with Moral Regen & Drug Abuse Ed.', 3, '22', '2013-2014'),
(17, 'Chem. 1', 'General and Organic Chemistry', 5, '22', '2013-2014'),
(18, 'Philosophy 1', 'Introduction to Philosophy', 3, '22', '2013-2014'),
(19, 'PE 1', 'Physical Education', 2, '22', '2013-2014'),
(20, 'NSTP 1', 'NSTP', 3, 'Grade 7', '2013-2014'),
(21, 'Theology 2', 'Bible and Salvation History ', 3, '22', '2013-2014'),
(22, 'English 1', 'Study and Thinking Skills ', 3, '22', '2013-2014'),
(23, 'English 02', 'Developmental Reading 2', 3, '22', '2013-2014');

Create a landing page and name it “index.php“.
Create a configuration between MySQL database and PHP script.

<?php
 $server = 'localhost';
 $dbuser = 'root';
 $dbpass = '';
 $dbname = 'quicksearchdb';
 $con = mysql_connect($server, $dbuser, $dbpass);
 if (isset($con)) {
 # code...
 $dbSelect = mysql_select_db($dbname);
 if (!$dbSelect) {
 echo "Problem in selecting database! Please contact administraator";
 die(mysql_error());
 }
 } else {
 echo "Problem in database connection! Please contact administraator";
 die(mysql_error());
 }
 ?>

Do the following codes for the layout, design and retrieving data in the database.

<h1>jQuery Table Sorter</h1> 
 <table id="dataTable" class="table table-bordered" style="font-size:11px;">
 <thead style="background-color:blue;color:white">
 <tr>
 <th width="10%">Subject Code</th>
 <th width="30%">Description</th>
 <th width="10%">Unit</th>
 <th width="10%">Year</th>
 <th width="10%">Academic Year</th> 
 </tr>
 </thead>
 <tbody>
 <?php 
 $sqlQuery = "SELECT * FROM `subject` limit 10";
 $result = mysql_query($sqlQuery) or die(mysql_error());

 while ($row = mysql_fetch_array($result)) {
 echo '<tr>'; 
 echo '<td>' .$row['SUBJ_CODE'].'</a></td>';
 echo '<td>'. $row['SUBJ_DESCRIPTION'].'</td>'; 
 echo '<td>'. $row['UNIT'].'</td>'; 
 echo '<td>'. $row['YR'].'</td>'; 
 echo '<td>'. $row['AY'].'</td>'; 
 echo '</tr>';
 }
 
 ?>
 </tbody>
 </table>

Set the following jQuery plugins.

 <script src="jquery/jquery-2.2.4.min.js"></script>
 <script src="jquery/tableSorter.jquery.js"></script>

Finally, create a script for sorting data in the table.

<script>
 $(document).ready(function() { 
 $("#dataTable").tablesorter(); 
 } 
 ); 
 </script>

For all students who need programmer for your thesis system or anyone who needs a sourcecode in any programming languages. You can contact me @ :

Email – [email protected]
Mobile No. – 09305235027 – tnt

Download Sourcecode

Leave a Comment Cancel reply