jQuery DataTables: Using WHERE, JOIN and GROUP BY with ssp.class.php

Using WHERE clause

Class ssp.class.php comes with two methods – SSP::simple() and SSP::complex().

Usage of SSP::simple() method is demonstrated in the server_processing.php script.

Method SSP::complex() has two additional arguments $whereResult and $whereAll that allow to add WHERE clause to a query.

• $whereResult – Condition to apply to result set only

  If you need to apply condition to your dataset and let user know that larger dataset is available, supply your condition using $whereResult argument. jQuery DataTables will show something like Showing 1 to X of X entries (filtered from X total entries) in the information panel.

• $whereAll – Condition to apply to all queries

  If you need to apply condition to your dataset without letting user know that larger dataset is available, supply your condition using $whereAll argument. jQuery DataTables will show something like Showing 1 to X of X entries in the information panel.

Most of the time you need to apply your condition using $whereAll argument and use null as a value for $whereResult argument.

For example, the following excerpt shows code that produces JSON response while adding WHERE position='Software Developer' clause to the query.

echo json_encode(
   SSP::complex( $_GET, $sql_details, $table, $primaryKey, $columns, null, "position='Software Developer'" )
);

See below excerpt from ssp.class.php for more information on SSP::complex() method.

   /**
    * The difference between this method and the `simple` one, is that you can
    * apply additional `where` conditions to the SQL queries. These can be in
    * one of two forms:
    *
    * * 'Result condition' - This is applied to the result set, but not the
    *   overall paging information query - i.e. it will not effect the number
    *   of records that a user sees they can have access to. This should be
    *   used when you want apply a filtering condition that the user has sent.
    * * 'All condition' - This is applied to all queries that are made and
    *   reduces the number of records that the user can access. This should be
    *   used in conditions where you don't want the user to ever have access to
    *   particular records (for example, restricting by a login id).
    *
    *  @param  array $request Data sent to server by DataTables
    *  @param  array|PDO $conn PDO connection resource or connection parameters array
    *  @param  string $table SQL table to query
    *  @param  string $primaryKey Primary key of the table
    *  @param  array $columns Column information array
    *  @param  string $whereResult WHERE condition to apply to the result set
    *  @param  string $whereAll WHERE condition to apply to all queries
    *  @return array          Server-side processing response array
    */
   static function complex ( $request, $conn, $table, $primaryKey, $columns, $whereResult=null, $whereAll=null )

Using JOIN or GROUP BY clauses

Solution #1

Workaround described below can be used to add support for any complex query not just JOIN or GROUP BY clauses. However it requires modification of original ssp.class.php class.

• Edit ssp.class.php and replace all instances of FROM `$table` with FROM $table to remove backticks. You will be responsible for escaping your table names if they contain reserved keywords.
• Use sub-query as table name $table and use any complex query with JOIN, GROUP BY or any other clauses.

  
  <?php
  
  $table = <<<EOT
   (
      SELECT 
        a.id, 
        a.name, 
        b.position
      FROM table a
      LEFT JOIN positions b ON a.position_id = b.id
   ) temp
  EOT;
  
  $primaryKey = 'id';
  
  $columns = array(
     array( 'db' => 'id',          'dt' => 0 ),
     array( 'db' => 'name',        'dt' => 1 ),
     array( 'db' => 'position',    'dt' => 2 )
  );
  
  $sql_details = array(
     'user' => '',
     'pass' => '',
     'db'   => '',
     'host' => ''
  );
  
  require( 'ssp.class.php' );
  echo json_encode(
     SSP::simple( $_GET, $sql_details, $table, $primaryKey, $columns )
  );
  

Solution #2

Alternatively you can try forked version of ssp.class.php created by Emran Ul Hadi, see emran/ssp repository on GitHub.

However the file was forked in May 2014, and hasn’t been kept in sync with minor updates to ssp.class.php since then.

You May Also Like

jQuery DataTables: Row selection using checkboxes and Select extension

jQuery DataTables: Row selection using checkboxes

jQuery DataTables: Why click event handler does not work

jQuery DataTables: Alphabetical Search