nested while loop mysql_fetch_array

Question

I have a set of nested while loops to compare to mysql queries to each other. Here is the code:

while($row = mysql_fetch_array($resultbat))
{
    $drafted = 0;

while($crossedRow = mysql_fetch_array($crossedAnswer))
{

    if($row['NAME'] == $crossedRow['name'])
    {
        $drafted = 1;
    }
    else
    {
        $drafted = 0;
    }
}
if ($drafted == 1)
{
    echo "<tr class='drafted' id='" . $row['NAME'] . "'>";
}
else if($n&1)
{
    echo "<tr id='" . $row['NAME'] . "'>";
}else
{
    echo "<tr class='alt' id='" . $row['NAME'] . "'>";
}

...}

In the $resultbat is a list of all players, and in the $crossedAnswer is a list of a few players that should be marked. For each player I want to see if they are in the $crossedAnswer list of players. If they are I want to mark the class of that html element to drafted.

Thanks in advance.

Answer 1

I think you'll either need to:

• open the cursor on $crossedAnswer for each row from $resultbat (open it at the beginning of each loop through, and close it at the end, which effectively runs that query for each for in resultbat), or

• fetch all of the rows from the $crossedAnswer resultset into a structure, and look through that structure for each row from $resultbat, (to avoid running the same query multiple times against the database), or

• ensure that both $resultbat and $crossedAnswer are ordered by key value, and do just a single loop; open both cursors, fetch a row from each, and then compare the key values to determine which resultset is "behind", and which one to fetch from (this is more efficient, but is more complicated code to write and test), or

• if these resultsets are from the same MySQL server, rewrite this as a single query, and let MySQL do the work of joining the rows together, and process just a single resultset.

---

Personally, I would opt for the last. I would let MySQL do the join, get a single resultset back, and a single loop.

The query to get the resultset would be of the form:

SELECT b.*
     , IF(a.name IS NOT NULL,1,0) AS drafted
  FROM (
         query_for_$resultbat
       ) b
  LEFT
  JOIN (
         query_for_$crossedAnswer
       ) a
    ON b.name = a.name

Note that if a matching row is found in a, then the drafted column will return a 1, otherwise, it will return a 0.

---

The code to handle this resultset, based on the original code could be something like this:

while($row = mysql_fetch_array($result))
{
    $class = "";
    if($row['drafted'] == 1
    {
        $class = "class='drafted' ";
    }
    else if($n&1)
    {
        $class = "";
    }
    else
    {
        $class = "class='alt' ";
    }

    echo "<tr " . $class . " id='" . $row['NAME'] . "'>";
    ...
}