How to use 'WHERE' clause using ssp.class.php DataTables

Question

Okay so i'm trying to display information from my database using jQuery DataTable (DataTables.net). I can get it to work fine displaying the entire table 'notes' but I would like to just display notes that have not been read. So I need to include a WHERE clause somehow but i'm unclear on the best way to go about this.

Here is how I currently display the whole table:

// DB table to use
$table = 'Notes';

// Table's primary key
$primaryKey = 'CID';

// 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. In this case simple
// indexes
$columns = array(
array( 'db' => 'CID', 'dt' => 0 ),

array(
    'db'        => 'CID',
    'dt'        => 0,
    'formatter' => function( $d, $row ) {
        return '<a href="profile.php?search='.$d.'" target="_Blank">'.$d."</a>";
    }
),

array( 'db' => 'Title', 'dt' => 1 ),
array( 'db' => 'Name',  'dt' => 2 ),
array(
    'db'        => 'Date',
    'dt'        => 3,
    'formatter' => function( $d, $row ) {
        return date( 'jS M y', strtotime($d));
        }
    )
);

// SQL server connection information
$sql_details = array(
'user' => '*DB_USER*',
'pass' => '*Password*',
'db'   => '*DatabaseName*',
'host' => 'localhost'
);
require( 'ssp.class.php' );

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

I would need the equivalent of SELECT * FROM Notes WHERE Status ='Unread'

Answer 1

You should change DataTables default functions to do this!

use this ssp.class.php customized class

Link

Use it like following example:

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

If you set $where parameter, Custom class will add where clause to select statement!

Update :

DataTables in 2015 add complex method

The new built-in method can set where clause in query!

Answer 2

Well.. you can't without editing or extending SSP. This is pretty bad style with alot of copied code, but SSP does not allow better customization...

class SSPCustom extends SSP
{
    /**
     *  @param  array $request Data sent to server by DataTables
     *  @param  array $sql_details SQL connection details - see sql_connect()
     *  @param  string $table SQL table to query
     *  @param  string $primaryKey Primary key of the table
     *  @param  array $columns Column information array
     *  @param  string $whereCustom Custom (additional) WHERE clause
     *  @return array          Server-side processing response array
     */
    static function simpleCustom ( $request, $sql_details, $table, $primaryKey, $columns, $whereCustom = '' )
    {
        $bindings = array();
        $db = self::sql_connect( $sql_details );

        // Build the SQL query string from the request
        $limit = self::limit( $request, $columns );
        $order = self::order( $request, $columns );
        $where = self::filter( $request, $columns, $bindings );

        if ($whereCustom) {
            if ($where) {
                $where .= ' AND ' . $whereCustom;
            } else {
                $where .= 'WHERE ' . $whereCustom;
            }
        }

        // Main query to actually get the data
        $data = self::sql_exec( $db, $bindings,
            "SELECT SQL_CALC_FOUND_ROWS `".implode("`, `", self::pluck($columns, 'db'))."`
             FROM `$table`
             $where
             $order
             $limit"
        );

        // Data set length after filtering
        $resFilterLength = self::sql_exec( $db,
            "SELECT FOUND_ROWS()"
        );
        $recordsFiltered = $resFilterLength[0][0];

        // Total data set length
        $resTotalLength = self::sql_exec( $db,
            "SELECT COUNT(`{$primaryKey}`)
             FROM   `$table`
             WHERE  " . $whereCustom
        );
        $recordsTotal = $resTotalLength[0][0];


        /*
         * Output
         */
        return array(
            "draw"            => intval( $request['draw'] ),
            "recordsTotal"    => intval( $recordsTotal ),
            "recordsFiltered" => intval( $recordsFiltered ),
            "data"            => self::data_output( $columns, $data )
        );
    }
}

Call it with:

echo json_encode(
    SSPCustom::simpleCustom( $_GET, $sql_details, $table, $primaryKey, $columns, "Status ='Unread'" )
);

Untested