StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POHelp with PHP MySQL join
text
Body
copied!<p>Please help me to understand proper join syntax.</p> <p>I have table named <em>inventory</em> which has: </p> <pre><code>trans_id trans_items items -> item_id trans_user employees -> person_id trans_date trans_comment trans_inventory </code></pre> <p>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.</p> <p>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.</p> <hr> <p>EDIT................................................ so i was enable to display only the last name of the employee with this code:</p> <pre><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'); </code></pre> <p>with the model code:</p> <pre><code>foreach($report_data as $row) { $tabular_data[] = array($row['name'], $row['last_name'],$row['trans_date'], $row['trans_inventory'], $row['trans_comment']); } </code></pre> <p>but i need it to be first name and last name so i did these:</p> <pre><code>$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'); </code></pre> <p>with the model code:</p> <pre><code>foreach($report_data as $row) { $tabular_data[] = array($row['name'], $row['employee'],$row['trans_date'], $row['trans_inventory'], $row['trans_comment']); } </code></pre> <p>it would error if i would use concat function. please help.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload