CODEWITHSUNDEEP
php
questioner
questioner

Reputation: 21

PHP Query using two while loop

My problem is that the firstname in the table data is the same as the second firstname in the table data

What I want to happen is that the first table data firstname should be the student name and the second table data firstname should the teacher's name

Let's assume I have more than one data so I used While loop.

Sample Data:

Students are John Doe and Mike Gard

Teachers are Myka and Jess

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

School Table(teacher or student) Fields: personid, firstname, lastname

Task Table Fields: personid, task, status, teacherid -> this came from the person 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>
    }   
}

Upvotes: 0

Views: 56

Answers (1)

tjfo
tjfo

Reputation: 1189

I don't think you need 2 separate queries for this if I'm understanding correctly. 

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;

Now you can just use one while loop and reference $row1['studentname'] and $row1['teachername'] in place of $row1['firstname'] and $row2['firstname'].

Upvotes: 1

Related Questions