Using javascript parameters to build a server-side WHERE filter

akrinsky

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

allan

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

akrinsky

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

akrinsky

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