How to join two tables using ssp.class.php

Question

I started using the DataTables Table plugin for jQuery and got some problems. I am using the sample code from here .

My MySQL table witch looks like this:

id | name | father_id

father_idis a value idin a single table in only different rows. Therefore, if I want to find out the name of the father, I need to look in the same table WHERE id = father_id. But what DataTable it just displays the contents of the MySQL table as it is.

In my DataTable, I want to show such data:

id | name | father's name | father_id

Therefore, when a DataTable takes data from a MySQL table, but before creating the table, I want to change the value of the column, which at that time is the value father_idin the same row in MySQL. I also want to add father_nameby doing a search with father_id.

Answer 1

As PaulF noted , you need to use JOINeither a subquery to retrieve the father's name from the same table.

I assume that you are using ssp.class.phpto process your data on the server side based on the example you mentioned.

ssp.class.php , . , $table. table .

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

ssp.class.php FROM `$table` FROM $table, .

, , AS .

github.com/emran/ssp , ssp.class.php JOINs.

. jQuery DataTables: WHERE, JOIN GROUP BY ssp.class.php .

Answer 2

-

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

Answer 3

, DataTables JOIN WHERE, ssp.class.php . API, , . "SSP:: simple" "SSP:: complex" "SSP:: process".

- script pastebin.com : ssp.class. PHP

, :

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" parameters (SEE "SSP :: complex" for details) can also have an "alias" to refer to a column in a joined table.

An example of an 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 route of a structured array because it allowed me to create queries, while maintaining the rigidity of the queries with backticks and associated operator parameters. I am raising this post in the hope that others will find it useful, as I am.