In this post we learned how to sort table rows using drag and drop jQuery with ajax and PHP scripts without refreshing the web page. If you are using WordPress, you can find this type of feature in the admin control panel. With this functionality, we can easily rearrange table row data with a simple user interface. Users can also easily sort table rows using HTML and drag and drop elements

Here we are using the jQuery UI method to drag and drop the sortable() library. With this method we can easily move HTML code from one place to another. Certain elements are automatically placed and are sort elements. However, when we update the web page, the HTML is adjusted according to its original position.

However, we don\’t want to reposition the HTML elements and position the HTML elements using drag and drop. That\’s why we\’re using Ajax with a PHP script here. So when we have moved our HTML elements we trigger sending Ajax requests to the server with all row table IDs with indexes and updating the table row data in the PHP script according to the index position.

With this function, users can sort table rows by simply dragging and dropping HTML elements, and users can easily understand this kind of interface. If you have developed a web based application then to make it a user friendly application you need to use this type of feature and the user can easily use the application. You can find the complete source code for this tutorial as well as an online demonstration below.

Preview

Create Database and Table by SQL query

In first step, we need to create database and table, so here I created “webscodex” database and “countries” table with id, name, iso, phone code and position column. You can simply create “countries” table as following SQL query.

SQL query

-- Database: `webscodex`
--
-- --------------------------------------------------------
-- Table structure for table `countries`
--

CREATE TABLE `countries` (
  `id` int(11) NOT NULL PRIMARY KEY AUTO_INCREMENT,
  `iso` char(2) NOT NULL,
  `name` varchar(80) NOT NULL,
  `phonecode` int(5) NOT NULL,
  `position` int(11) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

--
-- Dumping data for table `countries`
--

INSERT INTO `countries` (`id`, `iso`, `name`, `phonecode`, `position`) VALUES
(1, 'AF', 'AFGHANISTAN', 93, 152),
(2, 'AL', 'ALBANIA', 355, 137),
(3, 'DZ', 'ALGERIA', 213, 136),
(4, 'AS', 'AMERICAN SAMOA', 1684, 135),
(5, 'AD', 'ANDORRA', 376, 134),
(6, 'AO', 'ANGOLA', 244, 133),
(7, 'AI', 'ANGUILLA', 1264, 132),
(8, 'AQ', 'ANTARCTICA', 0, 131),
(9, 'AG', 'ANTIGUA AND BARBUDA', 1268, 130),
(10, 'AR', 'ARGENTINA', 54, 129),
(11, 'AM', 'ARMENIA', 374, 128),
(12, 'AW', 'ARUBA', 297, 127),
(13, 'AU', 'AUSTRALIA', 61, 126),
(14, 'AT', 'AUSTRIA', 43, 138),
(15, 'AZ', 'AZERBAIJAN', 994, 139),
(16, 'BS', 'BAHAMAS', 1242, 140),
(17, 'BH', 'BAHRAIN', 973, 151),
(18, 'BD', 'BANGLADESH', 880, 150),
(19, 'BB', 'BARBADOS', 1246, 149),
(20, 'BY', 'BELARUS', 375, 148)

In this step, we require to create database configuration file, here we will set database name, username and password. So let’s create “config.php” file on your root directory and put bellow code:

Create Database Configuration File

config.php

<?php

	// Database configuration 	
	$hostname = "localhost"; 
	$username = "root"; 
	$password = ""; 
	$dbname   = "webscodex";
	 
	// Create database connection 
	$con = new mysqli($hostname, $username, $password, $dbname); 
	 
	// Check connection 
	if ($con->connect_error) { 
	    die("Connection failed: " . $con->connect_error); 
	}

?>

Create index File and Display Sorted list

Here, we need to create index.php file and we will display all data and write code for sorting with drag and drop table rows. So let\’s create index.php file and put bellow code. 

index.php

<?php

   // Database conneciton

   include_once('config.php');

   if (isset($_POST['update'])) {

   $positions = $_POST['positions'];

   foreach ($positions as $key => $position) {
      $index = $position[0];
      $newPosition = $position[1];
      $query  = "UPDATE countries SET position = '$newPosition' WHERE id = '$index'";
      $result = $con->query($query);
   }
      echo "Your country position change successfully saved";
      exit();
   }

?>

<!DOCTYPE html>
<html lang="en">
<head>
  <title>Sorting Table Row using Jquery Drag Drop with Ajax PHP</title>
  <meta charset="utf-8">
  <meta name="viewport" content="width=device-width, initial-scale=1">
  <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/4.5.2/css/bootstrap.min.css"> 
</head>
<body>

<style type="text/css">
   #page_list td
   {
    padding:16px;
    background-color:#f9f9f9;
    border:1px dotted #ccc;
    cursor:move;
    margin-top:12px;
   }
</style>

<div class="card text-center" style="padding:20px;">
  <h3>Sorting Table Row using Jquery Drag Drop with Ajax PHP</h3>
</div><br>

<div class="container">
  <div class="row justify-content-center">
    <div class="col-md-8 col-md-offset-8">
      <div class="card">
        <table class="table table-bordered table-stripped table-hovered">
          <thead>
            <tr>
              <th>Id</th>
              <th>Country name</th>
              <th>ISO</th>
              <th>Phone code</th>
            </tr>  
          </thead>
          <tbody>
            <?php

              $query = "SELECT * FROM countries ORDER BY position";
              $result = $con->query($query);
              if ($result->num_rows > 0) {
                while ($data = $result->fetch_assoc()) {
            ?>
            <tr data-index="<?php echo $data['id'] ?>" data-position="<?php echo $data['position'] ?>" 
               id="page_list">
              <td><?php echo $data['id'] ?></td>
              <td><?php echo $data['name'] ?></td>
              <td><?php echo $data['iso'] ?></td>
              <td><?php echo $data['phonecode'] ?></td>
            </tr>

            <?php  }

            }else{
              echo "No records found";
            }?>
          </tbody>
        </table>        
      </div>
    </div>
  </div>
</div>

<script src="http://code.jquery.com/jquery-3.5.1.min.js"></script>
<script src="http://code.jquery.com/ui/1.12.1/jquery-ui.min.js"></script>

<script type="text/javascript">

  $(document).ready(function(){

    $('table tbody').sortable({
      update:function(event,ui){
        $(this).children().each(function(index){
          if($(this).attr('data-position') != (index+1)) {
            $(this).attr('data-position', (index+1)).addClass('updated');
          }
        });
        saveNewPositions();
      }
    });
  });

  function saveNewPositions(){
    var positions = new Array();
    $('.updated').each(function(){
      positions.push([$(this).attr('data-index'), $(this).attr('data-position')]);
      $(this).removeClass('updated');
    });

    $.ajax({
      url : 'index.php',
      method:'POST',
      dataType: 'text',
      data:{update:1,positions:positions},
      success:function(result){
        alert(result);
      }
    });
  }
</script>
</body>
</html>

You can always support by sharing on social media or recommending my blog to your friends and colleagues. If you have any suggestions / problems about this tutorial, please comment on the form below.😊