关于php：如何连接四个表来显示具体数据
how to join four table to display specific data
我在加入表格时遇到了一些麻烦。我有四张桌子
"设备"表
|
1
2 3 4 |
equipment_id equipment_name
----------- -------------- 1 Motor Cycle 2 Private Car |
==================================
这是我的项目表
|
1
2 3 4 |
project_id project_name
---------- ------------ 1 xyz trading 2 abc private ltd |
=================================
这是出租桌
|
1
2 3 4 5 |
rental_id project_id equipment_id Rental_date
--------------------------------------------------------- 1 2 1 12/10/2018 2 2 2 15/09/2018 3 1 2 20/10/2018 |
这是删除表
|
1
2 3 4 5 6 |
remove_id project_id equipment_id removal_date
--------------------------------------------------------- 1 2 1 null 2 2 2 14/10/18 these are my table, now i want to display data of single equipment (eg: equipment no 2) in which project this equipment ran, when was the rental date and when was the removal date from a project. |
我想这样显示..(使用 Codeigniter)
设备2日志
=======
|
1
2 3 4 |
SN Project Name Rental date Removal date
--- ------------ ----------- ------------ 1 ABC pvt Ltd 15/09/2018 14/10/2018 2 xyz trading 20/10/2018 Null |
控制器传递给模型
|
1
|
$data['eqipment_details']=$this->Equipment_model->getLog($equipment_id);
|
请帮我查询
相关讨论
- "SN"来自哪个列和表？
- SN 只是为了计算结果，使用 $count=1; $count=$count 1;在视野中
- 我在我的模型中使用了这个查询，我知道这是不正确的 public function getLog($equipment_id=0) { return $this->db->select( \'*\' ) ->from(\' rent AS r \ ') ->join(\'设备 AS e\', \'e.eq_id = r.eq_id\') ->join(\'移除 AS m\', \'m.eq_id = e.eq_id\') ->join(\'project AS p\', \'p.project_id = r.project_id\') ->where(\'r.eq_id\', $eq_id) ->get()->result_array(); }
不得不猜测你的表名是什么，但你应该能够从中调整：
|
1
2 3 4 5 6 7 8 9 10 11 12 |
SELECT
(@cnt := @cnt + 1) AS SN, PROJECT.project_name AS Project Name, RENTAL.Rental_date AS Rental date, REMOVAL.removal_date AS Removal date FROM rental_table RENTAL LEFT JOIN project_table PROJECT ON (RENTAL.project_id = PROJECT.id) LEFT JOIN equipment_table EQUIPMENT ON (RENTAL.equipment_id = EQUIPMENT.id) LEFT JOIN removal_table REMOVAL ON (PROJECT.id = REMOVAL.project_id AND PROJECT.equipment_id = REMOVAL.equipment_id) WHERE EQUIPMENT.id = 2; |
我强烈建议与您的命名约定保持一致，您将骆驼大小写和标题大小写与下划线混合。
相关讨论
- 没问题。您能否将问题标记为已回答，谢谢:)