Inner Join on Datatable server-side - ssp.class.php

michelmir

Hello.

I'm working with 2 related tables and i would like to INNER JOIN these tables to display name instead of number. On the first table (users) i have users data and on second table (categories) i have category informations. The structure of these tables are:

//Category table

| id  |Category
-------------------
|  1  | Client 
|  2  | Visitor

//users table

|  id  |      name       |  categoryFK
-----------------------------------------
|  1  | John Morello  | 1
|  2  | George Max   | 2
|  3  | Joseph Moor  | 2
|  4  | Mary Philipes | 1

Based on structure above, my Datatable display data based on script below:

$table = 'users';
 
// Table's primary key
$primaryKey = 'id';
 
$columns = array(
    array( 'db' => 'id', 'dt' => 'id' ),
    array( 'db' => 'name',  'dt' => 'name' ),
    array( 'db' => 'category',   'dt' => 'categoryFK' ),
);
 

$sql_details = array(
    'user' => '',
    'pass' => '',
    'db'   => '',
    'host' => 'localhost'
);
 
require( 'ssp.class.php' );
 
echo json_encode(
    SSP::simple( $_POST, $sql_details, $table, $primaryKey, $columns )
);

My doubt is, how can i INNER JOIN Category table with Users table to display category name on users table instead of Category id? In this case i have to put SQL select code in this code line $table = 'users'; ? What a correct way to improve this code for this situation?

Thanks

rihcardo

You have to make "inner join" in your sql script. It must be somewhere.