JOIN on Server-Side
JOIN on Server-Side
jviglio
Posts: 2Questions: 1Answers: 0
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; ?>This discussion has been closed.
Answers
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.
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 ),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: