Editor serverside php/Mysql

stoads · March 2016

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 · March 2016

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 · March 2016

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

allan · March 2016

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

Allan

stoads · March 2016

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 · March 2016

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 · March 2016

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 · April 2016

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 · April 2016

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 · April 2016

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

allan · April 2016

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

Allan

stoads · April 2016

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 · April 2016

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 · April 2016

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 · April 2016

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 · April 2016

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

allan · April 2016

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 · April 2016

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 · April 2016

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 · April 2016

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 · April 2016

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 · April 2016

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'
      });
   });
   
});

Editor serverside php/Mysql

Editor serverside php/Mysql

Answers

Howdy, Stranger!

Categories

DataTables

Editor serverside php/Mysql

Editor serverside php/Mysql

Answers

Howdy, Stranger!

Quick Links

Categories

DataTables