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

In case anyone else stumbles across this, the correct answer is now:

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

No need for 3rd party classes, this functionality is built into DataTables.

Answer 3

I was also able to solve this problem but inserting some code into the ssp.class.php filter function. Below is the listing for the function with an example custom where clause inserted. The "simple" function from that class will work without any further jury-rigging. The advantage is that it will play nice with the text-search feature of the datatable.

static function filter ( $request, $columns, &$bindings )
{
    $globalSearch = array();
    $columnSearch = array();
    $dtColumns = self::pluck( $columns, 'dt' );

    if ( isset($request['search']) && $request['search']['value'] != '' ) {
        $str = $request['search']['value'];

        for ( $i=0, $ien=count($request['columns']) ; $i<$ien ; $i++ ) {
            $requestColumn = $request['columns'][$i];
            $columnIdx = array_search( $requestColumn['data'], $dtColumns );
            $column = $columns[ $columnIdx ];

            if ( $requestColumn['searchable'] == 'true' ) {
                $binding = self::bind( $bindings, '%'.$str.'%', PDO::PARAM_STR );
                $globalSearch[] = "`".$column['db']."` LIKE ".$binding;
            }
        }
    }

    // Individual column filtering
    for ( $i=0, $ien=count($request['columns']) ; $i<$ien ; $i++ ) {
        $requestColumn = $request['columns'][$i];
        $columnIdx = array_search( $requestColumn['data'], $dtColumns );
        $column = $columns[ $columnIdx ];

        $str = $requestColumn['search']['value'];

        if ( $requestColumn['searchable'] == 'true' &&
         $str != '' ) {
            $binding = self::bind( $bindings, '%'.$str.'%', PDO::PARAM_STR );
            $columnSearch[] = "`".$column['db']."` LIKE ".$binding;
        }
    }

    // Combine the filters into a single string
    $where = '';

    if ( count( $globalSearch ) ) {
        $where = '('.implode(' OR ', $globalSearch).')';
    }

    if ( count( $columnSearch ) ) {
        $where = $where === '' ?
            implode(' AND ', $columnSearch) :
            $where .' AND '. implode(' AND ', $columnSearch);
    }

        //------------------------------------------------------------
        //############################################################
        //EXAMPLE ADDITIONAL WHERE CONDITIONS HERE. THIS IS EQUIVALENT
        //TO "WHERE id = 1"
        $where = ($where === '') ? 
            "id = ".self::bind( $bindings, 1, PDO::PARAM_INT) :
            $where ." AND "."id = ".self::bind( $bindings, 1, PDO::PARAM_INT);
        //############################################################
        //############################################################
        //------------------------------------------------------------


    if ( $where !== '' ) {
        $where = 'WHERE '.$where;
    }

    return $where;
}

Answer 4

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

Answer 5

You can use where clause like that;

$data = SSP::sql_exec( $db, $bindings,
   "SELECT SQL_CALC_FOUND_ROWS ".implode(", ", SSP::pluck($columns, 'db'))."
    FROM $table where Status = 'Unread' // <--where clause here
    $where
    $order
    $limit"
);