如何显示外键数据 [英] How to display foreign key data

问题描述

我有三张表(休假、员工、部门)

I have three table (leave, employee, department)

这是我的数据库

员工表:{ Emp_ID (PK), Emp_Fname, Emp_Lname, ContactNo_HP, ContactNo_Home, Emp_Email, Dept_ID(FK)}

employee table: { Emp_ID (PK), Emp_Fname, Emp_Lname, ContactNo_HP, ContactNo_Home, Emp_Email, Dept_ID(FK)}

离开表:{ Leave_ID (PK), Date_Apple, Leave_Type, Leave_Start, Leave_End, Status, Emp_ID(FK)}

leave table: { Leave_ID (PK), Date_Apple, Leave_Type, Leave_Start, Leave_End, Status, Emp_ID(FK)}

部门表:{ Dept_ID (PK), Dept_Name, Dept_Desp}

department table: { Dept_ID (PK), Dept_Name, Dept_Desp}

当我点击详细信息"时，它会出现一个新页面.只有特定的

When i click "Detail" it will come out a new page. Only specific

<tr>
    <td><?php echo $row["Leave_ID"];?></td>
    <td><?php echo $row["Emp_ID"];?></td>
    <td><?php echo $row["Date_Apply"];?></td>
    <td><?php echo $row["Leave_Type"];?></td>
    <td><?php echo $row["Leave_Start"];?></td>
    <td><?php echo $row["Leave_End"];?></td>
    <td><?php echo $row["Status"];?></td>
    <td><a href="app_status.php?id=<?php echo $row[Leave_ID];?>"
           target="_blank">Detail</a></td>
</tr>

在新页面中，我在显示(Emp_Name、Dept_Name、ContactNo_HP、ContactNo_Home)时遇到问题，因为 Emp_ID 是休假表中的外键，而 Dept_Name 位于部门表中，该表将 Dept_ID 链接为员工表中的外键.

In the new page I having a problem to display (Emp_Name, Dept_Name, ContactNo_HP, ContactNo_Home) as Emp_ID is foreign key in leave table and Dept_Name is in department table which link Dept_ID as a foreign key in employee table.

我在新页面中使用以下语句来显示数据

I use following statement in the new page to display data

<?php
    $result = mysql_query("select * from `leave`");
    $row = mysql_fetch_assoc($result);
?>

推荐答案

首先你必须在详情页中检索 Leave_ID:

First of all you have to retrieve Leave_ID in the details page:

$leaveID = $_GET['Leave_ID'];

然后你必须编写一个从三个表中选择并将其分配给一个变量(如 $sql)的 SQL 代码:

Then you have to write a SQL code that select from the three tables and assign it to a variable (like $sql):

select t1.Emp_Name, t2.Dept_Name, t1.ContactNo_HP, t1.ContactNo_Home
  from employee as t1 inner join department as t2
       on t1.Dept_ID = t2.Dept_ID inner join `leave` as t3
       on t3.Emp_ID = t1.Emp_ID
 where t3.Leave_ID = ?

此时我建议您使用带有准备好的语句的 MySQLi 对象:

At this point I suggest you to use a MySQLi object with a prepared statement:

$mysqli = new mysqli("localhost", "my_user", "my_password", "world");
$stmt = $mysqli->prepare($sql);
$stmt->bind_param("i", $leaveID);
$stmt->execute();
$stmt->bind_result($empName, $deptName, $contactNoHP, $contactNoHome);
while ($stmt->fetch()) {
    \\your code to display details here
}
$stmt->close();
$mysqli->close();

请注意:

1. "leave" 是一个保留字，所以你必须在你的 SQL 语句中用反引号将它括起来；
2. 您的页面中有 Emp_ID，因此您应该传递它而不是 Leave_ID；这将帮助您编写更清晰的 SQL 语句.

这篇关于如何显示外键数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！