Using javascript parameters to build a server-side WHERE filter

Using javascript parameters to build a server-side WHERE filter

akrinskyakrinsky Posts: 3Questions: 0Answers: 0
edited December 2013 in DataTables 1.9
I was hoping to filter the list of possible places in my database to the State of California (CA) prior to editing. In fact, I would like to marshall up a whole bunch of COLUMN = VALUE filters and have them filter down the rows passed to the grid.

This usage is not working... am I missing something?

$('#Places').dataTable( {
"sDom": "Tfrtip",
"sAjaxSource": "php/table.Places.php",
"bServerSide": true,
"sServerMethod": 'POST',
"fnServerParams": function ( aoData ) {aoData.push( { "name":"STATE","value":"CA" } )},
"aoColumns": [
{
"mData": "STATE"
},
{
"mData": "CITY"
}
],
"oTableTools": {
"sRowSelect": "multi",
"aButtons": [
//{ "sExtends": "editor_create", "editor": editor },
{ "sExtends": "editor_edit", "editor": editor },
{ "sExtends": "editor_remove", "editor": editor }
]
}
} );
} );

Replies

  • allanallan Posts: 63,494Questions: 1Answers: 10,470 Site admin
    Hi,

    The code above should be sending `STATE=CA` as a POST parameter to the server whenever DataTables makes a request to the server for a table draw. Is that not happening? Also have you added the `where()` call in the PHP file? Something like:

    [code]
    // If no action is requested, than its a DataTables data get
    if ( ! $_REQUEST['action'] ) {
    $editor->where( 'state', 'POST' );
    }
    [/code]

    (which would be added before the `process()` call).

    Thanks,
    Allan
  • akrinskyakrinsky Posts: 3Questions: 0Answers: 0
    That was helpful.

    For multiple criteria, I'm seeing an AND'ing problem like other users. Does $q need to be declared in some special way? Is there a recommended approach for debugging this?

    if (isset($_POST['STATE'])) {
    //$editor->where( $key = 'STATE',$value = explode ("|",$_POST['STATE']),$op = '=' );
    $editor->where( function ($q) {
    $q->where( 'STATE', 'PA' );
    $q->where( 'STATE', 'CA' );
    } );
    }
  • akrinskyakrinsky Posts: 3Questions: 0Answers: 0
    Allan,

    Changing the [code]where[/code] method in Query.php makes this much more usable:

    [code]else {
    if ( !is_array($key) && is_array($value) ) {
    $this->_where_group( true, ' AND ' );
    for ( $i=0 ; $i_where( $key, $value[$i], ' OR ',$op, $bind );
    }
    $this->_where_group( false, ' OR ' );
    return $this;
    }
    $this->_where( $key, $value, ' AND ', $op, $bind );
    }
    [/code]

    That allows anything passed as an array to be rendered as (X=A OR X=B OR X=C)...

    Cheers
This discussion has been closed.