问题描述
我有三张表(休假、员工、部门)
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();
请注意:
- "leave" 是一个保留字,所以你必须在你的 SQL 语句中用反引号将它括起来;
- 您的页面中有 Emp_ID,因此您应该传递它而不是 Leave_ID;这将帮助您编写更清晰的 SQL 语句.
这篇关于如何显示外键数据的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!