I want to concatenate the first and the last name as full name in my query.
$query = $this->db->select('CONCAT(w.firstName," ",w.lastName) as fullName,w.ID,w.firstName,w.lastName, w.firstname_ar, w.lastname_ar, w.cin , w.tel, w.tel2, w.poste_id, w.gender, w.date_birth, w.address, w.address_ar, w.familySituation, w.socialSecurity, w.typeContract, w.period,w.email,w.status,r.name,w.conge as cng,w.startDateContract, w.pictureProfile, w.endDateContract, w.dateTitulation, w.rib, w.languages, w.passport, w.passport_end_date, w.urgent_phone, w.urgent_phone_name,w.pointage_id as idpointeur, w.shift_start as heure_arr, w.shift_end as heure_dep, w.worker_role as workerRole, r.color as roleColor, r.name as departement, conge_annee, c.name as companyName, c.logo as logoCompany, p.title as poste',FALSE)->from('Workeforce w')->join('Role r', 'r.ID = w.RoleID')->join('Company c', 'c.ID = w.CompanyID')->join('poste p', 'p.id = w.poste_id', 'left')->where('w.worker_role <', 100);
And it give me this error.
Fatal error: Uncaught Exception: SQLSTATE[42S22]: Column not found: 1054 Unknown column ‘CONCAT(w.firstName’ in ‘field list’ in /Users/mac/www/application/core/models/workeforce_model.php:100 Stack trace: #0
Advertisement
Answer
Try escaping the quotation marks in your CONCAT.
The fact that your error says that “‘CONCAT(w.firstName'” is not a valid column indicates to me that your DB believes the SELECT ends there.
CONCAT(w.firstName," ",w.lastName) as fullName, ....
Adding in front of the quotation mark will escape it.
You can read more on escaping special characters here:
https://dev.mysql.com/doc/refman/8.0/en/string-literals.html