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

score 17 · Accepted Answer · answered Jun 18 '13 at 11:50

17

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

answered Jun 18 '13 at 11:50

kittycat

14,983
9
55
80

Thanks Cryptic. I have found myself an alternate solution, In the where clause I concatenated like so : $this->db->where("CONCAT(user_firstname, '.', user_surname) LIKE '%".$term."%'", NULL, FALSE); – StuBlackett Jun 18 '13 at 12:59
that was epic solution, would you mind to explain it? – Adi Prasetyo Sep 16 '17 at 16:57

score 11 · Answer 2 · edited Mar 03 '15 at 10:34

11

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

Try like above 100% it will work in ci.

edited Mar 03 '15 at 10:34

Rubik

1,431
1
18
24

answered Mar 03 '15 at 10:25

vinod inti

637
6
5

2

Only this works for me in CI 3. In CI 2, parenthesis was not a problem. – Rajitha Bandara Aug 14 '15 at 07:11
This is the actually Answer to concat two fields in CI, fields must be not null – Hassan Ali Shahzad Jul 25 '16 at 07:50

score 4 · Answer 3 · edited May 23 '17 at 11:54

4

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

edited May 23 '17 at 11:54

Community

1
1

answered Jun 18 '13 at 11:54

mrsrinivas

34,112
13
125
125

score 4 · Answer 4 · edited Jun 04 '14 at 20:40

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

edited Jun 04 '14 at 20:40

Marc Towler

705
11
32

answered Jun 04 '14 at 20:20

user3708698

41
1

score 4 · Answer 5 · edited Jan 07 '15 at 11:03

4

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

edited Jan 07 '15 at 11:03

Pete

57,112
28
117
166

answered Jan 07 '15 at 10:55

Developer

477
1
4
5

Concat in Codeigniter Active Record

5 Answers5

Linked

Related