Saturday 10 June 2017

Sorting Table Row using Jquery Drag Drop with Ajax PHP



In this post, We have learn how to sort table row by using Jquery Drag and drop with Ajax and PHP script without refresh of webpage. If you have use Wordpress then in admin dashboard you can find this type of feature. By using this functionality we can easily rearrange table row data with easily user interface. User can also easily sort table row by using Drag and Drop html element.

Here we have use JQuery UI drag and drop library sortable() method. By using this method we can easily move any HTML from one place to another and particular element will be placed automatically and it will be sort element. But when we have refresh web page then HTML will be arrange according to its orignal place. But we don't want to change position of HTML element and get HTML element position as per our drag and drop. So for this here we have use Ajax with PHP script. So when we have move HTML element then we will fire Ajax request send to server with all table row id with index order and in PHP script it will update table row order data according to index position.

By using this feature User can easily sort table row by simply drag and drop HTML element and User can easily understand this type of interface. If you have developed any web based application, then you should use this type feature for make User friendly application and user can easily used this application. Below you can find complete source code of this tutorial and also get online demo also.





Source Code


index.php



<?php
$connect = mysqli_connect("localhost", "root", "", "testing");
$query = "SELECT * FROM page ORDER BY page_order ASC";
$result = mysqli_query($connect, $query);
?>
<html>
 <head>
  <title>Sorting Table Row using JQuery Drag Drop with Ajax PHP</title>
  <script src="http://code.jquery.com/jquery-1.10.2.js"></script>
  <script src="http://code.jquery.com/ui/1.11.2/jquery-ui.js"></script>
  <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css" >
  <script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/js/bootstrap.min.js"></script>
  <style>
   body
   {
    margin:0;
    padding:0;
    background-color:#f1f1f1;
   }
   .box
   {
    width:1270px;
    padding:20px;
    background-color:#fff;
    border:1px solid #ccc;
    border-radius:5px;
    margin-top:25px;
   }
   #page_list li
   {
    padding:16px;
    background-color:#f9f9f9;
    border:1px dotted #ccc;
    cursor:move;
    margin-top:12px;
   }
   #page_list li.ui-state-highlight
   {
    padding:24px;
    background-color:#ffffcc;
    border:1px dotted #ccc;
    cursor:move;
    margin-top:12px;
   }
  </style>
 </head>
 <body>
  <div class="container box">
   <h1 align="center">Sorting Table Row using JQuery Drag Drop with Ajax PHP</h1>
   <br />
   <ul class="list-unstyled" id="page_list">
   <?php 
   while($row = mysqli_fetch_array($result))
   {
    echo '<li id="'.$row["page_id"].'">'.$row["page_title"].'</li>';
   }
   ?>
   </ul>
   <input type="hidden" name="page_order_list" id="page_order_list" />
  </div>
 </body>
</html>

<script>
$(document).ready(function(){
 $( "#page_list" ).sortable({
  placeholder : "ui-state-highlight",
  update  : function(event, ui)
  {
   var page_id_array = new Array();
   $('#page_list li').each(function(){
    page_id_array.push($(this).attr("id"));
   });
   $.ajax({
    url:"update.php",
    method:"POST",
    data:{page_id_array:page_id_array},
    success:function(data)
    {
     alert(data);
    }
   });
  }
 });

});
</script>


update.php



<?php
//update.php
$connect = mysqli_connect("localhost", "root", "", "testing");
//$page_id = $_POST["page_id_array"];
for($i=0; $i<count($_POST["page_id_array"]); $i++)
{
 $query = "
 UPDATE page 
 SET page_order = '".$i."' 
 WHERE page_id = '".$_POST["page_id_array"][$i]."'";
 mysqli_query($connect, $query);
}
echo 'Page Order has been updated'; 

?>



<?php
//update.php
$connect = mysqli_connect("localhost", "root", "", "testing");
//$page_id = $_POST["page_id_array"];
for($i=0; $i<count($_POST["page_id_array"]); $i++)
{
 $query = "
 UPDATE page 
 SET page_order = '".$i."' 
 WHERE page_id = '".$_POST["page_id_array"][$i]."'";
 mysqli_query($connect, $query);
}
echo 'Page Order has been updated'; 

?>



Database



--
-- Database: `testing`
--

-- --------------------------------------------------------

--
-- Table structure for table `page`
--

CREATE TABLE IF NOT EXISTS `page` (
  `page_id` int(11) NOT NULL,
  `page_title` text NOT NULL,
  `page_url` text NOT NULL,
  `page_order` int(11) NOT NULL
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=latin1;

--
-- Dumping data for table `page`
--

INSERT INTO `page` (`page_id`, `page_title`, `page_url`, `page_order`) VALUES
(1, 'JSON - Dynamic Dependent Dropdown List using Jquery and Ajax', 'json-dynamic-dependent-dropdown-list-using-jquery-and-ajax', 3),
(2, 'Live Table Data Edit Delete using Tabledit Plugin in PHP', 'live-table-data-edit-delete-using-tabledit-plugin-in-php', 5),
(3, 'Create Treeview with Bootstrap Treeview Ajax JQuery in PHP\r\n', 'create-treeview-with-bootstrap-treeview-ajax-jquery-in-php', 9),
(4, 'Bootstrap Multiselect Dropdown with Checkboxes using Jquery in PHP\r\n', 'bootstrap-multiselect-dropdown-with-checkboxes-using-jquery-in-php', 0),
(5, 'Facebook Style Popup Notification using PHP Ajax Bootstrap\r\n', 'facebook-style-popup-notification-using-php-ajax-bootstrap', 1),
(6, 'Modal with Dynamic Previous & Next Data Button by Ajax PHP\r\n', 'modal-with-dynamic-previous-next-data-button-by-ajax-php', 6),
(7, 'How to Use Bootstrap Select Plugin with Ajax Jquery PHP\r\n', 'how-to-use-bootstrap-select-plugin-with-ajax-jquery-php', 7),
(8, 'How to Load CSV File data into HTML Table Using AJAX jQuery\r\n', 'how-to-load-csv-file-data-into-html-table-using-ajax-jquery', 8),
(9, 'Autocomplete Textbox using Typeahead with Ajax PHP Bootstrap\r\n', 'autocomplete-textbox-using-typeahead-with-ajax-php-bootstrap', 4),
(10, 'Export Data to Excel in Codeigniter using PHPExcel\r\n', 'export-data-to-excel-in-codeigniter-using-phpexcel', 2);

--
-- Indexes for dumped tables
--

--
-- Indexes for table `page`
--
ALTER TABLE `page`
  ADD PRIMARY KEY (`page_id`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `page`
--
ALTER TABLE `page`
  MODIFY `page_id` int(11) NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=11;

13 comments:

  1. sir please upload coupon card discount codeigniter

    ReplyDelete
  2. Great tutorial. Thanks man!

    ReplyDelete
  3. Exactly what I have been looking for. Sdayd my day. Thanks and keep up your good job.

    ReplyDelete
  4. thanks for sharing. it's working with me on localhost but when i make it live so then it's not working.i have php version 5.6 on live server.

    ReplyDelete
  5. in which version of php its working its not working on live

    ReplyDelete
  6. Great tutorial, shame it doesn't work as the past few posted. Why are there 2 update.php files?

    ReplyDelete
  7. Try this code for the update.php. Ensure that your user in MySQL has update permissions


    connect_error) {
    die("Connection failed: " . $conn->connect_error);
    }


    echo count($_POST["page_id_array"]);
    for($i=0; $iclose();



    ?>


    ReplyDelete
  8. it mactches my requirement perfectly thanks .

    ReplyDelete