How can I filter out results from my DataTable using MySQL WHERE query?

erbanach

Hi Allan!

I need to filter out some results from my DataTable. For example, I need to filter out the rows that have a Status equal to Cancelled or Invoiced using something like this query:

WHERE Status = Cancelled AND Status = Invoiced;

How can I include this?

As a reference, my configuration file now looks like this:

<?php
// connection configuration
require_once 'config.php';

// db table to use
$table = 'Estimates';

// table's primary key
$primaryKey = 'EstimateNumber';

// date variables
$created_date = 'CreatedDate';
$current_date = new DateTime();

// estimate total variable
$current_estimate_total = 'CurrentEstimateTotal';

// client cost total variable
$client_cost_total = 'ClientCostsTotal';

// array of database columns which should be read and sent back to DataTables.
// the 'db' parameter represents the column name in the database, while the 'dt'
// parameter represents the DataTables column identifier.
$columns = array(
    array( 'db' => 'Client', 'dt' => 0 ),
    array( 'db' => 'EstimateNumber', 'dt' => 1 ),
    array( 'db' => 'Status', 'dt' => 2 ),
    array(
      'db' => $created_date,
      'dt' => 3,
      'formatter' =>
            function ($created_date, $row) use ($current_date) {
            $date = new DateTime($created_date);
            $diff = date_diff($date, $current_date);
            return $diff->format('%d days live');
            } // end function
    ),
    array(
      'db' => $client_cost_total,
      'dt' => 4,
      'formatter' =>
            function ($client_cost_total, $row) {
            // US national money format
            setlocale(LC_MONETARY, 'en_US');
            $format = money_format('%(#10n',$client_cost_total);
            return $format;
            } // end function
    ),
    array(
      'db' => $current_estimate_total,
      'dt' => 5,
      'formatter' =>
            function ($current_estimate_total, $row) {
            // US national money format
            setlocale(LC_MONETARY, 'en_US');
            $format = money_format('%(#10n',$current_estimate_total);
            return $format;
            } // end function
    )
); // end columns array

// sql server connection information
$sql_details = array(
    'user' => $currentConfig['user'],
    'pass' => $currentConfig['pass'],
    'db'   => $currentConfig['name'],
    'host' => $currentConfig['host']
);

// DataTables helper class
require( 'ssp.class.php' );

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

allan

You already have a WHERE condition in the last parameter passed in to the SSP::complex function, so you just need to expand upon that. For example:

"CreatedDate IS NOT NULL AND  Status = 'Cancelled' AND Status = 'Invoiced'"

Allan

erbanach

Thank you - I copied that line exactly, however, it didn't seem to work. On the front-end, it displays: "No data available in table". Nothing is visible in the Apache error log.

Please let me know how to fix this.

Thank you,
Liz

allan

Could you have a look in your browser's network tab and show me the JSON that is being returned? It sounds like there might not be any data that matched that condition.

Allan

erbanach

Hi Allan,

My mistake. I meant to write is not equal (!=). Once I fixed the query, it worked.

Thank you!
Liz