rowReorder and save the new sorting order in mysql table
rowReorder and save the new sorting order in mysql table
Brecht2727
Posts: 28Questions: 4Answers: 0
Hi, this is my table init:
var table = new DataTable('#categoriesTable', {
dom: "Bfrtilp",
buttons: ["excel", "pdf"],
processing: true,
serverSide: true,
serverMethod: 'post',
/*rowReorder: {
selector: '.reorder'
},*/
rowReorder: true,
ajax: {
url: 'assets/ajax/ajax-get-table-games-cats-overview.php'
},
//scrollX: !0,
columns: [
{ data: 'id' },
{ data: 'sort_order' },
{ data: 'cat_name' },
{ data: 'cat_icon' },
{ data: 'active' },
{ data: 'order_id' },
{ data: 'actions' }
],
columnDefs: [
{
className: 'reorder',
render: () => '≡',
targets: 1
},
],
order: [[0, "desc"]]
});
table.on('row-reorder', function (e, diff, edit) {
let result = 'Reorder started on row: ' + edit.triggerRow.data()[1] + '<br>';
for (var i = 0, ien = diff.length; i < ien; i++) {
let rowData = table.row(diff[i].node).data();
result +=
`${rowData[1]} updated to be in position ${diff[i].newData} ` +
`(was ${diff[i].oldData})<br>`;
}
document.querySelector('#result123').innerHTML = 'Event result:<br>' + result;
});
This is the result i am get back. Everything is undefined.
Event result:
Reorder started on row: undefined
undefined updated to be in position undefined (was undefined)
undefined updated to be in position undefined (was undefined)
undefined updated to be in position undefined (was undefined)
undefined updated to be in position undefined (was undefined)
Replies
There are a couple issues:
edit.triggerRow.data()[1]
needs to beedit.triggerRow.data().sort_order
and${rowData[1]}
needs to be${rowData.sort_order}
.position undefined (was undefined)
. These are thenewData
andoldData
fields which areundefined
. A quick forum search found this thread which was solved by settingrowReorder.dataSrc
.Here is your code in a server side processing test case with the above updates:
https://live.datatables.net/pojageyo/1/edit
Kevin
Hi Kevin, thanks for helping me out here.
I changed your code to what i need but now i am trying to save the new order into the mysql table with following fields:
id, cat_name, cat_icon, active, order_id
The order_id should be updated. Any idea?
Its hard to say without seeing what you are doing. I suspect you will want to use
$.ajax()
to send the JSON array of the changes to the server, for example:Then loop through the array and use MySql
UPDATE
to update eachid
with the changedorder_id
. I assume you set therowReorder.dataSrc
toorder_id
?Kevin
I will post my code again to show you what i am trying to do but it is not good:
For the jQuery:
For the ajax:
That is what i need to do: 'Then loop through the array and use MySql UPDATE to update each id with the changed order_id'
It looks like you are sending one ajax request per row. This might be too slow for a smooth update. I think you will want to create an array of updates, like I posted above, to send in one request. I think the loop would look like this:
Send that as that ajax.data. The server should receive JSON data like this:
It looks like you are using PHP. I'm not familiar with PHP so can't give an example.
You might also want to set
owReorder.update
false
with server side processing and calldraw()
in thesuccess
function to update the row order from the DB.Kevin
Yes, i think that your solution to send the JSON in one ajax call is better so i have updated my code.
But i have still a problem with all other ids that are also in the database and not updated so they are not in the JSON array. That brings me to duplicate order_id items.
That is possible if you don't start with unique values for the order_id. I don't know your situation nor data but you will probably need to reset the order_id field to have unique values. Then I would set the fields UNIQUE constraint to true. Once you have unique values then they should never duplicate as they are swapped in the RowReorder process which you see in the
diff
variable.Kevin
It is not clear to me. But if someone adds a new category the order_id is always 0 from the start. So if they add 10 new categories we have 10 times 0 in order_id field.
Can you explain more in detail what you want to say?
I am using MySQL database and PHP code
This article describes unique constraints. Without knowing your solution my suggestion is that you will need to use a query to get the highest numeric value in the order_id column then increment that to set the order_id value when adding a new category.
Kevin
Hi Kevin,
If I follow your idea, I still have duplicate entries in my database on save and because the CONSTRAINT is on that order_id field I get an error. P.e. if you want to update the first item order_id = 1 there is another order_id = 1 needs to be changed later in the array
That makes sense. So having the UNIQUE constraint won't work. Turn it off. If you start with a unique set of
order_id
values and only update them with the RowReorder process then they will stay unique. There will be duplicates for a short period while updating all the changes but once done there shouldn't be any duplicates.Kevin
Hi Kevin,
Finally it works. Thanks for helping me!
dataSrc
must be set to 'order_id' and not to the auto_increment 'id' otherwise you will continue to get duplicate order_ids.Complete code below:
ajax-content-games-categories-change-order.php file contains: