How to inner join query in ssp.class ( DataTables 1.10.3-dev)

jemz

Hi I need some help please I am confuse how can I apply inner join to the ssp.class
I want to get records from my two tables and display it in one datatable.

employee table

  empno
  empname,
  empaddress

emp_proj table

emp_proj_no
emp_no
emp_proj_name

And I want to display this list

empNo         empName        empprojNo       empprojName

0006            john                   A-001               Unit-1
0007            miguel               A-002               bldng-2
0008            sarah                A-003               lab-1
0009            lynda                A-004               lab-2
0010            Anaktaka          A-005                office-3

 $('#example').dataTable( {
                        "processing": true,
                        "serverSide": true,
                        "autoWidth": true,
                        "start": 0,
                        "searchable":true,
                        "ajax":{
                            url:"querydata4.php"

                        }
  });

jemz

querydata4.php

<?php

/*
 * DataTables example server-side processing script.
 *
 * Please note that this script is intentionally extremely simply to show how
 * server-side processing can be implemented, and probably shouldn't be used as
 * the basis for a large complex system. It is suitable for simple use cases as
 * for learning.
 *
 * See http://datatables.net/usage/server-side for full details on the server-
 * side processing requirements of DataTables.
 *
 * @license MIT - http://datatables.net/license_mit
 */

/* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
 * Easy set variables
 */

// DB table to use
$table = 'employee';

// Table's primary key
$primaryKey = 'id';

// Array of database columns which should be read and sent back to DataTables.
// The `db` parameter represents the column name in the database, while the `dt`
// parameter represents the DataTables column identifier. In this case simple
// indexes
$columns = array(
    array( 'db' => 'id', 'dt' => 0 ),
    array( 'db' => 'empno',  'dt' => 1 ),
    array( 'db' => 'empname',   'dt' => 2 ),
    array( 'db' => 'empaddress',  'dt' => 3 )

);

// SQL server connection information
$sql_details = array(
    'user' => 'root',
    'pass' => '',
    'db'   => 'mydb',
    'host' => 'localhost'
);


/* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
 * If you just want to use the basic configuration for DataTables with PHP
 * server-side, there is no need to edit below this line.
 */

require( 'ssp.class.php' );

echo json_encode(
    SSP::simple( $_GET, $sql_details, $table, $primaryKey, $columns )
);

jemz

I am using this ssp.class.php

https://github.com/DataTables/DataTables/blob/master/examples/server_side/scripts/ssp.class.php

I could not paste the code for ssp.class.php because body is 4119 characters too long.I just put the link above.

jemz

I hope @allan could help me on this.

allan

The sap class isn't designed for use with joined tables. No doubt it could be modified to do such, and I would be happy to work on that under the support options (the Quick 60 should do it).

Allan

ignignokt

What I ended up doing is creating a subquery for every table since none of the queries I have are simple. Basically the subquery is your completed query to get the desired results for the initial table load. Then in the ssp.class.php file I wrap a Select * From ( base query here).

Then from there you can easily add in the filter (LIKE statements), order, and limit to that query, which will all work perfectly with whatever crazy query you came up with to build your table.

jemz

@ignignokt,I am confuse I could not figure it out,can you please show some snippet where to apply this query in the ssp.class

select emp.*,proj.*
   from employee emp 
     inner join 
   emp_proj proj  on emp.empno = proj.emp_no

Thank you in advance.

ignignokt

I don't use their class, but I think it would look like this for you. I don't get their code formatting here, seems like tilde messes it up. I'm sure I'm just doing it wrong. Replace ' with tilde. Also don't forget the asterix in the baseSql

$baseSql = "select emp.,proj.
from employee emp
inner join
emp_proj proj on emp.empno = proj.emp_no";

$data = self::sql_exec( $db, $bindings,
"SELECT SQL_CALC_FOUND_ROWS '".implode("', '", self::pluck($columns, 'db'))."'
FROM ($baseSql)
$where
$order
$limit"
);

jemz

Thank you :)