This article will explain how to reorder mysql database records without lose the information with a user friendly drag and drop. For this a field should be available which can save the position record for particular record . For example if you want to display a record on no 2 and it is on no 5. So i have used jquery ui sortable function for this . First of all you need a table in which the records are present . Let us create it .

CREATE TABLE IF NOT EXISTS `sortable` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  `display_order` int(11) NOT NULL,
  `created` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `modified` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

Then you need some html part so that user can drag & drop the elements . So here is the index.php

<head>
  <meta charset="utf-8">
  <title>jQuery UI Sortable - Default functionality</title>
  <link rel="stylesheet" href="//code.jquery.com/ui/1.11.4/themes/smoothness/jquery-ui.css">
  <script src="//code.jquery.com/jquery-1.10.2.js"></script>
  <script src="//code.jquery.com/ui/1.11.4/jquery-ui.js"></script>
  <script>
  $(function() {
    $( "#sortable" ).sortable();
    $( "#sortable" ).disableSelection();
  });
  </script>
</head>
<body>
<?php require_once('function.php'); ?>	
<ul id="sortable">
	<?php $data=get_records(); ?> 
	<?php foreach($data as $record): ?>
		<li data-id="<?php echo $record['id'];  ?>" class="ui-state-default"><span class="ui-icon ui-icon-arrowthick-2-n-s"></span><?php echo $record['name']; ?></li>
	<?php endforeach; ?>
</ul>
<button id="save-reorder">Save</button> 
</body>

Then you need a ajax function which will request to server to save the records with new order . so here it is

$(document).ready(function(){
	$(document).on('click','#save-reorder',function(){
		var list = new Array();
		$('#sortable').find('.ui-state-default').each(function(){
			 var id=$(this).attr('data-id');	
			 list.push(id);
		});
		var data=JSON.stringify(list);
		$.ajax({
        url: 'save.php', // server url
        type: 'POST', //POST or GET 
        data: {token:'reorder',data:data}, // data to send in ajax format or querystring format
        datatype: 'json',
        success: function(message) {
			alert(message);
        }
 
    });
	});
	
});

This ajax request will send data to the save.php file . and code on this file will be like this

require_once('function.php');
if(isset($_POST['token']))
{
	$data=json_decode($_POST['data']);
	$counter=1;
	foreach($data as $key=>$val)
	{
		save_record($val,$counter);
		$counter++;
	}	
	echo "saved";
}

some database function to handle the saving and connection process . Function.php

function connectsql()
{
	$con=mysqli_connect("localhost","root","","test");
	if (mysqli_connect_errno())
	{
		echo "Failed to connect to MySQL: " . mysqli_connect_error();
	}
	return $con;
}
function get_records()
{
	$con=connectsql();
	$records=mysqli_query($con,"SELECT * FROM sortable order by display_order ASC");
	$all=array();
	while($data=$records->fetch_assoc())
	{
		$all[]=$data;
	}
	return $all;
}
function save_record($id,$order)
{
	$con=connectsql();
	$query="UPDATE sortable SET display_order=".$order." WHERE id=".$id;
	if ($con->query($query) === TRUE) {
    //echo "Record updated successfully";
	} else {
    //echo "Error updating record: " . $conn->error;
	}
}

some css rule to make it nice

  #sortable { list-style-type: none; margin: 0; 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; }
  #save-reorder{ padding:10px; background:#12a574;color:#fff;}
  .wrapper{width:60%; margin0 auto;}
http://codingbin.com/wp-content/uploads/2015/12/jQuery-UI-Sortable-Default-functionality1.pnghttp://codingbin.com/wp-content/uploads/2015/12/jQuery-UI-Sortable-Default-functionality1-150x150.pngdevidjQueryphpdrag and drop,reorderThis article will explain how to reorder mysql database records without lose the information with a user friendly drag and drop. For this a field should be available which can save the position record for particular record . For example if you want to display a record on no...coding, php, wordpress, yii, cakephp, and  jquery

Are you want to get implementation help, or modify or extend the functionality of this script? Submit paid service request