在我的实习表中,我有两个外键id_promoteer_interview和id_supervisor_intership。在business_contacts表中,我有一个主键id_business。我正试图从business_contacts表中获取数据,该表与实习表相关联。以下查询是否正确?
public function update_form_business_contact($name_enterprise){
$query = "
SELECT
*
FROM business_contacts
,internships
WHERE
business_contacts.id_business = internships.id_supervisor_internship
AND internships.name_enterprise_internship = '$name_enterprise'";
$result = $this->_db->query($query);
# Go through results of teachers
if($result->rowCount()!=0){
while($row=$result->fetch()){
$contact= new businesscontact ( $row->id_business,$row->firstname_business,$row->lastname_business,$row->service_business,$row->function_business,$row->phone_business,$row->phone_secretary_business,$row->mobile_business);
}
}
return $contact;
}
我的问题是:主键必须同时引用两个外键吗?如果是的话,我该怎么做呢。谢谢你的帮助。
您真的应该使用JOIN
(因为FK就是这样做的)。JOIN
语法告诉DB表是如何相关的(并让其他代码的工作人员清楚地了解这一点)。顺便说一句,您应该避免使用SELECT *
,并列出您想要的字段。
SELECT bc.*, i.*
FROM
business_contacts bc
INNER JOIN internships i ON bc.id_business = i.id_supervisor_internship
WHERE
i.name_enterprise_internship = '$name_enterprise'
您只需要引用相关的字段。在.
您正试图从internships
获取数据,因此使用启动
SELECT *
FROM internships
这将为您提供实习机会,其中包括联系人的标识符。要添加这些联系人的信息,请加入包含该信息的表。在这种情况下,有两个连接,每个联系人一个:
SELECT *
FROM internships
INNER JOIN business_contacts promoter
ON internships.id_promoter_internship = promoter.id
INNER JOIN business_contacts supervisor
ON internships.id_supervisor_internship = supervisor.id
通过给表提供别名(本例中为promoter
和supervisor
),您可以轻松地将同一个表联接两次(或者任意多次),并将其视为单独的表。
这样做的结果将是internships
中的所有记录,以及来自business_contacts
的与internships
记录中的每个密钥相关的附加数据。
您还可以对列进行别名,以便使结果更加明确。类似于:
SELECT
internships.some_field AS some_field,
promoter.some_field AS promoter_some_field,
supervisor.some_field AS supervisor_some_field
FROM
...
还不错,但你可能想试试这个:
<?php
public function update_form_business_contact($name_enterprise){
$query = "
SELECT
*
FROM business_contacts bCon
INNER JOIN internships iShip
ON bCon.id=iShip.id_supervisor_internship
WHERE
iShip.name_enterprise_internship = '$name_enterprise'";
$result = $this->_db->query($query);
# Go through results of teachers
if($result->rowCount()!=0){
while($row=$result->fetch()){
$contact= new businesscontact ( $row->id_business,$row->firstname_business,$row->lastname_business,$row->service_business,$row->function_business,$row->phone_business,$row->phone_secretary_business,$row->mobile_business);
}
}
return $contact;
}