# jQuery UI Sortable Tutorial - Save Positions With Ajax & PHP & MySQL (ok)

E:\Draggable và Droppable

{% file src="https://3988971916-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-LXUV09EJOZ8BKB8YL5I%2F-MSRw6oM_UZmuYDxrjER%2F-MSRwBQw4-LNN1l3J23r%2Fjquerysortable.rar?alt=media&token=495cc5b1-497d-40b3-8f07-9849cdc01743" %}

![](https://3988971916-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-LXUV09EJOZ8BKB8YL5I%2F-MSRXsrQjG3pmjU30PAF%2F-MSRgUrVMlFOODDnX_Ij%2FScreenshot_1.jpg?alt=media\&token=6fcde4d6-20af-4db7-84e3-f3308fc57b8d)

Bước 1: Chuẩn bị giao diện

C:\xampp\htdocs\php\index.php

```
<!doctype html>
<html lang="en">
<html lang="en">
<head>
  <meta charset="UTF-8">
  <meta name="viewport" content="width=device-width, user-scalable=no, initial-scale=1.0, maximum-scale=1.0, minimum-scale=1.0">
  <meta http-equiv="X-UA-Compatible" content="ie=edge">
  <title>jQuery Sortable</title>
  <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/4.0.0/css/bootstrap.min.css">
  <script src="https://code.jquery.com/jquery-3.3.1.min.js"></script>
  <script src="https://code.jquery.com/ui/1.12.1/jquery-ui.min.js"></script>
</head>
<body>
  <div class="container" style="margin-top: 100px;">
    <div class="row justify-content-center">
      <div class="col-md-4 col-md-offset-4">
        <table class="table table-stripped table-hover table-bordered">
          <thead>
            <tr>
              <td>Country Name</td>
            </tr>
          </thead>
          <tbody>
            <tr>
              <td>Afghanistan</td>
            </tr>
            <tr>
              <td>Ecuador</td>
            </tr>
            <tr>
              <td>East Timor</td>
            </tr>
            <tr>
              <td>Djibouti</td>
            </tr>
            <tr>
              <td>Denmark</td>
            </tr>
            <tr>
              <td>Cameroon</td>
            </tr>
            <tr>
              <td>Cambodia</td>
            </tr>
            <tr>
              <td>Bahrain</td>
            </tr>
            <tr>
              <td>Bahamas</td>
            </tr>
            <tr>
              <td>Albania</td>
            </tr>
          </tbody>
        </table>
      </div>
    </div>
  </div>
</body>
</html>
```

Bước 2: Đổ dữ liệu từ mysql

```
<?php  
  $conn = new mysqli("localhost", "root", "", "jQuerySortable");
?>
<!doctype html>
<html lang="en">
<html lang="en">
<head>
  <meta charset="UTF-8">
  <meta name="viewport" content="width=device-width, user-scalable=no, initial-scale=1.0, maximum-scale=1.0, minimum-scale=1.0">
  <meta http-equiv="X-UA-Compatible" content="ie=edge">
  <title>jQuery Sortable</title>
  <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/4.0.0/css/bootstrap.min.css">
  <script src="https://code.jquery.com/jquery-3.3.1.min.js"></script>
  <script src="https://code.jquery.com/ui/1.12.1/jquery-ui.min.js"></script>
</head>
<body>
  <div class="container" style="margin-top: 100px;">
    <div class="row justify-content-center">
      <div class="col-md-4 col-md-offset-4">
        <table class="table table-stripped table-hover table-bordered">
          <thead>
            <tr>
              <td>Country Name</td>
            </tr>
          </thead>
          <tbody>
            <?php  
              $sql = $conn->query("SELECT id, name, position FROM country ORDER BY position");
              $html = '';
              while ($data = $sql->fetch_array()) {
                $html .= '<tr data-id="'.$data['id'].'" data-position="'.$data['position'].'">';
                  $html .= '<td>'.$data['name'].'</td>';
                $html .= '</tr>';
              }
              echo $html;
            ?>
          </tbody>
        </table>
      </div>
    </div>
  </div>
  <script type="text/javascript">
    $(document).ready(function () {
      $('table tbody').sortable({
        update: function( event, ui ) {
          $(this).children().each(function (index) {
            console.log(index);
          });
        }
      });
    });
  </script>
</body>
</html>
```

Bước 3: Phát hiện được sự thay đổi vị trí

C:\xampp\htdocs\php\index.php

```
<?php  
  $conn = new mysqli("localhost", "root", "", "jQuerySortable");
?>
<!doctype html>
<html lang="en">
<html lang="en">
<head>
  <meta charset="UTF-8">
  <meta name="viewport" content="width=device-width, user-scalable=no, initial-scale=1.0, maximum-scale=1.0, minimum-scale=1.0">
  <meta http-equiv="X-UA-Compatible" content="ie=edge">
  <title>jQuery Sortable</title>
  <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/4.0.0/css/bootstrap.min.css">
  <script src="https://code.jquery.com/jquery-3.3.1.min.js"></script>
  <script src="https://code.jquery.com/ui/1.12.1/jquery-ui.min.js"></script>
</head>
<body>
  <div class="container" style="margin-top: 100px;">
    <div class="row justify-content-center">
      <div class="col-md-4 col-md-offset-4">
        <table class="table table-stripped table-hover table-bordered">
          <thead>
            <tr>
              <td>Country Name</td>
            </tr>
          </thead>
          <tbody>
            <?php  
              $sql = $conn->query("SELECT id, name, position FROM country ORDER BY position");
              $html = '';
              while ($data = $sql->fetch_array()) {
                $html .= '<tr data-id="'.$data['id'].'" data-position="'.$data['position'].'">';
                  $html .= '<td>'.$data['name'].'</td>';
                $html .= '</tr>';
              }
              echo $html;
            ?>
          </tbody>
        </table>
      </div>
    </div>
  </div>
  <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');
            }
          });
        }
      });
    });
  </script>
</body>
</html>
```

Bước 4: Thực hiện việc update data

C:\xampp\htdocs\php\index.php

```
<?php  
  $conn = new mysqli("localhost", "root", "", "jQuerySortable");
  if (isset($_POST['update'])) {
    foreach ($_POST['positions'] as $position) {
      $index       = $position[0];
      $newPosition = $position[1];
      $conn->query("UPDATE country SET position = '$newPosition' WHERE id='$index'");
    };
  }
?>
<!doctype html>
<html lang="en">
<html lang="en">
<head>
  <meta charset="UTF-8">
  <meta name="viewport" content="width=device-width, user-scalable=no, initial-scale=1.0, maximum-scale=1.0, minimum-scale=1.0">
  <meta http-equiv="X-UA-Compatible" content="ie=edge">
  <title>jQuery Sortable</title>
  <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/4.0.0/css/bootstrap.min.css">
  <script src="https://code.jquery.com/jquery-3.3.1.min.js"></script>
  <script src="https://code.jquery.com/ui/1.12.1/jquery-ui.min.js"></script>
</head>
<body>
  <div class="container" style="margin-top: 100px;">
    <div class="row justify-content-center">
      <div class="col-md-4 col-md-offset-4">
        <table class="table table-stripped table-hover table-bordered">
          <thead>
            <tr>
              <td>Country Name</td>
            </tr>
          </thead>
          <tbody>
            <?php  
              $sql = $conn->query("SELECT id, name, position FROM country ORDER BY position");
              $html = '';
              while ($data = $sql->fetch_array()) {
                $html .= '<tr data-index="'.$data['id'].'" data-position="'.$data['position'].'">';
                  $html .= '<td>'.$data['name'].'</td>';
                $html .= '</tr>';
              }
              echo $html;
            ?>
          </tbody>
        </table>
      </div>
    </div>
  </div>
  <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).data('position', (index+1)).addClass('updated');
            }
          });
          saveNewPositions();
        }
      });
    });
    function saveNewPositions() {
      var positions = [];
      $('.updated').each(function () {
        positions.push([$(this).data('index'), $(this).data('position')]);
        $(this).removeClass('updated');
      });
      $.ajax({
        url: 'index.php',
        method: 'POST',
        dataType: 'text',
        data: {update: 1, positions: positions},
      })
      .done(function(success) {
      });
    }
  </script>
</body>
</html>
```

