How do you only display results that do not have NULL values in a specified column?

erbanach

Hi there,

I have a question about PHP server-side processing with DataTables. Currently, I have the following columns being pulled in from the mySQL database:

$columns = array(
    array( 'db' => 'Client', 'dt' => 0 ),
    array( 'db' => 'EstimateNumber',  'dt' => 1 ),
    array( 'db' => 'Status',   'dt' => 2 ),
    array( 'db' => 'CurrentEstimateTotal',     'dt' => 3 ),
    array( 'db' => 'CreatedDate',     'dt' => 4 )
);

I want to include code here that only displays results that do not have a NULL value in the CreatedDate column. Is there an easy way to do this? Thank you for your help!

allan

This is with the demo SSP class is it? You would need to use the SSP::complex() method which allows you to specify a WHERE filter by passing in optional parameters. There are documentation comments for those parameters in the SSP class.

Allan

erbanach

Hi Allan,

 echo json_encode(
 SSP::complex( $_GET, $sql_details, $table, $primaryKey, $columns, $whereResult=null, $whereAll=null )
 );

Is this the correct function? What else do I need to add?

Thank you,
Liz

allan

Yes - that's the one. So basically what you might do is:

echo json_encode(
  SSP::complex( $_GET, $sql_details, $table, $primaryKey, $columns, null, "myColumn IS NOT NULL"
);

That will basically add WHERE myColumn IS NOT NULL to the SQL query (obviously update the column name as required .

Regards,
Allan

erbanach

Worked like a charm! Thank you!