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

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

jemzjemz Posts: 131Questions: 40Answers: 1
edited October 2014 in Free community support

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"

                        }
  });

This question has an accepted answers - jump to answer

Answers

  • jemzjemz Posts: 131Questions: 40Answers: 1

    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 )
    );
    
    
    
  • jemzjemz Posts: 131Questions: 40Answers: 1

    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.

  • jemzjemz Posts: 131Questions: 40Answers: 1

    I hope @allan could help me on this.

  • allanallan Posts: 61,446Questions: 1Answers: 10,054 Site admin

    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

  • ignignoktignignokt Posts: 146Questions: 4Answers: 39

    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.

  • jemzjemz Posts: 131Questions: 40Answers: 1

    @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.

  • ignignoktignignokt Posts: 146Questions: 4Answers: 39
    edited October 2014 Answer ✓

    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"
    );

  • jemzjemz Posts: 131Questions: 40Answers: 1
    edited October 2014

    Thank you :)

This discussion has been closed.