Written by Muhammad Taha
on March 10, 2019

Datatables: Saving Reordered Data into the Database

I’ve been working on improving a DBA that I built almost an year ago. And one requirement that came up was to be able to reorder the data entereed into the table. I’ve been using Datatables, which I should mention is a very useful tool and is customizable to a great extent. You can style it, extend it, control the rendering or make it work however you like. So, i looked up its extension catalog for something that could help me reorder the data and I found an extension that does exactly what I needed.

You can find the initialisation for it at the source website so I’m not going to get into that. Once however, you’ve enabled it you’ll see that you can now drag the rows however you want. But upon refreshing the table, you’ll notice that the data rows have gone back to their previous state. This happened because we never saved the reordered information anywhere. Also, if your row jumps back to its original place immediately after reordering it then check if you have ordering: set to false because that’s probably what’s causing that behaviour and setting it to true should fix it up. Anyway, what you need to do is to figure out a way to save that data to the existing table. We could use the id of the row and replace it everytime a row is re-ordered but the issue with this approach is that you don’t want to be messing with the id column ever. So, lets create a new column named serial in our database which copies the id of the newly created data so we could use the serial column for manipulation rather than the id. For that, lets create a new trigger in mysql with this query:

CREATE TRIGGER `this_trigger_name` BEFORE INSERT ON `table_name`
      FOR EACH ROW SET NEW.`serial_column` = (
            SELECT AUTO_INCREMENT 
            FROM information_schema.TABLES 
            WHERE TABLE_SCHEMA = DATABASE() 
            AND TABLE_NAME = 'design'
      )

This will copy the data from id to serial on every new data creation. Now, to get the re-ordered data from the table:

Create a variable to hold the datatables row data like so: var rowData = oTable.row( diff[i].node ).data();
Get the original id of the row you’re reordering like so: rowData['id'] and the new serial like this: diff[i].newData
Send both this data variables to a php script via AJAX (jQuery)
Keep all of this in a for loop

Of course, you’ll be putting this in a function and you’ll want to call that function everytime a row is reordered. we can use events for that. Lets use the row-reordered event like so:

yourDataTable.on('row-reordered', function ( e, diff, edit) {
    for (var i = 0; i < diff.length; i++) {
        var rowData = yourDataTable.row( diff[i].node ).data();
        $.post("/link-to-script.php", {
            oldData: rowData['id'],
            newData: diff[i].newData
        }, function(data, status) {
            //empty
        });
    }
});

There’s a big problem with this, however. You’ll be calling the server everytime you displace the row. So for example, if you displace the row 10 places down, the AJAX call will be placed 10 times. And this seems wasteful, not to mention it’ll needlessly tax your server. This happens even though the row-reordered event docs contrast it to row-reorder event as only being fired when you have completed the reordering process. I don’t know maybe I don’t understand the documentation correctly or whatever but anyways here’s the modified js code along with the steps:

Create a global variable (I know, but I couldn’t find any other way) to hold the reorder information. Call it serialsDict.
Add this code that utilizes the draw event (which is fired every time the row is done reordering among other times):

yourDataTable.on('row-reorder', function ( e, diff, edit) {
    for (var i = 0; i < diff.length; i++) {
        var rowData = yourDataTable.row( diff[i].node ).data();
        serialsDict.push({
            oldData: rowData['id'],
            newData: diff[i].newData
        });
    }
});

yourDataTable.on('draw', function () {
    if (serialsDict.length) {
        $.post("/link-to-script.php", {
        serialsDict: serialsDict
        }, function(data, status) {
            serialsDict = [];
        })
    } ;
});

We’re using the if statement to combat unnecessary AJAX calls because the draw event is pretty common and we don’t want to call the order update script unnecessarily.

Now, on to the PHP script to handle the AJAX call:

$object = new CRUD($pdo, 'design', 'id');
            foreach ($_POST['serialsDict'] as $rowId) {
                $object->update(['id' => $rowId['oldData'],
                'serial' => $rowId['newData']]);
          }

The PHP code is using custom ORM that provides basic SQL injection protection and a few OOP-styled database calls but essentially I’m running this sql script behind this update function:

$query = 'UPDATE `serial` = 'newData' WHERE `id` = 'oldData''

And there you have it! I’m pretty sure the code can be made better but this solution works well and isn’t very ugly either so I guess it suits me.

Hope this helps :)

← → Top