How to join two tables with ssp.class.php

Question

I started using DataTables Table plug-in for jQuery and got some problems. I am using example code from here.

I have MySQL table witch looks like that:

id | name | father_id

father_id is id value in same table only in different row. So if I want to know father name i have to search in same table WHERE id = father_id. But what DataTable does it just show the contents of MySQL table as it is.

In my DataTable i want to show data like that:

id | name | father_name | father_id

So when DataTable takes data from MySQL table, but before it creates table I want to change column value which at that time is value of father_id in the same row in MySQL. I want too add father_name by searching for it with particular father_id.

Answer 1

As PaulF pointed out, you need to use JOIN or sub-query to retrieve father's name from the same table.

I assume you're using ssp.class.php to process your data on the server-side based on the example you've mentioned.

Class ssp.class.php doesn't support joins and sub-queries, but there is a workaround. The trick is to use sub-query as shown below in $table definition. Replace table with your actual table name in the sub-query.

$table = <<<EOT
 (
    SELECT 
      a.id, 
      a.name, 
      a.father_id, 
      b.name AS father_name
    FROM table a
    LEFT JOIN table b ON a.father_id = b.id
 ) temp
EOT;

$primaryKey = 'id';

$columns = array(
   array( 'db' => 'id',          'dt' => 0 ),
   array( 'db' => 'name',        'dt' => 1 ),
   array( 'db' => 'father_id',   'dt' => 2 ),
   array( 'db' => 'father_name', 'dt' => 3 )
);

$sql_details = array(
   'user' => '',
   'pass' => '',
   'db'   => '',
   'host' => ''
);

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

You also need to edit ssp.class.php and replace all instances of FROM `$table` with FROM $table to remove backticks.

Make sure all column names are unique otherwise use AS to assign an alias.

NOTES

There is also github.com/emran/ssp repository that contains enhanced ssp.class.php supporting JOINs.

LINKS

See jQuery DataTables: Using WHERE, JOIN and GROUP BY with ssp.class.php for more information.

Answer 2

Join the table with itself - you will need to use aliases

SELECT a.id, a.name, b.name, b.id
FROM table a
join table b on (b.father_id=a.id);

Answer 3

Recently I found myself using DataTables and requiring a more complex JOIN and WHERE clause that the original ssp.class.php did not support. So I modified the original file and changed the API a tiny bit to provide a table that gave me the flexibility I needed. I combined the functionality of the "SSP::simple" and "SSP::complex" into a single function named "SSP::process".

Due to the length of the script, I put it on pastebin.com here: ssp.class.php

And a quick example of how I use it:

private function get_recent_payments() {
    global 
        $pdoHost, $pdoUser, 
        $pdoPass, $pdoDatabase;

    // SQL server connection information
    $sql_details = array(
        'user' => $pdoUser,
        'pass' => $pdoPass,
        'db'   => $pdoDatabase,
        'host' => $pdoHost
    );

    // DataTables server-side processing
    require_once('ssp.class.php');

    $options = [
        'table' => 'payments',
        'alias' => 'l',
        'primaryKey' => 'id',
        'columns' => [
            [ 'db' => 'id',       'dt' => 0 ],
            [
                'db' => 'client_id',     
                'dt' => 1,
                'join' => [
                    'table' => 'clients',
                    'on' => 'id',
                    'select' => 'first_name',
                    'alias' => 'c',
                    'as' => 'fname',
                ]
            ],
            [
                'db' => 'client_id',     
                'dt' => 2,
                'join' => [
                    'table' => 'clients',
                    'on' => 'id',
                    'select' => 'last_name',
                    'alias' => 'c'
                ]
            ],
            [ 'db' => 'pay_date', 'dt' => 3 ]
        ],
        'where' => [
            [
                'db' => 'client_id',
                'op' => '!=',
                'value' => $_SESSION['client_id']
            ]
        ]
    ];

    $this->response(SSP::process($_REQUEST, $sql_details, $options));
}

The 'where' and 'whereResult' (SEE 'SSP::complex' for Details) clauses of the options array may also have an 'alias' to refer to a column in a joined table.

Example SQL Query passed to the server:

SELECT l.`id`, c.`first_name` AS 'fname', c.`last_name`, l.`pay_date` 
        FROM `payments` l
        JOIN `clients` c ON (c.`id` = l.`client_id`)
        WHERE l.`client_id` != :binding_0
        ORDER BY l.`pay_date` DESC
        LIMIT 0, 5

I took the structured array route because this enabled me to build the queries while maintaining the rigidity of queries with backticks and bound statement parameters. I'm putting up this post in hopes that others will find it as useful as I have.