Drag and drop - save state to database

OSCART_Thomas

I have 3 columns in my database:
ID - Category - OrderNr

I want to change the order nr in the database by drag and drop the rows.

I have now the following code:

var table = $('#example').DataTable({
                responsive: true,
                "pageLength": 20,
                dom: "Tfrtip",
                ajax: "../dist/DataTables/PHP/PRODUCT_CATEGORY.php",
                columns: [{
                    data: null,
                    defaultContent: '',
                    orderable: false
                }, {
                    data: "ID",
                    orderable: false,
                    className: 'hidden'
                }, {
                    data: "Categorie",
                    orderable: false
                }, {
                    data: "Volgorde",
                    orderable: false,
                    className: 'hidden'
                }],
                order: [3, 'asc'],
                tableTools: {
                    sRowSelect: "os",
                    aButtons: [{
                        sExtends: "editor_create",
                        editor: editor
                    }, {
                        sExtends: "editor_remove",
                        editor: editor
                    }, {
                        sExtends: 'select_single',
                        sButtonText: 'First',
                        fnClick: function () {
                            if (table.row('.selected').length !== 0) {
                                PageMethods.setNewOrderNr(table.cell('.selected', 1).data(), "first", onSucess, onError);

                                function onSucess(result) {
                                    table.ajax.reload();
                                }

                                function onError(result) {
                                    alert('Something wrong.');
                                }
                            }
                        }
                    }, {
                        sExtends: 'select_single',
                        sButtonText: 'Higher',
                        fnClick: function () {
                            if (table.row('.selected').length !== 0) {
                                PageMethods.setNewOrderNr(table.cell('.selected', 1).data(), "higher", onSucess, onError);

                                function onSucess(result) {
                                    //alert(result);
                                    table.ajax.reload();
                                }

                                function onError(result) {
                                    alert('Something wrong.');
                                }
                            }
                        }
                    }, {
                        sExtends: 'select_single',
                        sButtonText: 'Lower',
                        fnClick: function () {
                            if (table.row('.selected').length !== 0) {
                                PageMethods.setNewOrderNr(table.cell('.selected', 1).data(), "lower", onSucess, onError);

                                function onSucess(result) {
                                    table.ajax.reload();
                                }

                                function onError(result) {
                                    alert('Something wrong.');
                                }
                            }
                        }
                    }, {
                        sExtends: 'select_single',
                        sButtonText: 'Last',
                        fnClick: function () {
                            if (table.row('.selected').length !== 0) {
                                PageMethods.setNewOrderNr(table.cell('.selected', 1).data(), "last", onSucess, onError);

                                function onSucess(result) {
                                    table.ajax.reload();
                                }

                                function onError(result) {
                                    alert('Something wrong.');
                                }
                            }
                        }
                    }]
                }
            });
            
            table.rowReordering({ 
                sURL:"../dist/DataTables/PHP/UpdateRowOrder.php", 
                sRequestType: "GET", 
                fnAlert: function(message) {
                    alert("order");
                },
                iIndexColumn: 1
            });

and this is the updateroworder.php file:

<?php
$id           = $_REQUEST['id'];
$fromPosition = is_array($_REQUEST['fromPosition']) ? $_REQUEST['fromPosition'][0] : $_REQUEST['fromPosition'];
$toPosition   = $_REQUEST['toPosition'];
$direction    = $_REQUEST['direction'];
$aPosition    = ($direction === "back") ? $toPosition+1 : $toPosition-1;
 
mysql_query("UPDATE tblcategorie SET Volgorde = 0 WHERE Volgorde = '".$toPosition."'");
mysql_query("UPDATE tblcategorie SET Volgorde = $toPosition WHERE ID = '".$id."'");
 
if($direction === "back") {
    mysql_query("UPDATE tblcategorie SET Volgorde = Volgorde + 1 WHERE ($toPosition <= Volgorde AND Volgorde <= $fromPosition) and ID != $id and Volgorde != 0 ORDER BY Volgorde DESC;");                     
} // backward direction
  
if($direction === "forward") {    
    mysql_query("UPDATE tblcategorie SET Volgorde = Volgorde - 1 WHERE ($fromPosition <= Volgorde AND Volgorde <= $toPosition) and ID != $id and Volgorde != 0 ORDER BY Volgorde ASC;");                        
} // Forward Direction
 
             
mysql_query("UPDATE tblcategorie SET Volgorde = $aPosition WHERE Volgorde = 0;");
?>

I can drag and drop the different rows, but the database won't change.
Can anybody tell me what I'm doing wrong?

Thanks for the help!