Replies

• kthorngren Posts: 21,343Questions: 26Answers: 4,954

  April 26

  There are a couple issues:

  1. You are using objects not arrays for the row data. edit.triggerRow.data()[1] needs to be edit.triggerRow.data().sort_order and ${rowData[1]} needs to be ${rowData.sort_order}.
  2. position undefined (was undefined). These are the newData and oldData fields which are undefined. A quick forum search found this thread which was solved by setting rowReorder.dataSrc.

  Here is your code in a server side processing test case with the above updates:
  https://live.datatables.net/pojageyo/1/edit

  Kevin
• Brecht2727 Posts: 28Questions: 4Answers: 0

  April 26 edited April 26

  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?
• kthorngren Posts: 21,343Questions: 26Answers: 4,954

  April 26 edited April 26

  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:

  [
      {
          "id": 1,
          "order_id": 2
      },
      {
          "id": 12,
          "order_id": 3
      },
      {
          "id": 3,
          "order_id": 1
      }
  ]
  

  Then loop through the array and use MySql UPDATE to update each id with the changed order_id. I assume you set the rowReorder.dataSrc to order_id?

  Kevin
• Brecht2727 Posts: 28Questions: 4Answers: 0

  April 27

  I will post my code again to show you what i am trying to do but it is not good:

  For the jQuery:

    var table = new DataTable('#categoriesTable', {
      dom: "Bfrtilp",
      buttons: ["excel", "pdf"],
      processing: true,
      serverSide: true,
      serverMethod: 'post',
      rowReorder: {
        selector: '.reorder',
        dataSrc: 'order_id'
      },
      ajax: {
        url: 'assets/ajax/ajax-get-table-games-cats-overview.php'
      },
      //scrollX: !0,
      columns: [
        { data: 'id' },
        { data: 'test' },
        { data: 'cat_name' },
        { data: 'cat_icon' },      
        { data: 'active' },
        { data: 'order_id' },
        { data: 'actions' }
      ],
      columnDefs: [
        {
          className: 'reorder',
          render: () => '≡',
          targets: 1,
          orderable: false
        }
      ],    
      //order: [[5, "asc"]]
    });
  
    table.on('row-reorder', function (e, diff, edit) {
      console.log(edit.triggerRow.data());
      let result = 'Reorder started on row: ' + edit.triggerRow.data().cat_name + '<br>';
  
      for (var i = 0, ien = diff.length; i < ien; i++) {
          let rowData = table.row(diff[i].node).data();
  
          result +=
              `${rowData.cat_name} (id = ${rowData.id}) updated to be in position ${diff[i].newData} ` +
              `(was ${diff[i].oldData})<br>`;
  
          $.ajax({
            type: 'POST',
            url: 'assets/ajax/ajax-content-games-categories-change-order.php',
            dataType: 'json',
            cache: false,
            data: {id: rowData.id, newdata: diff[i].newData, olddata: diff[i].oldData}, 
            success: function (data) {
              console.log(data);
            },
            error: function (data) {
              console.log('An error occurred');
              console.log(data);
            }
          });
  
      }
  
      document.querySelector('#result123').innerHTML = 'Event result:<br>' + result;
    });
  

  For the ajax:

    $id = $_REQUEST['id'];
    $newdata = $_REQUEST['newdata'];
    $olddata = $_REQUEST['olddata'];
    
    $data = array(
      'order_id' => $newdata
    );
  
    $db->where('id', $id);
    $db->update('game_categories', $data);
  

  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'
• kthorngren Posts: 21,343Questions: 26Answers: 4,954

  April 27

  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:

      for (var i = 0, ien = diff.length; i < ien; i++) {
          let rowData = table.row(diff[i].node).data();
          update.push( {id: rowData.id,  order_id: diff[i].newData} )
      }
  

  Send that as that ajax.data. The server should receive JSON data like this:

  [
      {
          "id": 1,
          "order_id": 2
      },
      {
          "id": 12,
          "order_id": 3
      },
      {
          "id": 3,
          "order_id": 1
      }
  ]
  

  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 call draw() in the success function to update the row order from the DB.

  Kevin
