Answers

• allan Posts: 63,350Questions: 1Answers: 10,443 Site admin

  December 2016 Answer ✓

  HI Sergio,

  Assuming you are using the provided PHP libraries for Editor, then yes, you can provide where condition information for the SQL as described in the documentation here.

  Allan
• sergioanselmi Posts: 3Questions: 1Answers: 1

  December 2016

  Hi Allan! Thankyou very much for your quick reply! It was helpful.
  Now I can't figure out how can I retrieve the ajax.data passed as parameter to url. I tried the solution given here but I dont know how can the server script get this value to apply the where condition.

  I'm working with the examples, here is the Javascript code

  $(document).ready(function() {
      editor = new $.fn.dataTable.Editor( {
              ajax:  {
          url: '../php/staff.php',
          data: function ( d ) {
          d.place = "New York";
           }
     },
     
          table: "#example",
          fields: [ {
                  label: "First name:",
                  name: "first_name"
              }, {
                  label: "Last name:",
                  name: "last_name"
              }, {
                  label: "Position:",
                  name: "position"
              }, {
                  label: "Office:",
                  name: "office"
              }, {
                  label: "Extension:",
                  name: "extn"
              }, {
                  label: "Start date:",
                  name: "start_date",
                  type: "datetime"
              }, {
                  label: "Salary:",
                  name: "salary"
              }
          ]
      } );
  
      $('#example').DataTable( {
          dom: "Bfrtip",
          ajax: "../php/staff.php",
          columns: [
              { data: null, render: function ( data, type, row ) {
                  // Combine the first and last names into a single table field
                  return data.first_name+' '+data.last_name;
              } },
              { data: "position" },
              { data: "office" },
              { data: "extn" },
              { data: "start_date" },
              { data: "salary", render: $.fn.dataTable.render.number( ',', '.', 0, '$' ) }
          ],
          select: true,
          buttons: [
              { extend: "create", editor: editor },
              { extend: "edit",   editor: editor },
              { extend: "remove", editor: editor }
          ]
      } );
  } );
  

  And here the PHP script code

  // DataTables PHP library
  include( "../../php/DataTables.php" );
  
  // Alias Editor classes so they are easy to use
  use
      DataTables\Editor,
      DataTables\Editor\Field,
      DataTables\Editor\Format,
      DataTables\Editor\Mjoin,
      DataTables\Editor\Options,
      DataTables\Editor\Upload,
      DataTables\Editor\Validate;
  
  // Build our Editor instance and process the data coming from _POST
  
  $place = $_GET['place']; //when I put this, it give me a json error (tn/1)
  
  Editor::inst( $db, 'datatables_demo' )
      ->fields(
          Field::inst( 'first_name' )->validator( 'Validate::notEmpty' ),
          Field::inst( 'last_name' )->validator( 'Validate::notEmpty' ),
          Field::inst( 'position' ),
          Field::inst( 'email' ),
          Field::inst( 'office' ),
          Field::inst( 'extn' ),
          Field::inst( 'age' )
              ->validator( 'Validate::numeric' )
              ->setFormatter( 'Format::ifEmpty', null ),
          Field::inst( 'salary' )
              ->validator( 'Validate::numeric' )
              ->setFormatter( 'Format::ifEmpty', null ),
          Field::inst( 'start_date' )
              ->validator( 'Validate::dateFormat', array(
                  "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_8601 )
              ->setFormatter( 'Format::date_format_to_sql', Format::DATE_ISO_8601 )
      )
          ->where( 'office', $place)
      ->process( $_POST )
      ->json();
  

  Thank you very much!
• sergioanselmi Posts: 3Questions: 1Answers: 1

  December 2016 Answer ✓

  I found the solution!
  I find here some more information.
  The ajax.data were in the wrong place (and other things)

  Here the correct code:

  var editor; // use a global for the submit and return data rendering in the examples
  
  $(document).ready(function() {
      editor = new $.fn.dataTable.Editor( {
    
          table: "#example",
          fields: [ {
                  label: "First name:",
                  name: "first_name"
              }, {
                  label: "Last name:",
                  name: "last_name"
              }, {
                  label: "Position:",
                  name: "position"
              }, {
                  label: "Office:",
                  name: "office"
              }, {
                  label: "Extension:",
                  name: "extn"
              }, {
                  label: "Start date:",
                  name: "start_date",
                  type: "datetime"
              }, {
                  label: "Salary:",
                  name: "salary"
              }
          ]
      } );
  
      $('#example').DataTable( {
          dom: "Bfrtip",
          ajax:  {
          url: '../php/staff.php',
          type: "POST",
          data: function ( d ) {
          d.place = "New York";
           }
     },
          columns: [
              { data: null, render: function ( data, type, row ) {
                  // Combine the first and last names into a single table field
                  return data.first_name+' '+data.last_name;
              } },
              { data: "position" },
              { data: "office" },
              { data: "extn" },
              { data: "start_date" },
              { data: "salary", render: $.fn.dataTable.render.number( ',', '.', 0, '$' ) }
          ],
          select: true,
          buttons: [
              { extend: "create", editor: editor },
              { extend: "edit",   editor: editor },
              { extend: "remove", editor: editor }
          ]
      } );
  } );
  

  And php script

  // DataTables PHP library
  include( "../../php/DataTables.php" );
  
  // Alias Editor classes so they are easy to use
  use
      DataTables\Editor,
      DataTables\Editor\Field,
      DataTables\Editor\Format,
      DataTables\Editor\Mjoin,
      DataTables\Editor\Options,
      DataTables\Editor\Upload,
      DataTables\Editor\Validate;
  
  // Build our Editor instance and process the data coming from _POST
  
  ;
  Editor::inst( $db, 'datatables_demo' )
      ->fields(
          Field::inst( 'first_name' )->validator( 'Validate::notEmpty' ),
          Field::inst( 'last_name' )->validator( 'Validate::notEmpty' ),
          Field::inst( 'position' ),
          Field::inst( 'email' ),
          Field::inst( 'office' ),
          Field::inst( 'extn' ),
          Field::inst( 'age' )
              ->validator( 'Validate::numeric' )
              ->setFormatter( 'Format::ifEmpty', null ),
          Field::inst( 'salary' )
              ->validator( 'Validate::numeric' )
              ->setFormatter( 'Format::ifEmpty', null ),
          Field::inst( 'start_date' )
              ->validator( 'Validate::dateFormat', array(
                  "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_8601 )
              ->setFormatter( 'Format::date_format_to_sql', Format::DATE_ISO_8601 )
      )
          ->where( 'office', $_POST['place'])
      ->process( $_POST )
      ->json();