Update data order using drag-drop

Question

I show some data from the DB on a table ordered by a column in the DB called order:

<tbody>
    <tr id="1" data-order="1">
        <td>1</td>    <!-- id from DB -->
        <td>Test</td> 
        <td>1</td>    <!-- order from DB  --> 
        <td>
            <a>Edit</a>
            <a>Delete</a>
        </td>
    </tr>
    ..
</tbody>

I use jquery-uri for drag and drop http://cdnjs.cloudflare.com/ajax/libs/jqueryui/1.12.1/jquery-ui.min.js

JS code:

jQuery("table tbody").sortable({
    update: function (event, ui) {
        UpdateOrder();
    }
});

function UpdateOrder() {
    jQuery("table tbody tr").each(function () {
        //Do something.
    }
}

It's working and I can drag and drop the elements, But how to save the new order of elements?

Like the WordPress menus drag and drop feature, Where you could organize the pages in the menu using drag-drop.

I could create a form beneath the table:

<form method="POST">
    <input type="submit" name="update_order" value="Save" >
</form>

Then check if that form is submitted:

if( isset($_POST['update_order']) ){
    global $wpdb;
    $wpdb->update('data', array("order" => ) , array('id' => ));
}

But how to gather all the elements order and ids to update them?

How to do that?

Answer 1

Please try this

$(document).ready(function() {
    jQuery("table tbody").sortable();
    // Save button click event
       $("[name='update_order']").click(function(e){
       e.preventDefault();
       UpdateOrder();
    });
 });
 
 function UpdateOrder() {
    var jsonObj = [];
    var id = '';
    var existing_order = '';
    var updated_order = '';
    var updated_row = {};
    jQuery("table tbody tr").each(function (index, value) {
      if((index+1) != $(this).data('order'))
      { // To check whether the current row order has been changed or not. Only the changed order rows only needs to be updated.
        id = $(this).attr("id");
        existing_order = $(this).data('order');
        updated_order = (index+1);
        updated_row = {}
        updated_row ["id"] = id;
        updated_row ["existing_order"] = existing_order; // I just added for your reference. If you do not need this value, comment this line.
        updated_row ["updated_order"] = updated_order;
        jsonObj.push(updated_row);
      }
    });
    console.log(JSON.stringify(jsonObj));
//ex: [{"id":"2","existing_order":2,"updated_order":1},{"id":"3","existing_order":3,"updated_order":2},{"id":"1","existing_order":1,"updated_order":3}]
    $('form').append("<input type='hidden' name='updated_rows' value='"+JSON.stringify(jsonObj)+"'>").submit(); // To send the values to server side script (here, PHP). Please do empty validation before you send to server if you need
}

<script src="https://ajax.googleapis.com/ajax/libs/jquery/1.11.1/jquery.min.js"></script>
<script src="http://cdnjs.cloudflare.com/ajax/libs/jqueryui/1.12.1/jquery-ui.min.js"></script>
<table>
  <tbody>
      <tr id="1" data-order="1">
          <td>1</td>    <!-- id from DB -->
          <td>Test</td>
          <td>1</td>    <!-- order from DB  -->
          <td>
              <a>Edit</a>
              <a>Delete</a>
          </td>
      </tr>
      <tr id="2" data-order="2">
          <td>2</td>    <!-- id from DB -->
          <td>Test</td>
          <td>2</td>    <!-- order from DB  -->
          <td>
              <a>Edit</a>
              <a>Delete</a>
          </td>
      </tr>
      <tr id="3" data-order="3">
          <td>3</td>    <!-- id from DB -->
          <td>Test</td>
          <td>3</td>    <!-- order from DB  -->
          <td>
              <a>Edit</a>
              <a>Delete</a>
          </td>
      </tr>
  </tbody>
</table>
<form method="POST">
<input type="submit" name="update_order" value="Save" >
</form>

PHP script

if( isset($_POST['update_order']) ){
  global $wpdb;
  if(!empty($_POST['updated_rows'])) {
    $updated_rows_json = $_POST['updated_rows'];
    $updated_rows_ary = json_decode($updated_rows_json, true);
    foreach ($updated_rows_ary as $updated_row) {
      $wpdb->update('data', array("order" => $updated_row['updated_order']) , array('id' => $updated_row['id']));
    }
  }
}