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;

<?php > ?>

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