mysqli连接两个表以匹配用户名和相应的用户ID

Question

I'm having a hard time figuring this out..

I have two tables "teacher_info" and "section_info".

In my forms I included all the attributes in my section_info table except I used teacher name instead of teacher id for easy selection which is a dropdownlist of teacher's name, this is my code

<?php
include("anhsis.php");
mysqli_select_db($con,"anhsis");
$result= mysqli_query($con,"SELECT t_lname,t_fname FROM teacher_info");
echo"<select name='adviser' class='form-control' required>";
echo"<option value='0'>--Select Adviser--</option>";
while ($row=mysqli_fetch_array($result)) {
echo "<option value='".$row['t_lname']."".$row['t_fname']."'>".$row['t_lname'].", ".$row['t_fname']."</option>";
}
echo'</select>'
?>         

and heres my php code to insert data in "section_info"

<?php
        include_once('anhsis.php');
        $room_id = $_POST['room_id'];
        $section = $_POST['section'];
        $adviser = $_POST['teacher_id'];
        $level = $_POST['level'];
        $curriculum = $_POST['curriculum'];

mysqli_select_db($con,"anhsis");
    $result= mysqli_query($con,"SELECT * FROM section_info WHERE room_id= '$room_id'");
    if (mysqli_num_rows($result)>0){
        echo '<script type="text/javascript">';
        echo 'alert("TIN No already exist!")';
        echo '</script>';
    }
else{

    mysqli_query($con,"INSERT INTO section_info VALUES('$room_id','$section','$adviser','$level','$curriculum')");
    }
?> 

my problem is that in my section_info theres no attribute of teacher's name, instead it has teacher_id. So how am I going to insert teacher_id from the "teacher_info" table to the "section_info" table by just selecting the teacher's name in my dropdownlist. Or is it possible? Thanks!

Answer 1

$result= mysqli_query($con,"SELECT t_lname,t_fname,teacher_id FROM teacher_info");

echo "<select name='adviser' class='form-control' required>";
echo "<option value='0'>--Select Adviser--</option>";
while ($row=mysqli_fetch_array($result)) {
    echo "<option value='".$row['teacher_id']."'>".$row['t_lname'].", ".$row['t_fname']."</option>";
}

This way you will have teacher_id value in $adviser variable, ready to use.