JOIN on Server-Side

jviglio

Hi. I have a Server-Side DataTable which shows the result of a JOIN sentence. The problem I have is I can't show fields from different tables. It shows the error: Column not found: 1054 Unknown column 'dep.date' in 'field list. If I show only the field "date" it works fine, but when I add the "dep" the error appears.
This is the code i use. Thanks, Juan.

```

<?php require('ssp.class.php'); require('config.php'); // DB table to use $table = 'vc_deployment AS dep'; $table .= " LEFT JOIN vc_process AS prc ON dep.id_process = prc.id"; // Table's primary key $primaryKey = 'id'; $columns = array( array( 'db' => 'dep.date', 'dt' => 0 ), array( 'db' => 'issue', 'dt' => 1 ), array( 'db' => 'date', 'dt' => 2 ), array( 'db' => 'result', 'dt' => 3 ), array( 'db' => 'version', 'dt' => 4 ) ); // SQL server connection information $sql_details = array( 'user' => $db_user, 'pass' => $db_pass, 'db' => $db_name, 'host' => $db_host ); $MinYear = $_GET['minYear']; $MaxYear = $_GET['maxYear']; $table = json_encode(SSP::simple( $_GET, $sql_details, $table, $primaryKey, $columns, $MinYear, $MaxYear )); echo $table; ?>

ignignokt

I believe the reason for this is the implode which builds the columns puts back-ticks around the column names. This will work for 'date' but not 'dep.date' since the back-ticks count the entire string as the field name. So it is literally looking for column dep.date instead of table dep column date.

You don't really "need" back-ticks or quotes around the column names unless you have punctuation or a space in the name, which I think is a bad idea anyway.

To use back-ticks, you'd need it to look like this 'dep'.'date'

You can also take a look at my implementation and alteration of the ssp.class.php file if you wish.

jviglio

Thanks for answering ignignilt. I have tested with the back-ticks but it doesn't work. This is what I have tested.

array( 'db' => 'dep.date', 'dt' => 0 ),

and

array( 'db' => dep.date, 'dt' => 0 ),

ignignokt

The back-ticks would have to be 'dep'.'date' two sets of them. But the function itself in ssp.class.php already adds them. Try taking them out everywhere like so:

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