Nested While Loop Only Displays first row of Data

Question

I'm a novice PHP programmer and am having a problem getting the 'nesting' idea down. When I run the following code, it will display everyone listed from $query3 (FirstName, LastName, etc) from the table, however when it displays the second and third while loops (using $query1 and $query2 respectively), it only does it on the first record (the first employee that is listed) but none of the others. Do I need to/how do I reset the $employeeID variable for each instance of the loop or where exactly do I need to make changes?

//pen some querying
$query1 = mysql_query("SELECT * FROM employees WHERE position='Supervisor' OR position='Area Manager' ORDER BY area, LastName, EmployeeNumber ");
$query2 = mysql_query("SELECT * FROM meetings WHERE meetingGroup='Management'");


//now write some wicked sick code that will change the world!
while($rows3 = mysql_fetch_array($query1)):
    $firstname = $rows3['FirstName'];
    $lasttname = $rows3['LastName'];
    $area = $rows3['area'];
    $employeeID = $rows3['EmployeeNumber'];

echo "<table border='1' align='center' width='75%'><tr><td width='25%'>$firstname $lasttname</td>";

        while($rows1 = mysql_fetch_array($query2)):
            $meetingID = $rows1['meetingID'];
            $meetingName = $rows1['meetingName'];

            echo "<td width='19%'>$meetingName</td>";

            $query3 = mysql_query("SELECT * FROM meetingattendance WHERE employeeid='$employeeID' AND meetingid='$meetingID'");
            while($rows2 = mysql_fetch_array($query3)):
                $attendanceMark = $rows2['employeeattendance'];
                if($attendanceMark = 1)
                    echo "<td><center><strong>X</strong></center></td>";
                else
                echo "No Record";
            endwhile;


        endwhile;

endwhile;
?>

Answer 1

Let's start with the nested loops. I'm going to give an example here that will skip the queries to make this a bit easier to understand.

<?php
$nested1 = array(10,11,12,13,14,15,16,17);
$nested2 = array(20,21,22,23,24,25,26,27);
$nested3 = array(30,31,32,33,34,35,36,37);

while ($rows3 = next($nested1)) {
  echo "First Level: $rows3\n";

  while ($rows1 = next($nested2)) {
    echo " -- Second Level: $rows1\n";

    while ($rows2 = next($nested3)) {
      echo " -- -- Third Level: $rows2\n";
    }
  }
}

The result of this looks a bit like what I expect you are seeing

First Level: 10
 -- Second Level: 20
 -- -- Third Level: 30
 -- -- Third Level: 31
 -- -- Third Level: 32
 -- -- Third Level: 33
 -- -- Third Level: 34
 -- -- Third Level: 35
 -- -- Third Level: 36
 -- -- Third Level: 37
 -- Second Level: 21
 -- Second Level: 22
 -- Second Level: 23
 -- Second Level: 24
 -- Second Level: 25
 -- Second Level: 26
 -- Second Level: 27
First Level: 11
First Level: 12
First Level: 13
First Level: 14
First Level: 15
First Level: 16
First Level: 17

The reason for this is that we are using next in this example which will move the internal pointer to the next element in the array. Since the pointer is never reset the next time we go through the the outer loop we will hit the echo "First Level: $rows\n"; line, then the next while loop. Since the pointer is still at the end of the array, next() will fail and continue on through the rest of the iteration. This is the same with the inner most loop ($rows2).

The reason that I used next in this example is that it acts just like the mysql_fetch_array function used in your query where it will get to the end of the result set and the pointer is never reset back to the beginning.

All this being said, you really should look into using JOINs in your query. Specifically start with a Left JOIN, something like the following

SELECT * FROM employees as e
LEFT JOIN meetingattendance as m ON e.EmployeeNumber = m.employeeid
WHERE e.position='Supervisor' OR e.position='Area Manager' 
ORDER BY e.area, e.LastName, e.EmployeeNumber

Start there and see if that gets you closer to what you are looking for.