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

Comments

  1. Thumbs up 10-Stars to Michael!!!!!!!!!!!!!
    You are a time saver thank you for sharing. This is such a simple and straight forward process but its hidden.I wonder why DataTables has not mentioned it. Thanks you for sharing. I have been searching and experimenting with all sorts FORKED nonsense which i was still believed was not the best! THIS IS THE BEST!!!!!!!!!!!!!!!!!!!

  2. Hello Michael, I’m using codeigniter using ssp class solution #1 but I have got an error when using this subqueries.
    That the message appear {“error”:”An SQL error occurred: SQLSTATE[42000]: Syntax error or access violation: 1064 blabla blala…bla…

    Note : I use same database like your example to test
    can you help me, to figure out this?
    thanks

      1. Hello Michael sorry for late response…
        Have you edited ssp.class.php to replace all instances of FROM `$table` with FROM $table?
        yes, am I. …

        I aready follow your instruction..but not working at all and got that error.

        can you help me to solve this?
        or can you make example for me …thanks

  3. very good post Michael..but, i use it for my 15k data and it’s very slow to show the data..even though i use simple join statement..can u help me please..thanks

Leave a Reply

You may use simple HTML to add links or lists to your comment. Also use <pre><code class="language-*">...</code></pre> to mark up code snippets. We support language-js, language-markup and language-css for comments.
(Optional)

This site uses Akismet to reduce spam. Learn how your comment data is processed.