使用两个while循环的PHP查询

Question

我的问题是表数据中的名字与表数据中的第二个名字相同

我想要的是第一个表数据名字应该是学生的名字，第二个表数据名字应该是老师的名字

假设我有多个数据，所以我使用了While循环。

示例数据：

学生是John Doe和Mike Gard

老师是麦卡和杰西

1st record: John Doe  Erase the Board  Done   Myka
2nd record: Mike Gard Erase the Board  Done   Myka (This should be Jess)

校表(教师或学生)字段: personid，firstname，lastname

任务表字段: personid、task、status、teacherid ->此字段来自人员id

sql1 = select * from School S Inner Join Task T on S.personid = T.personid  // I want to get the ID of the students to get the names
sql2 = select * from School S Inner Join Task T on S.personid = T.teacherid // I want to get the ID of the teacher to get the names

<th>Student Name</th>
<th>Task</th>
<th>Teacher Name</th>
<th>Status</th >

$result1 = mysqli_query($conn, $sql1);
$result2 = mysqli_query($conn, $sql2);

while ($row1 = mysqli_fetch_array($result1)) {
    while ($row2 = mysqli_fetch_array($result2)){

        <td>".$row1['firstname']."</td>//Name of the student
        <td>".$row1['task']."</td>
        <td>".$row2['firstname']."</td>//Name of the teacher
        <td>".$row1['result']."</td>
    }   
}

Answer 1

如果我理解正确的话，我不认为你需要两个单独的查询。

SELECT T.*,
S1.firstname AS studentname,
S2.firstname AS teachername
FROM Task T
LEFT JOIN School S1 ON T.personid=S1.personid
LEFT JOIN School S2 ON T.teacherid=S2.personid;

现在，您只需使用一个while循环并引用$row1'studentname‘和$row1'teachername’来代替$row1'firstname‘和$row2'firstname’。