Editor serverside php/Mysql

stoads

Hi, I'm new on dataTable Editor and I have a hard time using it.
I try to do the same sql request: 'SELECT DISTINCT datePrice FROM prices WHERE datePrice!=CURDATE() ORDER BY datePrice DESC LIMIT 3'

How can I do? Thanks

allan

Hi,

I'm afraid you can't use a DISTINCT with Editor at this time, but you can use where conditions, as described here.

Allan

stoads

Thanks Allan.
Can I do a where condition after a leftjoin or Not?

allan

Yes - the documentation I linked to above shows how to do a WHERE condition, and that supports left joined tables.

Allan

stoads

Hi Allan,
I try where condition but I have an error, here my code:

Editor::inst($db, 'articles')
        ->field(
        Field::inst('articles.product')
                ->options('products', 'id', 'name'),
        Field::inst('products.name'),
        Field::inst('articles.specifications'),
        Field::inst('articles.packaging'),
        Field::inst('articles.pf'),
        Field::inst('articles.country'),
        Field::inst('prices.datePrice'),
        Field::inst('prices.tel'),
        Field::inst('prices.cours1'),
        Field::inst('prices.cours2'),
        Field::inst('prices.cours3'),
        Field::inst('prices.cours4'),
        Field::inst('prices.cours5'),
        Field::inst('prices.cours6'),
        Field::inst('prices.cours7')
        )
        ->leftJoin('products', 'products.id', '=', 'articles.product')
        ->leftJoin('prices', 'prices.article', '=', 'articles.id')
        ->where( function ( $q ) {
               $q->where('datePrice', '(select article, max(datePrice) as currentPrice from prices group by article)', 'IN', false);
        })
        ->process('$_POST')
        ->json();

An error:

SQLSTATE[21000]: Cardinality violation: 1241 Operand should contain 1 column(s)

For information I want to group by article and only the last date or 3 past date.

Thanks.

stoads

I change where condition like this and for the last date it's work very well:

->where( function ( $q ) {
               $q->where('datePrice', '(select max(datePrice) as currentPrice from prices)', 'IN', false);
        })

Remains to find for the last three dates...

allan

I'm afraid I'm no SQL expert. A select statement with an order by and limit would be my approach, but for general SQL questions you might be best asking on StackOverflow or similar.

Allan

stoads

Hi Allan,
the sql statement work find on php 5 and MySql but it's not the same with datatable.

It reading well but the writing not. I forgot something?

allan

Just to confirm, the data read is working as you expect, but not the write? What do you expect to happen on the write? Can you link to the page so I can understand better what it is you are trying to achieve and what is going wrong please? I'm not quite getting it at the moment.

Thanks,
Allan

stoads

Hi Allan,
Did you receive my answer? I think I have a problem when sending my Post comment

allan

I'm afraid the only information I've received is that shown above.

Allan

stoads

the php file:

