Monday, 17 February 2020

Live Bootstrap 4 Table Row Reorder with PHP using Ajax jQuery



Table Row Reorder is a very important task in web development. For this things here we have make this tutorial, in which we have use Bootstrap 4 Table and we will Reorder Bootstrap 4 Table row reorder by using jQuery UI with Ajax and PHP. Table Row Reorder means, in which order we want to display row in Bootstrap 4 table.

For table row reorder here we will use jQuery UI Drag and Drop feature. jQuery UI Drag and Drop feature is very user friendly which allows to Users to reorder components by drag and drop functionality. That means it will put elements on desired position. If you are developing any web application, in which you have list your data in Bootstrap 4 table, then by using Drag and Drop functionality, we can easily reorder table row data and it will increase our web application user experience. Now in your mind how to enable drag and drop feature, for this we have to use jQuery UI library sortable() plugin. This plugin will enable drap and drop feature to any element.

By using jQuery UI sortable() plugin we can enable dragging and dropping functionality to any element, but after reorder table row, we want to store updated order data in Mysql table also. So when we have refresh web page, then our reorder row data has not be lost. For this we have to store updated order data in Mysql table on every drag and drop event. For update order data in Mysql table, here we will use Ajax request in sortable() method, so when we have drag and drop elements, then it will send Ajax request to PHP script for store order data in Mysql table. Below you can find source code of Live Bootstrap 4 Table row reorder with PHP using jQuery Ajax and Mysql database.



Source Code


Database



--
-- Database: `testing`
--

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

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

CREATE TABLE `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 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', 4),
(3, 'Create Treeview with Bootstrap Treeview Ajax JQuery in PHP\r\n', 'create-treeview-with-bootstrap-treeview-ajax-jquery-in-php', 8),
(4, 'Bootstrap Multiselect Dropdown with Checkboxes using Jquery in PHP\r\n', 'bootstrap-multiselect-dropdown-with-checkboxes-using-jquery-in-php', 10),
(5, 'Facebook Style Popup Notification using PHP Ajax Bootstrap\r\n', 'facebook-style-popup-notification-using-php-ajax-bootstrap', 7),
(6, 'Modal with Dynamic Previous & Next Data Button by Ajax PHP\r\n', 'modal-with-dynamic-previous-next-data-button-by-ajax-php', 5),
(7, 'How to Use Bootstrap Select Plugin with Ajax Jquery PHP\r\n', 'how-to-use-bootstrap-select-plugin-with-ajax-jquery-php', 1),
(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', 2),
(9, 'Autocomplete Textbox using Typeahead with Ajax PHP Bootstrap\r\n', 'autocomplete-textbox-using-typeahead-with-ajax-php-bootstrap', 6),
(10, 'Export Data to Excel in Codeigniter using PHPExcel\r\n', 'export-data-to-excel-in-codeigniter-using-phpexcel', 9);

--
-- 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;


index.php



<!DOCTYPE html>
<html>
 <head>
  <title>Live Bootstrap 4 Table Row Reorder with PHP using Ajax jQuery</title>
  <script src="https://code.jquery.com/jquery-1.12.4.min.js"></script>
  <script src="https://cdnjs.cloudflare.com/ajax/libs/popper.js/1.12.9/umd/popper.min.js"></script>
  <script src="https://maxcdn.bootstrapcdn.com/bootstrap/4.0.0/js/bootstrap.min.js"></script>
  <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/4.0.0/css/bootstrap.min.css" />
  <link rel="stylesheet" href="https://use.fontawesome.com/releases/v5.6.3/css/all.css" integrity="sha384-UHRtZLI+pbxtHCWp1t77Bi1L4ZtiqrqD80Kn4Z8NTSRyMA2Fd33n5dQ8lWUE00s/" crossorigin="anonymous">
  <link rel="stylesheet" href="https://code.jquery.com/ui/1.12.0/themes/smoothness/jquery-ui.css" type="text/css">
  <script src="https://code.jquery.com/ui/1.12.0/jquery-ui.min.js" integrity="sha256-eGE6blurk5sHj+rmkfsGYeKyZx3M4bG+ZlFyA7Kns7E=" crossorigin="anonymous"></script>
 </head>
 <body>
  <br />
  <br />
  <div class="container">
   <h3 align="center">Live Bootstrap 4 Table Row Reorder with PHP using Ajax jQuery</h3>
   <br />
   <div class="card">
    <div class="card-header">Live Table Row Reorder</div>
    <div class="card-body">
     <div class="table-resposive">
      <table class="table table-striped table-bordered">
       <thead style="cursor: ">
        <tr>
         <th>Post Name</th>
         <th>Post Order</th>
        </tr>
       </thead>
       <tbody style="cursor: all-scroll;"></tbody>
      </table>
     </div>
    </div>
   </div>
  </div>
 </body>
</html>
<script>
$(document).ready(function(){

 load_data();

 function load_data()
 {
  $.ajax({
   url:"action.php",
   method:"POST",
   data:{action:'fetch_data'},
   dataType:'json',
   success:function(data)
   {
    var html = '';
    for(var count = 0; count < data.length; count++)
    {
     html += '<tr id="'+data[count].page_id+'">';
     html += '<td>'+data[count].page_title+'</td>';
     html += '<td>'+data[count].page_order+'</td>';
     html += '</tr>';
    }
    $('tbody').html(html);
   }
  })
 }

 $('tbody').sortable({
  placeholder : "ui-state-highlight",
  update : function(event, ui)
  {
   var page_id_array = new Array();
   $('tbody tr').each(function(){
    page_id_array.push($(this).attr('id'));
   });

   $.ajax({
    url:"action.php",
    method:"POST",
    data:{page_id_array:page_id_array, action:'update'},
    success:function()
    {
     load_data();
    }
   })
  }
 });

});
</script>


action.php




<?php

//action.php;

$connect = new PDO("mysql:host=localhost;dbname=testing", "root", "");

if(isset($_POST["action"]))
{
 if($_POST["action"] == 'fetch_data')
 {
  $query = "
  SELECT * FROM page 
  ORDER BY page_order ASC
  ";

  $statement = $connect->prepare($query);

  $statement->execute();

  while($row = $statement->fetch(PDO::FETCH_ASSOC))
  {
   $data[] = $row;
  }

  echo json_encode($data);
 }

 if($_POST['action'] == 'update')
 {
  for($count = 0;  $count < count($_POST["page_id_array"]); $count++)
  {
   $query = "
   UPDATE page 
   SET page_order = '".($count+1)."' 
   WHERE page_id = '".$_POST["page_id_array"][$count]."'
   ";
   $statement = $connect->prepare($query);
   $statement->execute();
  }
 }
}

?>






2 comments:

  1. Thanks share important topics.We are a global IT solutions company provide full-cycle services in the areas of software development, web-based enterprise solutions, web application and portal development. website development company in pune | website developer in pune

    ReplyDelete