Save Sort Preference to MySQL Using PHP, Drag-and-Drop

Question

I'm using JQuery to provide my users with a re-arrangeable list of items. Like this:

Image of List

Here's the simple HTML:

      <ul class="sortable">
      <li>Item 1<i class="sorthandle"></i></li>
      <li>Item 2<i class="sorthandle"></i></li>          
      </ul>

The drag-and-drop feature works great. The next step is saving the user's sort preference to the database. I plan to add a "priority" column to my database, which will hold an integer representing the user's preferred sort order. My query will then Order by 'priority' ASC.

I'm thinking I need to generate an array of the sort order currently displayed on the drag-and-drop screen, then save those values to the database records. But as a JavaScript newbie, I need some help.

My JavaScript

<script src="//cdnjs.cloudflare.com/ajax/libs/jquery/2.1.3/jquery.min.js"></script>
<script src="//cdnjs.cloudflare.com/ajax/libs/jqueryui/1.11.2/jquery-ui.min.js"></script>
<script type="text/javascript">
$(function() {
  $(".sortable").sortable({
    connectWith: ".sortable",
    handle: '.handle'
  }).disableSelection();

});
</script>

Generating the drag-and-drop list via PHP:

while ($row = mysqli_fetch_array($result)){
  $playlistname = $row['name'];
  $id = $row['id'];
  echo "<li>$playlistname<i class='handle fa fa-bars'></i></li>";

}

Answer 1

First of all, JavaScript will need the IDs, not just the name. You can simply put them in a data attribute (I also got rid of the unnecessary variable assignments and built the string with concatenation)

echo '<li data-id="' . $row['id'] . '">' . $row['name'] . '<i class="handle fa fa-bars"></i></li>';

You can then extract this data attribute in JavaScript and create an array. You might want to think about when you save the settings. The easiest way would be to provide a button. You could also do it on the sortable update event (e.g. after "dropping" the element). But that is risky and might overload your server.

To trigger an AJAX POST on button click, put this code within $(function() {. It binds a lambda function on the click event of the DOM element with the ID save, f.ex. <button id="save">save order</button>

// bind a callback to the button click
$('#save').on('click', function () {
    // array where we store the IDs
    var ids = [];
    // loop through the <li> and extract data-id
    $('.sortable li').each(function() {
        ids.push($(this).data('id'));
    });

    // AJAX POST the array to a PHP script
    $.post('/savescript.php', {'ids[]': ids});
});

you can then access the data in $_POST['ids'].

Answer 2

I was able to accomplish this without writing any more JavaScript. PHP worked just fine!

PHP - Getting Values

<?php
$sql = "SELECT name, id FROM playlists WHERE userid = 'XX' AND active = 1 ORDER BY priority ASC";
$row = mysqli_fetch_array($result,MYSQLI_ASSOC);
$result = mysqli_query($conn,$sql);

 ?>
<form action="" method="POST">

<div class="container">
      <ul class="sortable">

        <?php
          while ($row = mysqli_fetch_array($result)){ /* FETCH ARRAY */
          $playlistname = $row['name'];
          $id = $row['id'];
          echo "<li>$playlistname<i class='handle fa fa-bars'><input type='hidden' name='sort[]' value='$id' /></i></li>";
          }
        ?>

        </ul>
</div>
<center><button name="submit" id="sortsavebutton" type="SUBMIT" value="submit" action="POST">Save</button></center><br>
</form>

Processing on Save/Submission

if (isset($_POST['submit'])){
    $rearrange = $_POST['sort'];
    $count = count($rearrange);
    $sort = 0;
    for($i=0;$i<$count;$i++){

        if(!empty($rearrange[$i])){ 

        $id = mysqli_real_escape_string($conn,$rearrange[$i]); /* ESCAPE STRINGS */
    $priority = $sort++;
    $stmt = $conn->prepare("UPDATE playlists SET priority=? WHERE id = ?");
    $stmt->bind_param("ii", $priority, $id);
    $stmt->execute();

          } 

    } 

}