// DataTables PHP library
include_once (../php/DataTables.php');
include_once (../php/Editor/Mjoin.php');

use DataTables\Editor,
    DataTables\Editor\Field,
    DataTables\Editor\Format,
    DataTables\Editor\Join,
    DataTables\Editor\Mjoin,
    DataTables\Editor\Upload,
    DataTables\Editor\Validate;

$editor = Editor::inst($db, 'prices')
        ->fields(
         Field::inst('articles.product')
                 ->options('products', 'id', 'name'),
         Field::inst('products.name')
                ->validator( 'Validate::dbValues' ),
         Field::inst('articles.specifications')
                ->validator('Validate::dbValues')
                ->setFormatter( 'Format::ifEmpty', null ),
         Field::inst('articles.packaging')
                ->validator('Validate::dbValues')
                ->setFormatter( 'Format::ifEmpty', null ),
         Field::inst('articles.pf')
                ->validator( 'Validate::dbValues' )
                ->setFormatter( 'Format::ifEmpty', null ),
         Field::inst('articles.country')
                ->validator('Validate::dbValues')
                ->setFormatter( 'Format::ifEmpty', null ),
         Field::inst('prices.datePrice')
                ->validator( 'Validate::dateFormat', array(
                "empty"   => true,
                "format"  => Format::DATE_ISO_8601,
                "message" => "Please enter a date in the format yyyy-mm-dd"
               ) )
               ->getFormatter( 'Format::date_sql_to_format', Format::DATE_ISO_850 )
               ->setFormatter( 'Format::date_format_to_sql', Format::DATE_ISO_8601 ),
         Field::inst('prices.tel'),
         Field::inst('prices.cours1')
                ->validator( 'Validate::dbValues' )
                ->setFormatter( 'Format::ifEmpty', null ),
         Field::inst('prices.cours2')
                ->validator( 'Validate::dbValues' )
                ->setFormatter( 'Format::ifEmpty', null ),
         Field::inst('prices.cours3')
                ->validator( 'Validate::dbValues' )
                ->setFormatter( 'Format::ifEmpty', null ),
         Field::inst('prices.cours4')
                ->validator( 'Validate::dbValues' )
                ->setFormatter( 'Format::ifEmpty', null ),
         Field::inst('prices.cours5')
                ->validator( 'Validate::dbValues' )
                ->setFormatter( 'Format::ifEmpty', null ),
         Field::inst('prices.cours6')
                ->validator( 'Validate::dbValues' )
                ->setFormatter( 'Format::ifEmpty', null ),
         Field::inst('prices.cours7')
                ->validator( 'Validate::dbValues' )
                ->setFormatter( 'Format::ifEmpty', null )
        )
        ->leftJoin('articles', 'articles.id', '=', 'prices.article')
        ->leftJoin('products', 'products.id', '=', 'articles.product')
        ->where( function ( $q ) {
               $q->where('datePrice', '(select max(datePrice) as currentPrice from prices)', 'IN', false);
        })
        ->process('$_POST')
        ->json();

Thanks for your help.

allan

Thanks for the code. Could you possibly also answer my questions from above please:

Just to confirm, the data read is working as you expect, but not the write? What do you expect to happen on the write? Can you link to the page so I can understand better what it is you are trying to achieve and what is going wrong please?

Thanks,
Allan

stoads

Hi Allan,
On my page I can see the data send by the server. But when I change one value nothing to do just the date change but the where condition remove the past dates and I loose all that I want.
Is it possible to make the where condition only to the creation of the editor?
Thanks

allan

Is it possible to make the where condition only to the creation of the editor?

As in when the data is fetched? Yes, you can use the Editor::action() method to determine if the action is get - e.g.:

if ( Editor::action( $_POST ) === Editor::ACTION_READ ) {
  ... apply `where` condition
}

Allan

stoads

Ok I try this tomorrow.
And for set the new values I forgot Something?

allan

I'm afraid I don't understand your last question. You asked about applying the where condition only when the Editor instance is created. I assumed you mean when the data is fetched.

Could you clarify the issue - I'm afraid I'm not really understanding at the moment.

Allan

stoads

When I change value on cell I want to refresh the data base (SQL) and dataTable.
At this time, when I change one value on editor, the value is push on PHP file but nothing it's refresh.

JS code:

var editor; // use a global for the submit and return data rendering in the examples

$(function () {

   editor = new $.fn.dataTable.Editor({
      ajax: "../private/server/ctrl_dataTable.php",
      table: "#articlesMarketing",
      fields: [{
            label: "Name ",
            name: "articles.product",
            type: "select"
         }, {
            label: "Fish specifications",
            name: "articles.specifications"
         }, {
            label: "Packaging ",
            name: "articles.packaging"
         }, {
            label: "PF ",
            name: "articles.pf",
            type: "checkbox",
            separator: "|",
            options: [
               {label: '', value: 1}
            ]
         }, {
            label: "Country ",
            name: "articles.country"
         }, {
            label: "Date ",
            name: "prices.datePrice",
            type: "datetime"
         }, {
            label: "Phone ",
            name: "prices.tel",
            type: "checkbox",
            separator: "|",
            options: [
               {label: '', value: 1}
            ]
         }, {
            label: "Cours I ",
            name: "prices.cours1"
         }, {
            label: "Cours II ",
            name: "prices.cours2"
         }, {
            label: "Cours III ",
            name: "prices.cours3"
         }, {
            label: "Cours IV ",
            name: "prices.cours4"
         }, {
            label: "Cours V ",
            name: "prices.cours5"
         }, {
            label: "Cours VI ",
            name: "prices.cours6"
         }, {
            label: "Cours VII ",
            name: "prices.cours7"
         }
      ]
   });


   var table = $('#articlesMarketing').DataTable({
      dom: "<'row'<'container'<'col-md-12'B>>>" + "<'row'<'container'<'col-md-6'l><'col-md-6'f>>>" + "<'row'<'container'<'col-md-12'rtipH>>>",
      ajax: {
         url: "../private/server/ctrl_dataTable.php",
         type: "POST"
      },
      lengthMenu: [[10, 25, 50, -1], [10, 25, 50, "All"]],
      scrollX: true,
      scrollY: '100vh',
      scrollCollapse: true,
      columns: [
         {
                data: null,
                defaultContent: '',
                className: 'select-checkbox',
                orderable: false
          },
         {data: "products.name"},
         {data: "articles.specifications"},
         {data: "articles.packaging"},
         {
            data: "articles.pf",
            render: function (data, type, row) {
               if (type === 'display') {
                  return '<input type="checkbox" class="selectedPf">';
               }
               return data;
            }
         },
         {data: "articles.country"},
         {data: "prices.datePrice"},
         {
            data: "prices.tel",
            render: function (data, type, row) {
               if (type === 'display') {
                  return '<input type="checkbox" class="selectedTel">';
               }
               return data;
            }
         },
         {data: "prices.cours1", render: $.fn.dataTable.render.number(',', '.', 0, '€')},
         {data: "prices.cours2", render: $.fn.dataTable.render.number(',', '.', 0, '€')},
         {data: "prices.cours3", render: $.fn.dataTable.render.number(',', '.', 0, '€')},
         {data: "prices.cours4", render: $.fn.dataTable.render.number(',', '.', 0, '€')},
         {data: "prices.cours5", render: $.fn.dataTable.render.number(',', '.', 0, '€')},
         {data: "prices.cours6", render: $.fn.dataTable.render.number(',', '.', 0, '€')},
         {data: "prices.cours7", render: $.fn.dataTable.render.number(',', '.', 0, '€')}
      ],
      autoWidth: true,
      order: [1, 'asc'],
      keys: {
            columns: ':not(:first-child)',
            keys: [ 9 ]
        },
      select: {
         style: 'os',
         selector: 'td:first-child'
      },
      buttons: [
         {extend: "create", editor: editor},
         {extend: "remove", editor: editor}
      ],
      rowCallback: function (row, data) {
         // Set the checked state of the checkbox in the table
         if ($('input.selectedTel', row)) {
            $('input.selectedTel', row).prop('checked', parseInt(data.prices.tel));
         }

         if ($('input.selectedPf', row)) {
            $('input.selectedPf', row).prop('checked', parseInt(data.articles.pf));
         }
      }
   });

   // Inline editing on click
   $('#articlesMarketing').on('click', 'tbody td:not(:first-child)', function (e) { 
      editor.inline(this);
   });
   
   // Inline editing on tab focus
   table.on('key-focus', function (e, datatable, cell) {
      editor.inline(cell.index(), {
         onBlur: 'submit'
      });
   });
   
});

PHP code:

$editor = Editor::inst($db, 'prices')
        ->fields(
         Field::inst('articles.product')
                 ->options('products', 'id', 'name'),
         Field::inst('products.name')
                ->validator( 'Validate::notEmpty' ),
         Field::inst('articles.specifications')
                ->setFormatter( 'Format::ifEmpty', null ),
         Field::inst('articles.packaging'),
         Field::inst('articles.pf'),
         Field::inst('articles.country'),
         Field::inst('prices.datePrice')
                ->validator( 'Validate::dateFormat', array(
                "empty"   => true,
                "format"  => Format::DATE_ISO_8601,
                "message" => "Please enter a date in the format yyyy-mm-dd"
               ) )
               ->getFormatter( 'Format::date_sql_to_format', Format::DATE_ISO_850 )
               ->setFormatter( 'Format::date_format_to_sql', Format::DATE_ISO_8601 ),
         Field::inst('prices.tel'),
         Field::inst('prices.cours1')
                ->validator( 'Validate::numeric' )
                ->setFormatter( 'Format::ifEmpty', null ),
         Field::inst('prices.cours2')
                ->validator( 'Validate::numeric' )
                ->setFormatter( 'Format::ifEmpty', null ),
         Field::inst('prices.cours3')
                ->validator( 'Validate::numeric' )
                ->setFormatter( 'Format::ifEmpty', null ),
         Field::inst('prices.cours4')
                ->validator( 'Validate::numeric' )
                ->setFormatter( 'Format::ifEmpty', null ),
         Field::inst('prices.cours5')
                ->validator( 'Validate::numeric' )
                ->setFormatter( 'Format::ifEmpty', null ),
         Field::inst('prices.cours6')
                ->validator( 'Validate::numeric' )
                ->setFormatter( 'Format::ifEmpty', null ),
         Field::inst('prices.cours7')
                ->validator( 'Validate::numeric' )
                ->setFormatter( 'Format::ifEmpty', null )
        )
        ->leftJoin('articles', 'articles.id', '=', 'prices.article')
        ->leftJoin('products', 'products.id', '=', 'articles.product')
        ->where( function ( $q ) {
               $q->where('datePrice', '(select max(datePrice) as currentPrice from prices)', 'IN', false);
        })
        ->process('$_POST')
        ->json();

I don't know if I can use setValue?

stoads

At this time when I change a value cell on editor it don't refresh the data base (Sql) and the datatable.
here the code:
js file:

var editor; // use a global for the submit and return data rendering in the examples

$(function () {

   editor = new $.fn.dataTable.Editor({
      ajax: "../private/server/ctrl_dataTable.php",
      table: "#articlesMarketing",
      fields: [{
            label: "Name ",
            name: "articles.product",
            type: "select"
         }, {
            label: "Fish specifications",
            name: "articles.specifications"
         }, {
            label: "Packaging ",
            name: "articles.packaging"
         }, {
            label: "PF ",
            name: "articles.pf",
            type: "checkbox",
            separator: "|",
            options: [
               {label: '', value: 1}
            ]
         }, {
            label: "Country ",
            name: "articles.country"
         }, {
            label: "Date ",
            name: "prices.datePrice",
            type: "datetime"
         }, {
            label: "Phone ",
            name: "prices.tel",
            type: "checkbox",
            separator: "|",
            options: [
               {label: '', value: 1}
            ]
         }, {
            label: "Cours I ",
            name: "prices.cours1"
         }, {
            label: "Cours II ",
            name: "prices.cours2"
         }, {
            label: "Cours III ",
            name: "prices.cours3"
         }, {
            label: "Cours IV ",
            name: "prices.cours4"
         }, {
            label: "Cours V ",
            name: "prices.cours5"
         }, {
            label: "Cours VI ",
            name: "prices.cours6"
         }, {
            label: "Cours VII ",
            name: "prices.cours7"
         }
      ]
   });

   // ************************************************* /
   // **************** DATA TABLE ********************/
   // ************************************************* /
   var table = $('#articlesMarketing').DataTable({
      dom: "<'row'<'container'<'col-md-12'B>>>" + "<'row'<'container'<'col-md-6'l><'col-md-6'f>>>" + "<'row'<'container'<'col-md-12'rtipH>>>",
      ajax: {
         url: "../private/server/ctrl_dataTable.php",
         type: "POST"
      },
      lengthMenu: [[10, 25, 50, -1], [10, 25, 50, "All"]],
      scrollX: true,
      scrollY: '100vh',
      scrollCollapse: true,
      columns: [
         {
                data: null,
                defaultContent: '',
                className: 'select-checkbox',
                orderable: false
          },
         {data: "products.name"},
         {data: "articles.specifications"},
         {data: "articles.packaging"},
         {
            data: "articles.pf",
            render: function (data, type, row) {
               if (type === 'display') {
                  return '<input type="checkbox" class="selectedPf">';
               }
               return data;
            }
         },
         {data: "articles.country"},
         {data: "prices.datePrice"},
         {
            data: "prices.tel",
            render: function (data, type, row) {
               if (type === 'display') {
                  return '<input type="checkbox" class="selectedTel">';
               }
               return data;
            }
         },
         {data: "prices.cours1", render: $.fn.dataTable.render.number(',', '.', 0, '€')},
         {data: "prices.cours2", render: $.fn.dataTable.render.number(',', '.', 0, '€')},
         {data: "prices.cours3", render: $.fn.dataTable.render.number(',', '.', 0, '€')},
         {data: "prices.cours4", render: $.fn.dataTable.render.number(',', '.', 0, '€')},
         {data: "prices.cours5", render: $.fn.dataTable.render.number(',', '.', 0, '€')},
         {data: "prices.cours6", render: $.fn.dataTable.render.number(',', '.', 0, '€')},
         {data: "prices.cours7", render: $.fn.dataTable.render.number(',', '.', 0, '€')}
      ],
      autoWidth: true,
      order: [1, 'asc'],
      keys: {
            columns: ':not(:first-child)',
            keys: [ 9 ]
        },
      select: {
         style: 'os',
         selector: 'td:first-child'
      },
      buttons: [
         {extend: "create", editor: editor},
         {extend: "remove", editor: editor}
      ],
      rowCallback: function (row, data) {
         // Set the checked state of the checkbox in the table
         if ($('input.selectedTel', row)) {
            $('input.selectedTel', row).prop('checked', parseInt(data.prices.tel));
         }

         if ($('input.selectedPf', row)) {
            $('input.selectedPf', row).prop('checked', parseInt(data.articles.pf));
         }
      }
   });

   // Inline editing on click
   $('#articlesMarketing').on('click', 'tbody td:not(:first-child)', function (e) { 
      editor.inline(this);
   });
   
   // Inline editing on tab focus
   table.on('key-focus', function (e, datatable, cell) {
      editor.inline(cell.index(), {
         onBlur: 'submit'
      });
   });
   
});

php file:

$editor = Editor::inst($db, 'prices')
        ->fields(
         Field::inst('articles.product')
                 ->options('products', 'id', 'name'),
         Field::inst('products.name')
                ->validator( 'Validate::notEmpty' ),
         Field::inst('articles.specifications')
                ->setFormatter( 'Format::ifEmpty', null ),
         Field::inst('articles.packaging'),
         Field::inst('articles.pf'),
         Field::inst('articles.country'),
         Field::inst('prices.datePrice')
                ->validator( 'Validate::dateFormat', array(
                "empty"   => true,
                "format"  => Format::DATE_ISO_8601,
                "message" => "Please enter a date in the format yyyy-mm-dd"
               ) )
               ->getFormatter( 'Format::date_sql_to_format', Format::DATE_ISO_850 )
               ->setFormatter( 'Format::date_format_to_sql', Format::DATE_ISO_8601 ),
         Field::inst('prices.tel'),
         Field::inst('prices.cours1')
                ->validator( 'Validate::numeric' )
                ->setFormatter( 'Format::ifEmpty', null ),
         Field::inst('prices.cours2')
                ->validator( 'Validate::numeric' )
                ->setFormatter( 'Format::ifEmpty', null ),
         Field::inst('prices.cours3')
                ->validator( 'Validate::numeric' )
                ->setFormatter( 'Format::ifEmpty', null ),
         Field::inst('prices.cours4')
                ->validator( 'Validate::numeric' )
                ->setFormatter( 'Format::ifEmpty', null ),
         Field::inst('prices.cours5')
                ->validator( 'Validate::numeric' )
                ->setFormatter( 'Format::ifEmpty', null ),
         Field::inst('prices.cours6')
                ->validator( 'Validate::numeric' )
                ->setFormatter( 'Format::ifEmpty', null ),
         Field::inst('prices.cours7')
                ->validator( 'Validate::numeric' )
                ->setFormatter( 'Format::ifEmpty', null )
        )
        ->leftJoin('articles', 'articles.id', '=', 'prices.article')
        ->leftJoin('products', 'products.id', '=', 'articles.product')
        ->where( function ( $q ) {
               $q->where('datePrice', '(select max(datePrice) as currentPrice from prices)', 'IN', false);
        })
        ->process('$_POST')
        ->json();

I don't know if I may use setValue or not?
Thanks.

stoads

At this time when I change a cell value on editor datatable it don't refresh the data base (sql) and the value on editor datatable.

here my code...

var editor; // use a global for the submit and return data rendering in the examples

$(function () {

   editor = new $.fn.dataTable.Editor({
      ajax: "../private/server/ctrl_dataTable.php",
      table: "#articlesMarketing",
      fields: [{
            label: "Name ",
            name: "articles.product",
            type: "select"
         }, {
            label: "Fish specifications",
            name: "articles.specifications"
         }, {
            label: "Packaging ",
            name: "articles.packaging"
         }, {
            label: "PF ",
            name: "articles.pf",
            type: "checkbox",
            separator: "|",
            options: [
               {label: '', value: 1}
            ]
         }, {
            label: "Country ",
            name: "articles.country"
         }, {
            label: "Date ",
            name: "prices.datePrice",
            type: "datetime"
         }, {
            label: "Phone ",
            name: "prices.tel",
            type: "checkbox",
            separator: "|",
            options: [
               {label: '', value: 1}
            ]
         }, {
            label: "Cours I ",
            name: "prices.cours1"
         }, {
            label: "Cours II ",
            name: "prices.cours2"
         }, {
            label: "Cours III ",
            name: "prices.cours3"
         }, {
            label: "Cours IV ",
            name: "prices.cours4"
         }, {
            label: "Cours V ",
            name: "prices.cours5"
         }, {
            label: "Cours VI ",
            name: "prices.cours6"
         }, {
            label: "Cours VII ",
            name: "prices.cours7"
         }
      ]
   });

   var table = $('#articlesMarketing').DataTable({
      dom: "<'row'<'container'<'col-md-12'B>>>" + "<'row'<'container'<'col-md-6'l><'col-md-6'f>>>" + "<'row'<'container'<'col-md-12'rtipH>>>",
      ajax: {
         url: "../private/server/ctrl_dataTable.php",
         type: "POST"
      },
      lengthMenu: [[10, 25, 50, -1], [10, 25, 50, "All"]],
      scrollX: true,
      scrollY: '100vh',
      scrollCollapse: true,
      columns: [
         {
                data: null,
                defaultContent: '',
                className: 'select-checkbox',
                orderable: false
          },
         {data: "products.name"},
         {data: "articles.specifications"},
         {data: "articles.packaging"},
         {
            data: "articles.pf",
            render: function (data, type, row) {
               if (type === 'display') {
                  return '<input type="checkbox" class="selectedPf">';
               }
               return data;
            }
         },
         {data: "articles.country"},
         {data: "prices.datePrice"},
         {
            data: "prices.tel",
            render: function (data, type, row) {
               if (type === 'display') {
                  return '<input type="checkbox" class="selectedTel">';
               }
               return data;
            }
         },
         {data: "prices.cours1", render: $.fn.dataTable.render.number(',', '.', 0, '€')},
         {data: "prices.cours2", render: $.fn.dataTable.render.number(',', '.', 0, '€')},
         {data: "prices.cours3", render: $.fn.dataTable.render.number(',', '.', 0, '€')},
         {data: "prices.cours4", render: $.fn.dataTable.render.number(',', '.', 0, '€')},
         {data: "prices.cours5", render: $.fn.dataTable.render.number(',', '.', 0, '€')},
         {data: "prices.cours6", render: $.fn.dataTable.render.number(',', '.', 0, '€')},
         {data: "prices.cours7", render: $.fn.dataTable.render.number(',', '.', 0, '€')}
      ],
      autoWidth: true,
      order: [1, 'asc'],
      keys: {
            columns: ':not(:first-child)',
            keys: [ 9 ]
        },
      select: {
         style: 'os',
         selector: 'td:first-child'
      },
      buttons: [
         {extend: "create", editor: editor},
         {extend: "remove", editor: editor}
      ],
      rowCallback: function (row, data) {
         // Set the checked state of the checkbox in the table
         if ($('input.selectedTel', row)) {
            $('input.selectedTel', row).prop('checked', parseInt(data.prices.tel));
         }

         if ($('input.selectedPf', row)) {
            $('input.selectedPf', row).prop('checked', parseInt(data.articles.pf));
         }
      }
   });

   // Inline editing on click
   $('#articlesMarketing').on('click', 'tbody td:not(:first-child)', function (e) { 
      editor.inline(this);
   });
   
   // Inline editing on tab focus
   table.on('key-focus', function (e, datatable, cell) {
      editor.inline(cell.index(), {
         onBlur: 'submit'
      });
   });
   
});

stoads

Hi Allan,
On write, I'm try to set the new value on data base (sql) but it don't work. Here my code.

var editor; // use a global for the submit and return data rendering in the examples

   editor = new $.fn.dataTable.Editor({
      ajax: "../private/server/ctrl_dataTable.php",
      table: "#articlesMarketing",
      fields: [{
            label: "Name ",
            name: "articles.product",
            type: "select"
         }, {
            label: "Fish specifications",
            name: "articles.specifications"
         }, {
            label: "Packaging ",
            name: "articles.packaging"
         }, {
            label: "PF ",
            name: "articles.pf",
            type: "checkbox",
            separator: "|",
            options: [
               {label: '', value: 1}
            ]
         }, {
            label: "Country ",
            name: "articles.country"
         }, {
            label: "Date ",
            name: "prices.datePrice",
            type: "datetime"
         }, {
            label: "Phone ",
            name: "prices.tel",
            type: "checkbox",
            separator: "|",
            options: [
               {label: '', value: 1}
            ]
         }, {
            label: "Cours I ",
            name: "prices.cours1"
         }, {
            label: "Cours II ",
            name: "prices.cours2"
         }, {
            label: "Cours III ",
            name: "prices.cours3"
         }, {
            label: "Cours IV ",
            name: "prices.cours4"
         }, {
            label: "Cours V ",
            name: "prices.cours5"
         }, {
            label: "Cours VI ",
            name: "prices.cours6"
         }, {
            label: "Cours VII ",
            name: "prices.cours7"
         }
      ]
   });

   var table = $('#articlesMarketing').DataTable({
      dom: "<'row'<'container'<'col-md-12'B>>>" + "<'row'<'container'<'col-md-6'l><'col-md-6'f>>>" + "<'row'<'container'<'col-md-12'rtipH>>>",
      //B:button, l:lengthmenu, f:filter, r:processing, t:table, i:information, p:pagination, H: jQueryUI theme "header" classes
      ajax: {
         url: "../private/server/ctrl_dataTable.php",
         type: "POST"
      },
      lengthMenu: [[10, 25, 50, -1], [10, 25, 50, "All"]],
      scrollX: true,
      scrollY: '100vh',
      scrollCollapse: true,
      columns: [
         {
                data: null,
                defaultContent: '',
                className: 'select-checkbox',
                orderable: false
          },
         {data: "products.name"},
         {data: "articles.specifications"},
         {data: "articles.packaging"},
         {
            data: "articles.pf",
            render: function (data, type, row) {
               if (type === 'display') {
                  return '<input type="checkbox" class="selectedPf">';
               }
               return data;
            }
         },
         {data: "articles.country"},
         {data: "prices.datePrice"},
         {
            data: "prices.tel",
            render: function (data, type, row) {
               if (type === 'display') {
                  return '<input type="checkbox" class="selectedTel">';
               }
               return data;
            }
         },
         {data: "prices.cours1", render: $.fn.dataTable.render.number(',', '.', 0, '€')},
         {data: "prices.cours2", render: $.fn.dataTable.render.number(',', '.', 0, '€')},
         {data: "prices.cours3", render: $.fn.dataTable.render.number(',', '.', 0, '€')},
         {data: "prices.cours4", render: $.fn.dataTable.render.number(',', '.', 0, '€')},
         {data: "prices.cours5", render: $.fn.dataTable.render.number(',', '.', 0, '€')},
         {data: "prices.cours6", render: $.fn.dataTable.render.number(',', '.', 0, '€')},
         {data: "prices.cours7", render: $.fn.dataTable.render.number(',', '.', 0, '€')}
      ],
      autoWidth: true,
      order: [1, 'asc'],
      keys: {
            columns: ':not(:first-child)',
            keys: [ 9 ]
        },
      select: {
         style: 'os',
         selector: 'td:first-child'
      },
      buttons: [
         {extend: "create", editor: editor},
         {extend: "remove", editor: editor}
      ],
      rowCallback: function (row, data) {
         // Set the checked state of the checkbox in the table
         if ($('input.selectedTel', row)) {
            $('input.selectedTel', row).prop('checked', parseInt(data.prices.tel));
         }

         if ($('input.selectedPf', row)) {
            $('input.selectedPf', row).prop('checked', parseInt(data.articles.pf));
         }
      }
   });

   // Inline editing on click
   $('#example').on( 'click', 'tbody td:not(:first-child)', function (e) {
        editor.inline( this, {
            submit: 'allIfChanged'
        } );
    } );
   
   // Inline editing on tab focus
   table.on('key-focus', function (e, datatable, cell) {
      editor.inline(cell.index(), {
         onBlur: 'submit'
      });
   });

stoads

At this time when I change a cell value on editor datatable it don't refresh the data base (sql) and the value on editor datatable.

here my code...

var editor; // use a global for the submit and return data rendering in the examples

$(function () {

   editor = new $.fn.dataTable.Editor({
      ajax: "../private/server/ctrl_dataTable.php",
      table: "#articlesMarketing",
      fields: [{
            label: "Name ",
            name: "articles.product",
            type: "select"
         }, {
            label: "Fish specifications",
            name: "articles.specifications"
         }, {
            label: "Packaging ",
            name: "articles.packaging"
         }, {
            label: "PF ",
            name: "articles.pf",
            type: "checkbox",
            separator: "|",
            options: [
               {label: '', value: 1}
            ]
         }, {
            label: "Country ",
            name: "articles.country"
         }, {
            label: "Date ",
            name: "prices.datePrice",
            type: "datetime"
         }, {
            label: "Phone ",
            name: "prices.tel",
            type: "checkbox",
            separator: "|",
            options: [
               {label: '', value: 1}
            ]
         }, {
            label: "Cours I ",
            name: "prices.cours1"
         }, {
            label: "Cours II ",
            name: "prices.cours2"
         }, {
            label: "Cours III ",
            name: "prices.cours3"
         }, {
            label: "Cours IV ",
            name: "prices.cours4"
         }, {
            label: "Cours V ",
            name: "prices.cours5"
         }, {
            label: "Cours VI ",
            name: "prices.cours6"
         }, {
            label: "Cours VII ",
            name: "prices.cours7"
         }
      ]
   });

   var table = $('#articlesMarketing').DataTable({
      dom: "<'row'<'container'<'col-md-12'B>>>" + "<'row'<'container'<'col-md-6'l><'col-md-6'f>>>" + "<'row'<'container'<'col-md-12'rtipH>>>",
      ajax: {
         url: "../private/server/ctrl_dataTable.php",
         type: "POST"
      },
      lengthMenu: [[10, 25, 50, -1], [10, 25, 50, "All"]],
      scrollX: true,
      scrollY: '100vh',
      scrollCollapse: true,
      columns: [
         {
                data: null,
                defaultContent: '',
                className: 'select-checkbox',
                orderable: false
          },
         {data: "products.name"},
         {data: "articles.specifications"},
         {data: "articles.packaging"},
         {
            data: "articles.pf",
            render: function (data, type, row) {
               if (type === 'display') {
                  return '<input type="checkbox" class="selectedPf">';
               }
               return data;
            }
         },
         {data: "articles.country"},
         {data: "prices.datePrice"},
         {
            data: "prices.tel",
            render: function (data, type, row) {
               if (type === 'display') {
                  return '<input type="checkbox" class="selectedTel">';
               }
               return data;
            }
         },
         {data: "prices.cours1", render: $.fn.dataTable.render.number(',', '.', 0, '€')},
         {data: "prices.cours2", render: $.fn.dataTable.render.number(',', '.', 0, '€')},
         {data: "prices.cours3", render: $.fn.dataTable.render.number(',', '.', 0, '€')},
         {data: "prices.cours4", render: $.fn.dataTable.render.number(',', '.', 0, '€')},
         {data: "prices.cours5", render: $.fn.dataTable.render.number(',', '.', 0, '€')},
         {data: "prices.cours6", render: $.fn.dataTable.render.number(',', '.', 0, '€')},
         {data: "prices.cours7", render: $.fn.dataTable.render.number(',', '.', 0, '€')}
      ],
      autoWidth: true,
      order: [1, 'asc'],
      keys: {
            columns: ':not(:first-child)',
            keys: [ 9 ]
        },
      select: {
         style: 'os',
         selector: 'td:first-child'
      },
      buttons: [
         {extend: "create", editor: editor},
         {extend: "remove", editor: editor}
      ],
      rowCallback: function (row, data) {
         // Set the checked state of the checkbox in the table
         if ($('input.selectedTel', row)) {
            $('input.selectedTel', row).prop('checked', parseInt(data.prices.tel));
         }

         if ($('input.selectedPf', row)) {
            $('input.selectedPf', row).prop('checked', parseInt(data.articles.pf));
         }
      }
   });

   // Inline editing on click
   $('#articlesMarketing').on('click', 'tbody td:not(:first-child)', function (e) { 
      editor.inline(this);
   });
   
   // Inline editing on tab focus
   table.on('key-focus', function (e, datatable, cell) {
      editor.inline(cell.index(), {
         onBlur: 'submit'
      });
   });
   
});