I am using jquery datatable with server-side processing. Just I am trying to make join
query to produce output.
So I changed columns array to something like this:
$aColumns = array (
"u.user_id",
"CONCAT(u.first_name, ' ', u.last_name)",
"u.gender",
"CONCAT(a.suburb, ', ', a.zip_code)",
"u.date_registered"
);
And changed query like this.
$sQuery = "SELECT SQL_CALC_FOUND_ROWS u.user_id
, CONCAT(u.first_name, ' ', u.last_name)
, u.gender
, CONCAT(a.suburb, ', ', a.zip_code)
, u.date_registered
FROM users u
INNER JOIN user_addresses a ON a.user_id = u.user_id ".$sWhere.$sOrder.$sLimit;
Then I can populate the datatable. But its search, filter and sort is not working.
This kind of error I can get when I trying to search, filter or sort.
Unknown column 'u.user_id' in 'where clause'
Updates
This is the output when I echo $sQuery
SELECT SQL_CALC_FOUND_ROWS u.user_id
, CONCAT(u.first_name, ' ', u.last_name)
, u.gender
, CONCAT(a.suburb, ', ', a.zip_code)
, u.date_registered
FROM users u
INNER JOIN user_addresses a ON a.user_id = u.user_id AND a.address_type = 1
WHERE (`u.user_id` LIKE '%s%' OR `CONCAT(u.first_name, ' ', u.last_name)` LIKE '%s%' OR `u.gender` LIKE '%s%' OR `CONCAT
(a.suburb, ', ', a.zip_code)` LIKE '%s%' OR `u.date_registered` LIKE '%s%') LIMIT 0, 10
Unknown column 'u.user_id' in 'where clause'
Can anybody may help me out. Thank you.