MYSQL update based on certain order from jquery sortable

Question

I am using jquery sortable and trying to save these items' positions into a mysql table's row named "position". I can't seem to get them to save/update correctly. Everything is working ok on the jquery side, just the php update function. Right now it is saving the position rows as 3, which I get why. I just can't seem to wrap my head around how to do this properly.

Here's what I have for the PHP:

$id = $_POST['id'];
$arr = $_POST['positions'];

foreach($arr as $r) {
   $sql = " UPDATE items
            SET   position = '$r'
            WHERE groupId = '$id' ";
}

Table Structure / desired output:

id | groupId | position
----------------------------
3    10       0
6    8        -
8    10       3
10   5        -
15   10       2
18   10       1

The jQuery file:

$("#items").sortable({
      update: function() {
         var invId = $("input[name='deleteId']").val();
         var post = $(this).sortable('serialize');

         $.ajax({
            type: 'POST',
            url: 'file.php',
            data: { positions: post, id: invId },
            dataType: 'JSON',
            cache: false,
            success: function(output) {
               console.log('success');
            },
            error: function(output) {
               console.log('fail ');
            }
         });
      }
   });

Thanks.

Answer 1

Your query updates all rows with groupId = 10 each time through the loop, so at the end they will all contain the value from the last element of the array.

You need to write a query that gets a row number for each matching row in the table, and then updates only that row. You can use a user-defined variable to increment the row number.

UPDATE items AS i1
JOIN (SELECT id, @rownum := rownum + 1 AS rownum
      FROM (SELECT id
            FROM items
            WHERE groupId = '$id'
            ORDER BY id) AS i
      CROSS JOIN (SELECT @rownum := 0) AS var) AS i2
ON i1.id = i2.id
JOIN (SELECT 1 AS target, 0 AS val
      UNION ALL
      SELECT 2, 3
      UNION ALL
      SELECT 3, 2
      UNION
      SELECT 4, 1) AS newpos
ON newpos.target = i2.rownum
SET i1.position = newpos.val

Answer 2

I think that may be I am missing something, but maybe you should update the items table based on id that matches that specific group id. Something like this:

$id = 10;
$arr = ('0', '3', '2', '1');

$searchId = "SELECT id FROM items WHERE groupId = '$id'";
$result = $db->query($searchId);

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

I assumed you use mysqli to connect to your database.

Answer 3

jQuery Sortable should already post data where the values are the row ids and the keys the order. That way, you can do a foreach loop. Assuming the following data:

//$_POST (use chrome inspector to see what post data is being sent)
//positions[] = 3
//positions[] = 18  
//positions[] = 15  
//positions[] = 8  

// note: use a prepared statement, this is just to demonstrate the query.
foreach($_POST['positions'] as $i => $id) {
    $db->query('update table set position = ? where id = ? ',array($i, $id));
}

Answer 4

If anyone lands on here with similar issues, this is what worked for me.

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('con.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();
}

JQUERY SORTABLE FILE:

var sortable = $('#items');
   sortable.sortable({
      opacity: 0.325,
      tolerance: 'pointer',
      cursor: 'move',
      update: function(event, ui) {
         var post_data = sortable.sortable('serialize');

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

      }
   });
   sortable.disableSelection();