Help with PHP MySQL join

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->jo开发者_如何学JAVAin('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.

This should work for you

   $this->db->select('inventory.*, items.name ');
   $this->db->from('inventory');
   $this->db->join('items', 'inventory.item_id = items.item_id');
   $this->db->order_by('trans_date desc');

   return $this->db->get()->result_array();

SELECT
    t.trans_id,
    i.item_name,
    t.trans_user,
    t.trans_date,
    t.trans_comment,
    t.trans_inventory
FROM
    trans as t
        LEFT JOIN
            items as i 
                ON
                    i.item_id = t.trans_items
LIMIT 0, 30;

Some assumsions are made in the above query like the item name feild is item_name, main table name is trans etc.

What i have used here is left join.

In this example using left join is the best thing to do as it only selects one row from the items table.

More on joins: MySQL :: MySQL 5.0 Reference Manual :: 12.2.8.1 JOIN Syntax

--- Update. PS: This is posted before you update about the CodeIgniter. And i am not familar with it. So you can either remove this answer or leave it here for others to refer.

Following will work for you.

$this->db->select('items.name , inventory.trans_user, inventory.trans_date, inventory.trans_inventory, inventory.trans_comment');
$this->db->from('inventory');
$this->db->join('items', 'inventory.item_id = items.item_id');
$this->db->order_by('inventory.trans_date desc');

return $this->db->get()->result_array();

EDIT -------------------------

Try out following. It will help..

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

NOTE : I have assumed that firstname and lastname properties are part of employees table. you can change it to table name of whatever it belongs to.

Thanks!

Hussain.