Tablednd - using returned JSON data in table

I'm making improvements to a wishlist plugin so the user can change the order of the table by drag and drop.

I've got jQuery Tablednd working with JSON data being returned after my ajax call and it's working. But I really can't get my head around what to do with json data so that the page returns with the updated list on refresh. I'm hoping someone can push me in the right direction as I think I'm searching for the wrong terms. I'm using Wordpress so the calls to AJAX are using actions,

Table

<table id="tinvwl-sort" class="tinvwl-table-manage-list">
  <tbody>
    <tr id="153" style="cursor: move; display: table-row;">
      item 1 - many columns of data
    </tr>
    <tr id="152" style="cursor: move;">
      item 2 - many columns of data
    </tr>
    <tr id="151" style="cursor: move;">
      item 3 - many columns of data
    </tr>
  </tbody>
</table>
<div id="feedback"></div>

JQuery

jQuery("#tinvwl-sort").tableDnD({

    onDrop: function(table, row) {
        var serial = jQuery.tableDnD.jsonize();

    // This does the ajax request
    jQuery.ajax({

        url: example_ajax_obj.ajaxurl,
        data: {
            'action': 'example_ajax_request',
            'order' : serial
        },
        success:function(data) {
            console.log(data);
            jQuery("#feedback").html(data);
        },
        error: function(errorThrown){
            console.log(errorThrown);
        }
    });

AJAX PHP

function example_ajax_request() {

    // The $_REQUEST contains all the data sent via ajax
    if ( isset($_REQUEST) ) {

        $order = $_REQUEST['order'];

        // Let's take the data that was sent and do something with it


       echo $order;

    }
   die();
}

Result in #feedback

{\"tinvwl-sort\":[\"153\",\"152\",\"151\"]}

I know I need to save this data to a database but struggling to work out how I can use the created JSON data to do it. Any direction would be appreciated as feel I have exhausted every google search possible and think I must of got the wrong end of stick somewhere.

Many thanks for any answer, Peersy

Solution

I think the key is understanding how are you storing the order of the items in your db at the moment. For example if you've got an SQL table called items, you might arrange it like this:

ID, OwnerID, ItemDescription
151, 543, "Visit Niagara Falls"
152, 543, "Visit London"
153, 543, "Visit Mumbia"
154, 989, "Eat ice-cream in Venice"

Then unfortunately you're not storing the order of the items and so the order they'll be displayed will be "undefined" i.e. it could be any order (or probably just in ID order which will probably be the order they created them in.

So you have to add a new Order column as follows:

ID, OwnerID, Order, ItemDescription
151, 543, 1, "Visit Niagara Falls"
152, 543, 2, "Visit London"
153, 543, 3, "Visit Mumbia"
154, 989, 1, "Eat ice-cream in Venice"

Once you've got that, you can easily ensure that you display the items in preference order by adding ORDER BY Order to your SQL query.

But what you can now do, assuming you get back the JSON from your table as you showed,

{\"tinvwl-sort\":[\"153\",\"152\",\"151\"]}

is update the values of the order column so that 153 -> 1, 152 -> 2, 151 -> 3. Then when you next fetch the items in the wish list, they'll be in the new order. You can use json_decode($order) in PHP and then you'll have an object with a tinvwl-sort property whose value is the array in the order you need it.

I hope this helps!