[EDITOR] Populate table filtering by field

sergioanselmi

Hi!

I used DataTables to show a table populated only with rows marked in certain field, on the table in database ("status: active" for example).
To do that I pre-process the data with javascript, and in the sql query and pass it then to DataTable as Json (as described in "Javascript sourced").

Now I'm working with Editor, but the tables can be populated by php a script that brings the entire table from database.

Is there a way to filter the data from database, by passing a parameter to the sql query?

Thanks!

Best regards,

Sergio

allan

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

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

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();