Using WHERE Clause in server side processing

baoky

AT the top of my page I did this

$contact_type = $_GET['contact'];

require( 'ssp.class.php' );
$where = "contact_type ='$contact_type'";
echo json_encode(
    SSP::simple( $_GET, $sql_details, $table, $primaryKey, $columns, $where )
);

Above is what I did to try to add the clause and below is what I did at my page itself which use the datatables

    var dt = $('#example').DataTable( {
        "processing": true,
        "serverSide": true,
        "ajax": "api.php?t=client",
        "columns": [
{ "className": "details-control" , "data": null , "defaultContent": "   "},
{ "data": "name" },
{ "data": "mobile_phone" }
]

But the result return from server side processing for datatables always does not include the where clause that I specify in.

How do I send my $contact_type to my api.php and set the where clause in ssp.class.php to include it in the return result so return result will be filter by

e.g

contact_type='admin'

contact_type='manager'

and only return result that fit this where clause.

I am using DataTables thanks

tangerine

All the "ssp" classes in here are intended as examples only. They are not intended to be one-size-fits-all production-standard libraries.

You need to examine the particular version you are using to see how it handles a "where" parameter.

allan

This question keeps coming up, so I've recently added where options to the SSP class.

It isn't documented as such outside of the code comments, and it isn't used on this site yet, but take a look at the new complex query (linked above) to see how you can add where conditions to server-side processing.

Ad @tangerine says - the SSP class is really a helper class only. It is only designed for the 80% use case. Full coverage of SQL features would be a project in its own right.

Allan

dellaboemia

Allan, for the novices such as myself out there, can you provide a basic use case with full php script with a simple where clause conditions e.g. selecting records based upon a user id, using the new complex method. I've read through all the posts, but the answers tend to provide snippets and I'm not connecting the dots. Thanks in advance.

allan

Let says for example you are using the same as in this example

SSP::simple( $_GET, $sql_details, $table, $primaryKey, $columns )

And you simply wanted to add a condition that will limit the table to only "London" in the office column:

SSP::complex( $_GET, $sql_details, $table, $primaryKey, $columns, null, "office = 'London'" )

As easy as that :-)

Allan

dellaboemia

Thanks Allan, for being so responsive to questions. Now I'm really confused. The recommendation above is different from http://editor.datatables.net/manual/php/conditions#. Which approach to use and most importantly, where to put the code...specifically? I've downloaded a host of files in the full package using the generator. Please indicate which file and specifically where to place the code. I'm afraid the answers I've read assume context I don't possess.

allan

The recommendation above is different from http://editor.datatables.net/manual/php/conditions

Sure - the code above uses the SSP class which you specifically referred to above. I didn't know you are using the Editor classes. Are you? If so, then follow the information in the Editor manual which does not use the SSP class - it uses its own implementation.

Allan

dellaboemia

Again, thanks for your responsiveness and patience. I get the WHAT, but not WHERE. I've added the following clause to table.tablename.php file, but it breaks the code. Please indicate WHERE to place the code, precisely.

$editor->where( 'age', 18, '>' );

allan

Anywhere after you have initialised the $editor parameter and before you call the process() method.

If you are using the chaining API like most of the examples you would simply insert it before the process() call:

Editor::inst( ... )
  ->fields( ... )
  ->where( ... )
  ->process()
  ->json();

Allan

dellaboemia

Thanks Allan! Exactly what I needed to know. I'm up and running now. Thanks again! - d