Sort Table Rows using jQuery and PHP

PHPZAG Team PHP

As a PHP programmer, we often need to implement table rows sorting with drag and drop to save into MySQL database dynamically. Here we are going to share how to implement jQuery sort table using jQuery sortable() function with PHP.

In this tutorial, you will see a running example of sorting table rows by drag and drop the rows and save changed rows display order automatically with rows change into MySQL database table.

Also, read:

Here we have very easy steps to implement table rows sort using jQuery and PHP.

1- Create MySQL Table:

The SQL table “php_questions” has 4 columns id(This is a unique identifier), question, answer, display_order(display order of items).


CREATE TABLE IF NOT EXISTS `php_questions` (
`id` int(8) NOT NULL AUTO_INCREMENT,
`question` text NOT NULL,
`answer` text NOT NULL,
`display_order` text NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=7 ;

2- Insert Data into MySQL Table:

Now you need to insert some data into “php_questions” table to display list of questions. Uses below insert statement to insert data.

INSERT INTO `php_questions` (`id`, `question`, `answer`, `display_order`) 
VALUES
(1, 'What is PHP?', 'PHP (Hypertext Preprocessor) is a server side scripting
 language that is embedded in HTML. It is used to manage dynamic content,
 databases, session tracking, even build entire e-commerce sites.', '4'),
(2, ' What is the purpose of php.ini file?', ' It is a PHP 
configuration file that controls and effect PHP’s functionality. 
The php.ini file is read each time when PHP is initialized. 
You can check settings of php.ini by calling phpinfo() function in PHP script.', '1'),
(3, 'What are the differences between PHP constants and variables?', '', '5'),
(4, 'How will you find the length of a string in PHP?', '', '2'),
(5, 'What is the difference between single quoted string and double quoted string?', '', '0'),
(6, 'How do you create a cookie, add data to it, and remove data from a cookie?', '', '3');

3- Get Data From MySQL Table:

Here we will get rows from MySQL database table and displaying to the browser using sortable list items.

<ul id="sortable-rows">
<?php
$sql = "SELECT id, question FROM php_questions ORDER BY display_order";
$resultset = mysqli_query($conn, $sql) or die("database error:". mysqli_error($conn));
while( $rows = mysqli_fetch_assoc($resultset) ) {
?>
<li id=<?php echo $rows["id"]; ?>><?php echo $rows["question"]; ?></li>
<?php } ?>
</ul>

4. Sort Table Rows and Make Ajax Request to Update Sort Order

This is JavaScript code to make table rows sortable with drag and drop.

$(function() {
$( "#sortable-rows" ).sortable({
placeholder: "ui-state-highlight",
update: function( event, ui ) {
updateDisplayOrder();
}
});
});

And also contains a function <strongupdateDisplayOrder to make Ajax request to PHP script update_order.php to save new display order to the MySQL database.

function updateDisplayOrder() {
var selectedLanguage = new Array();
$('ul#sortable-rows li').each(function() {
selectedLanguage.push($(this).attr("id"));
});
var dataString = 'sort_order='+selectedLanguage;
$.ajax({
type: "GET",
url: "update_order.php",
data: dataString,
cache: false,
success: function(data){
}
});
}

5. Update Sort Order in MySQL Database Table

Here is a PHP script that read form data for getting the new display order. It executes MySQL update query to save new order in database table.


if(isset($_GET["sort_order"])) {
$id_ary = explode(",",$_GET["sort_order"]);
for($i=0;$i < count($id_ary);$i++) {
$sql = "UPDATE php_questions SET display_order='" . $i . "' WHERE id=". $id_ary[$i];
mysqli_query($conn, $sql) or die("database error:". mysqli_error($conn));
}
}

You may also like:

You can see the complete running example from the demo link. You can also download complete demo script from below links.

Demo Download