Using CONCAT() in the SELECT clause of a CodeIgniter active record script creates an invalid query

Question

Please help me to fix my SELECT clause.

I have table named inventory which has:

trans_id    
trans_items items -> item_id         
trans_user  employees -> person_id           
trans_date                   
trans_comment                
trans_inventory

As you can see above, trans_items is a foreign key in items table, and trans_user is a foreign key in employees table and employee id is foreign key to people table.

After joining tables, I want to display in HTML the inventory table, but instead of displaying the employee id, I want the employee NAME to be displayed.

I was able to display only the last name of the employee with this code:

$this->db->select('inventory.*, items.name ,people.last_name');
$this->db->from('inventory');
$this->db->join('items', 'inventory.trans_items = items.item_id' , 'left');
$this->db->join('people', 'inventory.trans_user = people.person_id' , 'left');
$this->db->where('deleted', 0);
$this->db->order_by('trans_date desc');

but I need it to be first name and last name so I did this:

$this->db->select('inventory.*, items.name ,CONCAT(people.first_name, " ",people.last_name) as employee');
$this->db->from('inventory');
$this->db->join('items', 'inventory.trans_items = items.item_id' , 'left');
$this->db->join('people', 'inventory.trans_user = people.person_id' , 'left');
$this->db->where('deleted', 0);
$this->db->order_by('trans_date desc');

However, it errors when I use the CONCAT() function.

Diablo · Accepted Answer · 2011-01-13 08:07:23Z

22

Your select must be like this (second parameter in $this->db->select('your select part', FALSE) ):

$this->db->select('inventory.*, items.name ,CONCAT(people.first_name, " ",people.last_name) as employee', FALSE);
$this->db->from('inventory');
$this->db->join('items', 'inventory.trans_items = items.item_id' , 'left');
$this->db->join('people', 'inventory.trans_user = people.person_id' , 'left');
$this->db->where('deleted', 0);
$this->db->order_by('trans_date desc');

Quote from Codeigniter manual:

If you set it to FALSE, CodeIgniter will not try to protect your field or table names with backticks. This is useful if you need a compound select statement.

edited Jan 13, 2011 at 8:07

answered Jan 7, 2011 at 9:09

Diablo

3,4181 gold badge24 silver badges28 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

mickmackusa · Accepted Answer · 2025-10-30 01:41:44Z

More modern versions of CodeIgniter (certainly from 3.0 and higher) no longer have a problem with your CONCAT expression in the SELECT clause because of the protect_identifiers safeguard. This disables identifiter quoting in expressions including a parenthesis or single quote.

Your query will be correctly rendered with such a script:

return $this->db
    ->select("inventory.*, items.name, CONCAT(people.first_name, ' ', people.last_name) employee")
    ->join('items', 'inventory.trans_items = items.item_id' , 'left')
    ->join('people', 'inventory.trans_user = people.person_id' , 'left')
    ->order_by('trans_date', 'desc')
    ->get_where('inventory', ['deleted' => 0])
    ->result();

Renders:

SELECT `inventory`.*, `items`.`name`, CONCAT(people.first_name, ' ', people.last_name) employee
FROM `inventory`
LEFT JOIN `items` ON `inventory`.`trans_items` = `items`.`item_id`
LEFT JOIN `people` ON `inventory`.`trans_user` = `people`.`person_id`
WHERE `deleted` = 0
ORDER BY `trans_date` DESC

If you need to explicitly apply identifier quoting, I recommend a separate select() call for clarity.

return $this->db
    ->select('inventory.*, items.name')
    ->select(
        vsprintf(
            "CONCAT(%s, ' ', %s) %s",
            $this->db->escape_identifiers(['people.first_name', 'people.last_name', 'employee'])
        )
    )
    ->join('items', 'inventory.trans_items = items.item_id' , 'left')
    ->join('people', 'inventory.trans_user = people.person_id' , 'left')
    ->order_by('trans_date', 'desc')
    ->get_where('inventory', ['deleted' => 0])
    ->result();

Renders:

SELECT `inventory`.*, `items`.`name`, CONCAT(`people`.`first_name`, ' ', `people.last_name`) `employee`
FROM `inventory`
LEFT JOIN `items` ON `inventory`.`trans_items` = `items`.`item_id`
LEFT JOIN `people` ON `inventory`.`trans_user` = `people`.`person_id`
WHERE `deleted` = 0
ORDER BY `trans_date` DESC

Collectives™ on Stack Overflow

Using CONCAT() in the SELECT clause of a CodeIgniter active record script creates an invalid query

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related