php - 在 php codeigniter 中连接

Question

请帮助我理解正确的连接语法。

我有一个名为 inventory 的表，它有:

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

如上所示，trans_items 是 items 表的外键，trans_user 是 employees 表的外键，employee id 是 people 表的外键。

现在我想做的是以 HTML 格式显示库存表，但我不显示员工 ID，而是希望显示员工姓名。

---

编辑...................................... ..所以我可以使用以下代码仅显示员工的姓氏:

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

模型代码:

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

但我需要它是名字和姓氏，所以我做了这些:

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

模型代码:

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

如果我使用 concat 函数会出错。请帮忙。

Answer 1

您的选择必须像这样($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');

引自 Codeigniter 手册:

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.