Compare results from two different mysqli_fetch_array with PHP

Question

I created a DB where I am saving information about apps. Those apps can be linked to a vertical. I designed the DB in such way that I have one table for standard information about each application which includes an auto-incremented ID a name etc. I have another table that lists all different industries/verticals.

Except for those 2 tables, I have a third one connecting the two, which looks like this (I'm showing it in a PHP array because I think it's easier to read):

  $verticals_joint = array(
  array('vertical_joint_id' => '1','vertical_id' => '11','demo_id' => '173'),
  array('vertical_joint_id' => '2','vertical_id' => '12','demo_id' => '173'),
  array('vertical_joint_id' => '3','vertical_id' => '13','demo_id' => '173'),
  array('vertical_joint_id' => '4','vertical_id' => '2','demo_id' => '174'),
  array('vertical_joint_id' => '5','vertical_id' => '4','demo_id' => '174'),
  array('vertical_joint_id' => '6','vertical_id' => '3','demo_id' => '175'),
  array('vertical_joint_id' => '7','vertical_id' => '4','demo_id' => '175'),
  array('vertical_joint_id' => '8','vertical_id' => '8','demo_id' => '176'),
  array('vertical_joint_id' => '9','vertical_id' => '9','demo_id' => '176'),
  array('vertical_joint_id' => '10','vertical_id' => '1','demo_id' => '92'),
  array('vertical_joint_id' => '11','vertical_id' => '3','demo_id' => '92'),
  array('vertical_joint_id' => '12','vertical_id' => '12','demo_id' => '92'),
  array('vertical_joint_id' => '13','vertical_id' => '1','demo_id' => '179'),
  array('vertical_joint_id' => '14','vertical_id' => '3','demo_id' => '179'),
  array('vertical_joint_id' => '15','vertical_id' => '4','demo_id' => '179'),
  array('vertical_joint_id' => '16','vertical_id' => '2','demo_id' => '180'),
  array('vertical_joint_id' => '17','vertical_id' => '4','demo_id' => '180')
);

The vertical_id connects the industry/vertical with the application/demo. The demo_id references the application (or demo as I called it).

Now comes my issue. I came up with a while-loop in PHP and I nested an if-statement into it so I can display all verticals and additionally mark which verticals the given application was created for. E.g. this is what I want to achieve:

<select id="Vertical[]" class="form-control verticalText" multiple="multiple">
<option value="1">Education</option>
<option value="2">Manufacturing</option>
<option value="3">State, Local or Provincial Government</option>
<option value="4">Federal Government</option>
<option value="5">Other</option>
<option value="6">Transportation and Logistics</option>
<option value="7">Healthcare</option>
<option value="8">Petrochemical</option>
<option value="9">Utilities</option>
<option value="10">Public Safety</option>
<option value="11" selected>Hospitality</option>
<option value="12" selected>Wholesale Distribution</option>
<option value="13" selected>Human Services</option>
<option value="14">Retail</option>    

That's what I want to achieve but instead when I use my while loop it only correctly selects Hospitality and doesn't select Wholesale Dist or Human Services.

My while-loop + nested if-statement look like this:

//Gathers the names of all existing verticals.
$vertical_query  = "SELECT * FROM demos.verticals";
$result_vertical = mysqli_query($con, $vertical_query);
//Gathers information about the particular application you are comparing with. 
//It retrieves the verticals for a particular app/demo id. 
//For this example I used id 173
$joint_query  = "SELECT o.vertical_id, o.vertical_name FROM demos.alldemos alld
    LEFT JOIN demos.verticals_joint j ON alld.id = j.demo_id
    LEFT JOIN demos.verticals o ON o.vertical_id = j.vertical_id
    WHERE alld.id = 173";
$result_joint = mysqli_query($con, $joint_query);
$compare      = mysqli_fetch_array($result_joint);
$output       = '';
// while there are Verticals to be listed.
while ($row_vertical = mysqli_fetch_array($result_vertical)) {
    // If a vertical is already selected for this particular app id,        
    if ($compare['vertical_name'] == $row_vertical["vertical_name"]) {
        //then add the word select to the option so it will be selected
        $output .= '<option value="'.$row_vertical["vertical_id"].'" selected>'.$row_vertical['vertical_name'].'</option>';
    } elseif ($compare['vertical_name'] != $row_vertical["vertical_name"]) {
        //Otherwise just print out the normal option without 'selected'.
        $output .= '<option value="'.$row_vertical["vertical_id"].'">'.$row_vertical["vertical_name"].'</option>';
    }
}
echo $output;

Would you know why it only compares the first item from my SQL join against the verticals from my other SELECT query? Would you suggest to do things differently?

Answer 1

In order to check all matching conditions between both queries, you would need to run two loops between the $compare and the $row_vertical arrays. Right now you are only checking all $row_vertical rows to the first row of $compare (i.e., Hospitality). And since values can be overwritten in nested looping, include a break 1 when condition is met:

while ($row_vertical = mysqli_fetch_array($result_vertical)) {        
       while ($compare = mysqli_fetch_array($result_joint)) {

              if ($compare['vertical_name'] == $row_vertical["vertical_name"]) {
                 $output = ...;
                 break 1;
                 ...
       }
}

However, nested loops is not needed as you run a LEFT JOIN on the same table where unmatched records will return NULL. Hence, there is no need to run and fetch the redundant first query but just run one $compare query and then check for NULL.

while ($compare = mysqli_fetch_array($result_joint)) {
    // If a vertical is already selected for this particular app id,        
    if (is_null($compare['vertical_name']) == false) {
        //then add the word select to the option so it will be selected
        $output .= '<option value="'.$compare["vertical_id"].'" selected>'.$compare['vertical_name'].'</option>';

    } elseif (is_null($compare['vertical_name']) == true) {
        //Otherwise just print out the normal option without 'selected'.
        $output .= '<option value="'.$compare["vertical_id"].'">'.$compare["vertical_name"].'</option>';

    }
}

And Vertical values should not repeat if you keep the WHERE condition on one specific demo_id.