Concat in Codeigniter Active Record

Question

I am trying a Concat for an autocomplete, Using CI's Active Record.

My Query is :

$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 an MySQL Error from this saying:

Error Number: 1054

Unknown column 'name' in 'where clause'

Which is true, However I have just created in my Concat clause. I ideally need $term to match the Concatenated firstname and surname fields.

Any ideas what I can do to improve this? I am considering just writing this as an 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 running multiple selects. So just put it as a single select. It's probably that the 2nd one is overriding the first one and thus overwriting the concatenation to create the name column.

Answer 2

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

Try like above 100% it will work in ci.

Answer 3

If cryptic solution doen't work then 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: MySQL select with CONCAT condition

Answer 4

You have to SELECT the fields that you want concat like so:

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