PHP compare the results of two while loops

Question

I have the following queries:

$state = get['state'];

$getPossibleIDs = "SELECT * FROM ".$report_group_lookup;
     $qPossibleID = $conn->prepare($getPossibleIDs);
          $qPossibleID -> execute();
               while($rowPossible = $qPossibleID->fetch())
                    {
                         $possibleID = $rowPossible['id'];
                         $possibleName = $rowPossible['name'];
$getSpecificIDs = "SELECT * FROM rbs_report_type_2_specific WHERE rbs_report_type_id =".$state;
     $qSpecifcID = $conn -> prepare($getSpecificIDs);
          $qSpecifcID -> execute();
               while($rowSpecfics = $qSpecifcID->fetch())
                    {
                         $specificIDs = $rowSpecfics['rbs_specific_id'];

                         if($possibleID == $specificIDs)
                              {
                                   echo $possibleName."-Yes<br/>";
                              }
                         else
                              {
                                   echo $possibleName."-No<br/>";
                              }
                    }
}

What I am trying to do is compare the results of two while look ups, the first lookup, getPossibleIDs, finds all the possible IDs that the statement can look at. The second, getSpecificIDs, finds only the ones that would have been selected before.

It almost works, but the 2nd query looks at a table with the following info:

------------------------------------
|rbs_report_type_id|rbs_specific_id|
| 1                | 1             |
| 1                | 2             |
| 1                | 3             |

So the result I get is repeated three times, once for each rbs_spefic_id:

HDD Coordinator-No
HDD Coordinator-No
HDD Coordinator-No
Rig Manager-Yes
Rig Manager-No
Rig Manager-No
Driller-No
Driller-Yes
Driller-No
Tank Hand-No
Tank Hand-No
Tank Hand-Yes

Instead of:

HDD Coordinator-No
Rig Manager-Yes
Driller-Yes
Tank Hand-Yes

Any suggestions on how I could change the output accordingly?

Thanks

Answer 1

Instead of doing an inner loop, use a JOIN query to do it all in the 1st query

$getPossibleIDs = "
    SELECT rgl.id, rgl.name, 
    CASE WHEN rrt2s.rbs_specific_id IS NOT NULL THEN 'Yes' ELSE 'NO' END as `match`
    FROM ".$report_group_lookup." rgl 
    LEFT JOIN rbs_report_type_2_specific rrt2s 
         ON rrt2s.rbs_specific_id = rgl.id 
         AND rrt2s.rbs_report_type_id = ?";
$qPossibleID = $conn->prepare($getPossibleIDs);
$qPossibleID -> execute(array($state));
while($rowPossible = $qPossibleID->fetch())
     {
      echo $rowPossible['name']. " -".$rowPossible['match']."<br />";
     }

Answer 2

If you are only looking for the first value then maybe you can avoid using the second while loop altogether. You could do something like this:

$getPossibleIDs = "SELECT * FROM ".$report_group_lookup;
$qPossibleID = $conn->prepare($getPossibleIDs);
$qPossibleID -> execute();
while($rowPossible = $qPossibleID->fetch()) {
    $possibleID = $rowPossible['id'];
    $possibleName = $rowPossible['name'];
    $getSpecificIDs = "SELECT * FROM rbs_report_type_2_specific WHERE rbs_report_type_id =".$state;
    $qSpecificID = $conn -> prepare($getSpecificIDs);
    $qSpecificID -> execute();
    $rowSpecifics = $qSpecificID->fetch()
    if ($rowSpecifics)   {                      //if a value is present then continue execution
        $specificIDs = $rowSpecifics['rbs_specific_id'];
        if ($possibleID == $specificIDs) {
            echo $possibleName."-match<br/>";
        } else {
            echo $possibleName."-No<br/>";
        }
    }
}

This will check to see if a value exists in the second lookup. If so then it will execute your logic, but only does it once.