server-side table joins

bhar

I am using the two files - serverprocessing.php and ssp.class.php to join two tables and display data in datatables.

serverprocessing.php code

$table = 'company';
$sJoin ='inner join country on country.id = company.country inner join state on state.id = company.state inner join city on city.id = company.city inner join companytype on companytype.id = company.companytype';

// Table's primary key
$primaryKey = 'idcompany';

$columns = array(
array( 'db' => 'idcompany', 'dt' => 0 ),
array( 'db' => 'companyname', 'dt' => 1 ),
array( 'db' => 'website', 'dt' => 2 ),
array( 'db' => 'impemp', 'dt' => 3 ),
array( 'db' => 'country.countryname', 'dt' => 4 )
);

require( 'ssp.class.php' );

echo json_encode(
SSP::simple( $_GET, $sql_details, $table, $primaryKey, $columns, $sJoin)
);

ssp.class.php code - I have included $myjoin as parameter to simple function like this.

static function simple ( $request, $sql_details, $table, $primaryKey, $columns,$myJoin)
{
$bindings = array();
$db = SSP::sql_connect( $sql_details );

    // Build the SQL query string from the request
    $limit = SSP::limit( $request, $columns );
    $order = SSP::order( $request, $columns );
    $where = SSP::filter( $request, $columns, $bindings );

    // Main query to actually get the data
    $data = SSP::sql_exec( $db, $bindings,
        "SELECT SQL_CALC_FOUND_ROWS `".implode("`, `", SSP::pluck($columns, 'db'))."`
         FROM `$table`
          $myJoin
         $where
         $order
         $limit"
    );

I am getting an error - unknown column 'country.countryname' in 'field list

Any suggestions?

Thanks in advance

bhar

I got it.

I have done just one change in simple method in ssp.class as shown below.

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

In serverprocessing as shown below

$columns = array(
array( 'db' => 'idcompany','dt' => 0 ),
array( 'db' => 'companyname','dt' => 1 ),
array( 'db' => 'website', 'dt' => 2 ),
array( 'db' => 'impemp', 'dt' => 3 ),
array( 'db' => 'countryname','dt' => 4 )
);

please see the code and suggest me any mistakes in the code.