CODEWITHSUNDEEP
codeigniteractiverecord
StuBlackett
StuBlackett

Reputation: 3857

Concat in Codeigniter Active Record

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

Upvotes: 14

Views: 51037

Answers (5)

vinod inti
vinod inti

Reputation: 667

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

Try like above 100% it will work in ci.

Upvotes: 11

Developer
Developer

Reputation: 477

This will also solve the issue:

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

Upvotes: 4

user3708698
user3708698

Reputation: 41

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

Upvotes: 4

mrsrinivas
mrsrinivas

Reputation: 35404

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

Upvotes: 4

kittycat
kittycat

Reputation: 15045

$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.

Upvotes: 18

Related Questions