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.