Skip to main content

Drag and Drop Reorder with jQuery, PHP & MySQL

Updated on: coderszine team PHP, Tutorial

In our previous tutorial, we have explained how to implement Data Export to Excel with PHP and MySQL. So in this tutorial, we will explain how to implement drag and drop sorting functionality with jQuery and PHP.

Drag and drop is a very user friendly feature that allows users to sort or reorder element by drag and drop on desired position. If you’re developing a web application that listing items or images, then you can easily add drag and drop functionality in your web application to increase user experience. You just need to use jQuery UI that enables you to easily add draggable functionality on items list.

We will also use MySQL database table to display items and also order of items after drag and drop reorder. The tutorial explained in easy steps with live demo to display list of images and then reorder images by drag and drop. The images order will be save in MySQL database on each image drag and drag using Ajax. You can also download source code of live demo.

Also, read:

So let’s create example to implement drag and drop reorder with jQuery, PHP and MYSQL. The file structure for the example is following.

  • index.php
  • reorder.js
  • update_order.php

Steps1: Creare MySQL Database Table

As we will display images list from database, so first we will create MySQL database table reorder and store images with their order.

CREATE TABLE `reorder` (
  `id` int(11) NOT NULL,
  `image_name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `display_order` int(5) NOT NULL DEFAULT '0',
  `created` datetime NOT NULL,
  `modified` datetime NOT NULL,
  `status` enum('1','0') COLLATE utf8_unicode_ci NOT NULL DEFAULT '1'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

Steps2: Include Bootstrap, jQuery and jQuery UI Libraray

As we will handle this example with Bootstrap, so we need to include Bootstrap library files. We also need to include jQuery and jQuery UI library files to add drag and drop functionality. We will include these files in index.php

<script src="https://ajax.googleapis.com/ajax/libs/jquery/2.2.4/jquery.min.js"></script>
<script src="https://ajax.googleapis.com/ajax/libs/jqueryui/1.12.1/jquery-ui.min.js"></script>
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.5/css/bootstrap.min.css">

Steps3: Display Images Gallery

In index.php, we will display images gallery from database table according to display_order.

<div>		
	<div class="gallery">
		<ul class="reorder-gallery">
		<?php 			
		$sql_query = "SELECT id, image_name FROM reorder ORDER BY display_order";
		$resultset = mysqli_query($conn, $sql_query) or die("database error:". mysqli_error($conn));
		$data_records = array();
		while( $row = mysqli_fetch_assoc($resultset)) {				
		?>
			<li id="<?php echo $row['id']; ?>" class="ui-sortable-handle"><a href="javascript:void(0);"><img src="images/<?php echo $row['image_name']; ?>" alt=""></a></li>
		<?php } ?>
		</ul>
	</div>
</div>

Steps4: Implement Drag and Drop with jQuery UI

Now in reorder.js, we will handle drag and drop reorder functionality using jQuery UI sortable() function. We will also update order into MySQL database using sortable update properties to call function updateOrder() to make Ajax request to server side script update_order.php to update order into MySQL database table.

$(document).ready(function(){	
	$("ul.reorder-gallery").sortable({		
		update: function( event, ui ) {
			updateOrder();
		}
	});  
});
function updateOrder() {	
	var item_order = new Array();
	$('ul.reorder-gallery li').each(function() {
		item_order.push($(this).attr("id"));
	});
	var order_string = 'order='+item_order;
	$.ajax({
		type: "GET",
		url: "update_order.php",
		data: order_string,
		cache: false,
		success: function(data){			
		}
	});
}

Steps5: Update Order into MySQL Database Table

Now finally in update_order.php, we will update display order into MySQL database table.

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

You may also like:

You can view the live demo from the Demo link and can download the source from the Download link below.
Demo Download

4 thoughts on “Drag and Drop Reorder with jQuery, PHP & MySQL”

Comments are closed.