CODEWITHSUNDEEP
phpmysqlcodeigniterconcatenation
kester martinez
kester martinez

Reputation: 431

concat in php codeigniter

Please help me to understand proper join syntax.

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.

Now what I want to do is to display in HTML the inventory table, but instead of displaying the employee id, I want the employee NAME to be displayed.

EDIT................................................ so i was enable 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');

with the model code:

foreach($report_data as $row)
        {
            $tabular_data[] = array($row['name'], $row['last_name'],$row['trans_date'], $row['trans_inventory'], $row['trans_comment']);
        }

but i need it to be first name and last name so i did these:

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

with the model code:

foreach($report_data as $row)
        {
            $tabular_data[] = array($row['name'], $row['employee'],$row['trans_date'], $row['trans_inventory'], $row['trans_comment']);
        }

it would error if i would use concat function. please help.

Upvotes: 3

Views: 14551

Answers (2)

user3934078
user3934078

Reputation: 21

$query=$this->db->select("id, CONCAT(nombre,".' '.",apellido_paterno) as nombre", false)->
                                from("empleado")->
                                where('empleado', $filtro)->
                                order_by("nombre")->
                                get();

only add false to select

Upvotes: 0

Diablo
Diablo

Reputation: 3418

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.

Upvotes: 22

Related Questions