• Brecht2727 Posts: 28Questions: 4Answers: 0

  April 27 edited April 27

  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.

    var table = new DataTable('#categoriesTable', {
      dom: "Bfrtilp",
      buttons: ["excel", "pdf"],
      processing: true,
      serverSide: true,
      serverMethod: 'post',
      rowReorder: {
        selector: '.reorder',
        dataSrc: 'id' 
      },
      ajax: {
        url: 'assets/ajax/ajax-get-table-games-cats-overview.php'
      },
      //scrollX: !0,
      columns: [
        { data: 'id' },
        { data: 'test' },
        { data: 'cat_name' },
        { data: 'cat_icon' },      
        { data: 'active' },
        { data: 'order_id' },
        { data: 'actions' }
      ],
      columnDefs: [
        {
          className: 'reorder',
          render: () => '≡',
          targets: 1,
          orderable: false
        }
      ],    
      order: [[5, "asc"]]
    });
  
    table.on('row-reorder', function (e, diff, edit) {
  
      var update = [];
      
      for (var i = 0, ien = diff.length; i < ien; i++) {
          let rowData = table.row(diff[i].node).data();
          update.push( {id: rowData.id,  order_id: diff[i].newData} )
      } 
      
          $.ajax({
            type: 'POST',
            url: 'assets/ajax/ajax-content-games-categories-change-order.php',
            dataType: 'json',
            cache: false,
            data: {update: update}, 
            success: function (data) {
              console.log(data);
              $('#categoriesTable').DataTable().ajax.reload( null, false );
            },
            error: function (data) {
              console.log('An error occurred');
              console.log(data);
            }
          });
    });
  

    $update = $_REQUEST['update'];
    
    foreach($update as $u) {
      $data = array(
        'order_id' => $u['order_id']
      );
  
      $db->where('id', $u['id']);
      $db->update('game_categories', $data);    
    }
  

  
• kthorngren Posts: 21,343Questions: 26Answers: 4,954

  April 27

  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
• Brecht2727 Posts: 28Questions: 4Answers: 0

  April 27 edited April 27

  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
• kthorngren Posts: 21,343Questions: 26Answers: 4,954

  April 27

  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
• Brecht2727 Posts: 28Questions: 4Answers: 0

  April 30

  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
• kthorngren Posts: 21,343Questions: 26Answers: 4,954

  April 30

  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
• Brecht2727 Posts: 28Questions: 4Answers: 0

  April 30 edited April 30

  Hi Kevin,

  Finally it works. Thanks for helping me!

  1. dataSrc must be set to 'order_id' and not to the auto_increment 'id' otherwise you will continue to get duplicate order_ids.
  2. I dropped the UNIQUE constraint off again.
  3. And when creating a new category the 'order_id' must find the last 'order_id' and do a +1

  Complete code below:

    var table = new DataTable('#categoriesTable', {
      dom: "Bfrtilp",
      buttons: ["excel", "pdf"],
      processing: true,
      serverSide: true,
      serverMethod: 'post',
      rowReorder: {
        selector: '.reorder',
        dataSrc: 'order_id' 
      },
      ajax: {
        url: 'assets/ajax/ajax-get-table-games-cats-overview.php'
      },
      //scrollX: !0,
      columns: [
        { data: 'id' },
        { data: 'test' },
        { data: 'cat_name' },
        { data: 'cat_icon' },      
        { data: 'active' },
        { data: 'order_id' },
        { data: 'actions' }
      ],
      columnDefs: [
        {
          className: 'reorder',
          render: () => '≡',
          targets: 1,
          orderable: false
        },  
        { 
          targets: [3,4,5,6],
          class: 'text-center'
        },
        { 
          targets: [3,6],
          orderable: false
        }
      ],
      order: [[5, "asc"]]
    });
  
    table.on('row-reorder', function (e, diff, edit) {
      var update = [];
      
      for (var i = 0, ien = diff.length; i < ien; i++) {
          let rowData = table.row(diff[i].node).data();
          update.push( {id: rowData.id,  order_id: diff[i].newData} );
      } 
      
      //console.log(update);
      
      $.ajax({
        type: 'POST',
        url: 'assets/ajax/ajax-content-games-categories-change-order.php',
        dataType: 'json',
        cache: false,
        data: {update: update}, 
        success: function (data) {
          $('#categoriesTable').DataTable().ajax.reload( null, false );
        },
        error: function (data) {
          console.log('An error occurred');
          console.log(data);
        }
      });
    });
  

  ajax-content-games-categories-change-order.php file contains:

    $update = $_REQUEST['update'];
    
    foreach($update as $u) {
      $data = array(
        'order_id' => $u['order_id']
      );
  
      $db->where('id', $u['id']);
      $db->update('game_categories', $data);    
    }