我有两张桌子,employee
是父母,license
是孩子。他们都有一个Lic_ID
列作为参考,这个列是PK
中的license
和FK
中的employee
。license
表还有一列Lic_Type
用于保存许可证的名称。
我正在尝试创建一个带有列表框的表,以便可以更新employee
表。列表框value
需要用license.Lic_ID
填充,license.Lic_Type
将显示在option
中。以下是我所拥有的:
(Employee name, Id, etc. called out up here)
<?php
echo "<select name=\"Lic\">";
echo "<option value=\"\">Select...</option>";
$sql = $mysqli->query("SELECT Lic_ID, Lic_Type FROM license");
while($row = $result->fetch_assoc())
{
echo "<option value=\"" . $row['Lic_ID'] . "\">" . $row['Lic_Type'] . "</option>";
}
echo "</select>";
?>
这样很好,它显示了许可证类型,并将值设置为许可证id。如果为员工设置了许可证id,我要做的是
<option selected="selected">
。这段代码不起作用,但我想它说明了我要做的:<?php
echo "<select name=\"Lic\">";
echo "<option value=\"\">Select...</option>";
$sql = $mysqli->query("SELECT license.Lic_ID, license.Lic_Type, employee.Lic_ID FROM employee INNER JOIN license ON employee.Lic_ID = license.Lic_ID");
while($row = $result->fetch_assoc())
{
echo "<option value=\"" . $row['license.Lic_ID'] . "\"";
if($row['employee.Lic_ID'] = $row['license.Lic_ID']){echo "selected=\"selected\";}
echo ">" . $row['license.Lic_Type'] . "</option>";
}
echo "</select>";
?>
有没有办法完成我想做的事?
最佳答案
我想我到底想完成什么可能有些困惑,我很抱歉没有说清楚。不管怎样,我今天无意中找到了答案,所以我想我应该把它贴出来。
$sql1 = ("SELECT Emp_Name, Lic_MAT_ID FROM employee");
if(!$result_employee_query = $mysqli->query($sql1))
{
die ("There was an error getting the records from the employee table");
}
while($employee = $result_employee_query->fetch_assoc())
{
echo "Employee Name: " . $employee['Emp_Name'] . "<br>";
echo "License: ";
echo "<select>";
$sql2 = ("SELECT Lic_MAT_ID, Lic_MAT_Type FROM license_mat");
if(!$result_license_query = $mysqli->query($sql2))
{
die ("There was an error getting the records from the license table");
}
while($license = $result_license_query->fetch_assoc())
{
echo "<option value=\"" . $license ['Lic_MAT_ID'] . "\"";
if($license['Lic_MAT_ID'] == $employee['Lic_MAT_ID'])
{
echo " selected=\"selected\"";
}
echo ">" . $license ['Lic_MAT_Type'] . "</option>";
}
echo "</select><br>";
}
关于php - 联接mySQL表时如何从主键和外键获取值?,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/13447723/