PHP Query using two while loop

Question

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>
    }   
}

Without knowing the data set it's hard to predict HOWEVER what I see is Loop #1 is Select All Persons. Loop #2 is Select All Teachers. So for each Persons, you will show all Teachers. So Persons X Teachers = (Large Dataset) And if the first person in the School is a Teacher, then the first result will always be the same person. — Forbs
– Forbs, Commented Mar 24, 2017 at 3:25

tjfo · Accepted Answer · 2017-03-24 03:32:07Z

1

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'].

answered Mar 24, 2017 at 3:32

tjfo

1,1991 gold badge10 silver badges12 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

questioner Over a year ago

Thank you sir Ill try this one out.

Collectives™ on Stack Overflow

PHP Query using two while loop

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related