Updating a database display order with drag and drop in SQL

gistfile1.txt

<table>
  <thead>
    <tr>
      <th scope="col">user_id</th>
      <th scope="col">display_order</th>
      <th scope="col">todo</th>
    </tr>
  </thead>
  <tbody>
    <tr>
      <td>2000</td>
      <td>1</td>
      <td>Take out garbage</td>
    </tr>
    <tr>
      <td>2000</td>
      <td>2</td>
      <td>Clean house</td>
    </tr>
    <tr>
      <td>2000</td>
      <td>3</td>
      <td>Do dishes</td>
    </tr>
    <tr>
      <td>2000</td>
      <td>4</td>
      <td>Cut grass</td>
    </tr>
    <tr>
      <td>2000</td>
      <td>5</td>
      <td>Change light bulb</td>
    </tr>
  </tbody>
</table>

list.html

<ul class="todos">
  <li class="todo">Take out garbage</li>
  <li class="todo">Clean house</li>
  <li class="todo">Do dishes</li>
  <li class="todo">Cut grass</li>
  <li class="todo">Change light bulb</li>
</ul>

move-down.php

// Move down: Update the items between the current position and the desired position, decreasing each item by 1 to make space for the new item
if ($move == 'down') {
    $query = "UPDATE todos
              SET display_order = (display_order - 1)
              WHERE display_order > :current_position
              AND display_order <= :desired_position
              AND user_id = :user_id";
}

move-up.php

// Move up: Update the items between the desired position and the current position, increasing each item by 1 to make space for the new item
if ($move == 'up') {
    $query = "UPDATE todos
              SET display_order = (display_order + 1)
              WHERE display_order >= :desired_position
              AND display_order < :current_position
              AND user_id = :user_id";
}

position.php

// Determine if the user is moving the item up or down in the listing
$move = $desired_position > $current_position ? 'down' : 'up';

reset.php

// Update the item that was dragged and set it to be the desired position now that the slot is opend up
$query = "UPDATE todos
          SET display_order = :desired_position
          WHERE display_order = 0
          AND user_id = :user_id";

set.php

// Set the display_order for the dragged item to be 0 so we can update this record later by display_order = 0
$query = "UPDATE todos
          SET display_order = 0
          WHERE display_order = :current_position
          AND user_id = :user_id";

sort.js

// Make the form fields sortable/draggable
$('.todos').sortable({
    items: '.todo',
    start: function(event, ui) {
        // Create a temporary attribute on the element with the old index
        $(this).attr('data-currentindex', ui.item.index());
    },
    update: function(event, ui) {
        let user_id = $('#user_id').val();
        let current_position = $(this).attr('data-currentindex');
        let desired_position = ui.item.index();

        // Reset the current index
        $(this).removeAttr('data-currentindex');

        // Post to the server to handle the changes
        $.ajax({
            type: "POST",
            url: "/url-to-handle-database-updates/",
            data: {
                desired_position: desired_position,
                current_position: current_position,
                user_id: user_id
            },
            beforeSend: function() {
                // Disable dragging
                $('.todos').sortable('disable');
            },
            success: function(html) {
                // Re-enable dragging
                $('.todos').sortable('enable');
            }
        });
    }
});

For people who might set position/display_order as unique column in the database - one should add appropriate ordering in the update queries:

// Move up: Update the items between the desired position and the current position, increasing each item by 1 to make space for the new item
if ($move == 'up') {
    $query = "UPDATE todos
              SET display_order = (display_order + 1)
              WHERE display_order >= :desired_position
              AND display_order < :current_position
              AND user_id = :user_id
              ORDER BY display_order DESC";
}

// Move down: Update the items between the current position and the desired position, decreasing each item by 1 to make space for the new item
if ($move == 'down') {
    $query = "UPDATE todos
              SET display_order = (display_order - 1)
              WHERE display_order > :current_position
              AND display_order <= :desired_position
              AND user_id = :user_id
              ORDER BY display_order ASC";
}