Drag and Drop Reorder Items with jQuery, PHP & MySQL

March 21, 2022 XpertPhp

In this tutorial, we will explain to you how to create drag and drop reorder items with jQuery, PHP & MySQL.
If you want to drag and drop reorder elements, you should use the jquery UI library because you can drag and drop elements(jquery UI drag and drop list) easily with the help of jquery UI.

Create MySQL Database Table

In this step, We will create the MySQL table for reordering items. so you can see the below example.

CREATE TABLE `items` (
  `id` int(11) NOT NULL,
  `item_name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `priority` int(5) NOT NULL DEFAULT '0',
  `status` enum('1','0') COLLATE utf8_unicode_ci NOT NULL DEFAULT '1',
  `created` datetime NOT NULL,
  `modified` datetime NOT NULL,
   PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE TABLE `items` (

`id` int(11) NOT NULL,

`item_name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,

`priority` int(5) NOT NULL DEFAULT '0',

`status` enum('1','0') COLLATE utf8_unicode_ci NOT NULL DEFAULT '1',

`created` datetime NOT NULL,

`modified` datetime NOT NULL,

PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

Connect to Database

Create a new connect.php file. The code below is to provide how to connect with the database.
The following code is used to connect MySQL from PHP. It requires hostname, database username, database password, and database name.

<?php

 $hostname="localhost";
 $username="root";
 $password="test";
 $database="register";

 $conn = mysqli_connect($hostname,$username,$password,$database); 
 
?>

<?php

$hostname="localhost";

$username="root";

$password="test";

$database="register";

$conn = mysqli_connect($hostname,$username,$password,$database);

Include jQuery and jQuery UI Library

<link rel="stylesheet" href="//code.jquery.com/ui/1.13.1/themes/base/jquery-ui.css">
<script src="https://code.jquery.com/jquery-3.6.0.js"></script>
<script src="https://code.jquery.com/ui/1.13.1/jquery-ui.js"></script>

Display Items from the database

<ul id="sortable">
	<?php
	include("connect.php");
	$sql_query = "SELECT id, item_name FROM items ORDER BY priority";
	$result = mysqli_query($conn, $sql_query) or die("database error:". mysqli_error($conn));
	while( $row = mysqli_fetch_assoc($result)) 
	{	?>
		<li class="ui-state-default" id="<?php echo $row["id"]; ?>"><span class="ui-icon ui-icon-arrowthick-2-n-s"></span><?php echo $row["item_name"]; ?></li>
	  <?php 
	} ?>
</ul>

<?php

include("connect.php");

$sql_query = "SELECT id, item_name FROM items ORDER BY priority";

$result = mysqli_query($conn, $sql_query) or die("database error:". mysqli_error($conn));

while( $row = mysqli_fetch_assoc($result))

{ ?>

<?php

} ?>

</ul>

Implement Drag and Drop with jQuery UI

  <script>
	$(function() {
		$("#sortable").sortable({		
			update: function( event, ui ) {
				updateOrder();
			}
		});  
	});
  
	function updateOrder() {	
		var item_order = new Array();
		$('#sortable li').each(function() {
			item_order.push($(this).attr("id"));
		});
		var order_string = 'order='+item_order;
		$.ajax({
			type: "POST",
			url: "update_order.php",
			data: order_string,
			cache: false,
			success: function(data){			
			}
		});
	}
  </script>

$(function() {

$("#sortable").sortable({

update: function( event, ui ) {

updateOrder();

}

});

function updateOrder() {

var item_order = new Array();

$('#sortable li').each(function() {

item_order.push($(this).attr("id"));

});

var order_string = 'order='+item_order;

$.ajax({

type: "POST",

url: "update_order.php",

data: order_string,

cache: false,

success: function(data){

}

});

}

</script>

index.php

<!doctype html>
<html lang="en">
<head>
  <meta charset="utf-8">
  <meta name="viewport" content="width=device-width, initial-scale=1">
  <title>Drag and Drop Reorder Items with jQuery, PHP & MySQL - XpertPhp</title>
  <link rel="stylesheet" href="//code.jquery.com/ui/1.13.1/themes/base/jquery-ui.css">
  <style>
  #sortable { list-style-type: none; margin: 0 auto; padding: 0; width: 60%; }
  #sortable li { margin: 0 3px 3px 3px; padding: 0.4em; padding-left: 1.5em; font-size: 1.4em; height: 18px; }
  #sortable li span { position: absolute; margin-left: -1.3em; }
  </style>
  <script src="https://code.jquery.com/jquery-3.6.0.js"></script>
  <script src="https://code.jquery.com/ui/1.13.1/jquery-ui.js"></script>
  <script>
	$(function() {
		$("#sortable").sortable({		
			update: function( event, ui ) {
				updateOrder();
			}
		});  
	});
  
	function updateOrder() {	
		var item_order = new Array();
		$('#sortable li').each(function() {
			item_order.push($(this).attr("id"));
		});
		var order_string = 'order='+item_order;
		$.ajax({
			type: "POST",
			url: "update_order.php",
			data: order_string,
			cache: false,
			success: function(data){			
			}
		});
	}
  </script>
</head>
<body>
	
<ul id="sortable">
	<?php
	include("connect.php");
	$sql_query = "SELECT id, item_name FROM items ORDER BY priority";
	$result = mysqli_query($conn, $sql_query) or die("database error:". mysqli_error($conn));
	while( $row = mysqli_fetch_assoc($result)) 
	{	?>
		<li class="ui-state-default" id="<?php echo $row["id"]; ?>"><span class="ui-icon ui-icon-arrowthick-2-n-s"></span><?php echo $row["item_name"]; ?></li>
	  <?php 
	} ?>
</ul>
</body>
</html>

<!doctype html>

<head>

<title>Drag and Drop Reorder Items with jQuery, PHP & MySQL - XpertPhp</title>

<style>

#sortable { list-style-type: none; margin: 0 auto; padding: 0; width: 60%; }

#sortable li { margin: 0 3px 3px 3px; padding: 0.4em; padding-left: 1.5em; font-size: 1.4em; height: 18px; }

#sortable li span { position: absolute; margin-left: -1.3em; }

</style>

$(function() {

$("#sortable").sortable({

update: function( event, ui ) {

updateOrder();

}

});

function updateOrder() {

var item_order = new Array();

$('#sortable li').each(function() {

item_order.push($(this).attr("id"));

});

var order_string = 'order='+item_order;

$.ajax({

type: "POST",

url: "update_order.php",

data: order_string,

cache: false,

success: function(data){

}

});

}

</script>

</head>

<body>

<?php

include("connect.php");

$sql_query = "SELECT id, item_name FROM items ORDER BY priority";

$result = mysqli_query($conn, $sql_query) or die("database error:". mysqli_error($conn));

while( $row = mysqli_fetch_assoc($result))

{ ?>

<?php

} ?>

</ul>

</body>

</html>

Update Order into MySQL Database Table

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

<?php

if(isset($_POST["order"])) {

include("connect.php");

$order = explode(",",$_POST["order"]);

for($i=0; $i < count($order);$i++) {

$sql = "UPDATE items SET priority='" . $i . "' WHERE id=". $order[$i];

mysqli_query($conn, $sql) or die("database error:". mysqli_error($conn));

}

Please follow and like us:

Create MySQL Database Table

Connect to Database

Include jQuery and jQuery UI Library

Display Items from the database

Implement Drag and Drop with jQuery UI

Update Order into MySQL Database Table

Related posts: