Concat in Active Codeigniter Record

Question

I am trying to run Concat to autocomplete using an active CI record.

My request:

$this->db->select("CONCAT(user_firstname, '.', user_surname) AS name", FALSE); $this->db->select('user_id, user_telephone, user_email'); $this->db->from('users'); $this->db->where('name', $term); 

I keep getting the MySQL error from this statement:

Error Number: 1054

Unknown column name 'in' where clause '

That's true, but I just created Concat in my article. I ideally need $ term to match the hidden name and last name fields.

Any ideas what I can do to improve this? I am considering just writing this as a flat MySQL Query ..

Thanks in advance

Answer 1

 $this->db->select('user_id, user_telephone, user_email, CONCAT(user_firstname, '.', user_surname) AS name', FALSE); $this->db->from('users'); $this->db->where('name', $term); 

Not sure why you are using multiple samples. So just put it as a separate choice. Probably the 2nd of them overrides the first and thus overwrites the concatenation to create the name column.

Answer 2

 $this->db->select("CONCAT((first_name),(' '),(middle_name),(' '),(last_name)) as candidate_full_name"); 

Try that above 100% it will work in qi.

Answer 3

You must select the fields you want to make the same:

 $this->db->select('user_id, user_telephone, user_email, user_firstname, user_surname, CONCAT(user_firstname, '.', user_surname) AS name', FALSE); $this->db->from('users'); $this->db->where('name', $term); 

Answer 4

This will also solve the problem:

 $this->db->select('user_id, user_telephone, user_email, user_firstname, user_surname, CONCAT(user_firstname,user_surname) AS name', FALSE); $this->db->from('users'); 

Answer 5

If the cryptic solution does not work, try it.

 $query = "SELECT * FROM ( SELECT user_id, user_telephone, user_email, CONCAT(user_firstname, ' ', user_surname) name FROM users ) a WHERE name LIKE '%".$term."%'"; $this->db->query($query); 

Source: Choosing MySQL with CONCAT