如何在加入mySQL表时从主键和外键中获取值？

Question

I have two tables, employee as the parent and license as the child. They both have a Lic_ID column for reference, this column is the PK in license and the FK in employee. The license table also has a column Lic_Type which holds the name of the license.

I am trying to create a table with list boxes so the employee table can be updated. The list box value needs to be populated with the license.Lic_ID and the license.Lic_Type is to be displayed in the option. Here is what I have:

(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>";
?>

So that works good, it shows the license type and has the value set to the license id. What I want to do is have <option selected="selected"> if the license id is set for an employee. This code doesn't work, but I think it illustrates what I'm trying to do:

<?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>";
?>

Is there a way to accomplish what I'm trying to do?

Answer 1

I think there may have been some confusion on what exactly I was trying to accomplish, I apologize for not being very clear. Anyways, I stumbled over the answer today, so I thought I should post it.

$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>";
    }