jquery sortable saving to database not working properly

Question

I'm trying to incorporate the jquery sortable functionality into my website and saving the positions in the database is giving me all sorts of headaches... I've been fighting this for 3 days now, and I cannot seem to get this work properly.

As it stands, it is saving positions to the database, but not in the order or positions, that you'd expect. Meaning, if I move the item in position 0 to position 1, it saves the positions in a different order in the db. check out a live version here.

Here is my code...

index.php file:

<div id="container">
   <?php
      require_once 'conn.php';
      $q = ' SELECT * FROM items WHERE groupId = 3 ORDER BY position ';
      $result = mysqli_query($db, $q);
      if ($result->num_rows > 0) {
         while($items = $result->fetch_assoc()) {
      ?>
      <div id='sort_<?php echo$items['position'] ?>' class='items'>
         <span>&#9776;</span> <?php echo$items['description'] ?>
      </div>
      <?php
         }
      }
   ?>
</div>

js.js file:

$("#container").sortable({
   opacity: 0.325,
   tolerance: 'pointer',
   cursor: 'move',
   update: function(event, ui) {
      var itId = 3;
      var post = $(this).sortable('serialize');

      $.ajax({
         type: 'POST',
         url: 'save.php',
         data: {positions: post, id: itId },
         dataType: 'json',
         cache: false,
         success: function(output) {
            // console.log('success -> ' + output);
         },
         error: function(output) {
            // console.log('fail -> ' + output);
         }
      });

   }
});
$("#container").disableSelection();

save.php file:

require_once('conn.php');

$itId = $_POST['id'];
$orderArr = $_POST['positions'];
$arr = array();
$orderArr = parse_str($orderArr, $arr);
$combine = implode(', ', $arr['sort']);

$getIds = "SELECT id FROM items WHERE groupId = '$itId' ";
$result = mysqli_query($db, $getIds);

foreach($arr['sort'] as $a) {
   $row = $result->fetch_assoc();
   $sql = " UPDATE items
            SET position = '$a'
            WHERE id = '{$row['id']}' ";
   mysqli_query($db, $sql);
}

echo json_encode( ($arr['sort']) );

Can anyone please point to where I am going wrong on this?

Thank you in advance.

Serge

Answer 1

In case someone lands on here, here is what worked in my case...

NOTE: I did not create prepared statements in the index.php select function. But you probably should.

index.php file:

<div id="container">
      <?php
         require_once 'conn.php';
         $q = ' SELECT * FROM items WHERE groupId = 3 ORDER BY position ';
            $result = mysqli_query($db, $q);

         if ($result->num_rows > 0) {

            while( $items = $result->fetch_assoc() ){
      ?>
               <div id='sort_<?php echo $items['id'] ?>' class='items'>
                  <span>&#9776;</span> <?php echo $items['description'] ?>
               </div>
      <?php
            }
         }
      ?>
   </div>

jquery sortable file:

var ul_sortable = $('#container');

   ul_sortable.sortable({
      opacity: 0.325,
      tolerance: 'pointer',
      cursor: 'move',
      update: function(event, ui) {
         var post = ul_sortable.sortable('serialize');

         $.ajax({
            type: 'POST',
            url: 'save.php',
            data: post,
            dataType: 'json',
            cache: false,
            success: function(output) {
               console.log('success -> ' + output);
            },
            error: function(output) {
               console.log('fail -> ' + output);
            }
         });

      }
   });
   ul_sortable.disableSelection();

update php file:

$isNum = false;

foreach( $_POST['sort'] as $key => $value ) {
    if ( ctype_digit($value) ) {
        $isNum = true;
    } else {
        $isNum = false;
    }
}

if( isset($_POST) && $isNum == true ){
    require_once('conn.php');
   $orderArr = $_POST['sort'];
    $order = 0;
    if ($stmt = $db->prepare(" UPDATE items SET position = ? WHERE id=? ")) {
        foreach ( $orderArr as $item) {
            $stmt->bind_param("ii", $order, $item);
            $stmt->execute();
            $order++;
        }
        $stmt->close();
    }
    echo json_encode(  $orderArr );
    $db->close();
}

Answer 2

Change your JS code like this:

{...}
   tolerance: 'pointer',
   cursor: 'move',
// new LINE
   items: '.items', // <---- this is the new line
   update: function(event, ui) {
      var itId = 3;
      var post = $(this).sortable('serialize'); // it could be removed
// new LINES start
      var post={},count=0;
      $(this).children('.items').each(function(){
       post[++count]=$(this).attr('id');
      });
// new LINES end
      $.ajax({
{...}

With this $.each loop you overwrite your var post -> serialize and define your own sort order. Now look at your $_POST["positions"] with PHP print_r($_POST["positions"]); and you have your positions in your own order.