Foreach w/Selection DDList Using Lookup Tables

BLottman

I'm having a problem conceptually understanding how to display two selection lists (i.e., a parent list and a child list) that are extracted from two lookup tables.

I've built the foreach extraction and selection list for the parent rows of one of the lookup tables but I'm having trouble understanding how to display only rows from the child lookup table that are associated with the selected option of the parent lookup table.

For example:

I have two lookup tables = Manufacturer and Model Lookup tables.
Two rows exist (i.e., Briggs-Stratton and Tecumseh) in the manufacturer table.
Four rows (BS-1, BS-2, T-1, and T-2) exist in the Model table.
BS-1 and BS-2 rows in the Model table are associated with the Briggs-Stratton row in the Manufacturers table.
T-1 and T-2 rows of the Model table are associated with the Tecumseh row of the Manufacturers table.

Here's my code for selecting the lookup table rows and for creating the Manuf and Model drop down selection lists and for the subsequent form.

<?php
include("settings.inc");
mysql_select_db($mydb, $connection);

// Create the Category Array from the Lookup Table.
$sqlCategory = "Select ID,NAME From CATEGORIES";
$myCategoryresult=@($sqlCategory);
while($rowCategory = mysql_fetch_array($myCategoryresult))
{
$Category[] = $rowCategory["NAME"];
$SelectBoxCategory[] = "\n<option value\"".$rowCategory['NAME']."\">".$rowCategory['NAME']."</option>";
}

// Create the Manufacturer Array from the Lookup Table.
// The Manufacturer table contains columns ID, MANUF,
// DESC.
$sqlManuf = "Select ID,MANUF From MANUF";
$myManufresult=@($sqlManuf);
while($rowManuf = mysql_fetch_array($myManufresult))
{
$Manuf[] = $rowManuf["MANUF"];
$SelectBoxManuf[] = "\n<option value\"".$rowManuf['MANUF']."\">".$rowManuf['MANUF']."</option>";
}

// Create the Model Array from the Lookup Table.
// The Model table contains columns ID, MANUFID, NAME,
// DESC.

$sqlModel = "Select ID,MANUFID,NAME From MODEL";
$myModelresult=@($sqlModel);
while($rowModel = mysql_fetch_array($myModelresult))
{
$Model[] = $rowModel["NAME"];
$ManufID[] = $rowModel["MANUFID"];
$SelectBoxModel[] = "\n<option value\"".$rowModel['MODEL']."\">".$rowModel['MODEL']."</option>";
}

<form action="<? $PHP_SELF ?>" method=post>
<tr><td align=left>
<span class=s1>
Product Category
</span>
</td>
<td align=left>
<select name="PCat">
<?php
foreach($SelectBoxCategory as $option)
{
echo $option."\n";
}
?>
</select>
</td>

 <tr><td align=left>
    <span class=s1>
       Manufacturer
    </span>		</td>
    <td align=left>
      <select name="PManuf">
      <?php
          foreach($SelectBoxManuf as $option)
           {
            echo $option."\n";
           }
      ?>
      </select>
    </td>

 <tr><td align=left>
    <span class=s1>
       Model
    </span>		</td>
    <td align=left>
      <select name="PModel">
      <?php
          foreach($SelectBoxModel as $option)
           {
            echo $option."\n";
           }
      ?>
      </select>
    </td>

</form>

</table>

I have a suspicion that I'm supposed to nest another foreach in the selection portion of the form but I'm not sure how to link the parent and child rows. The common column in the two lookup tables are the ID column in the MANUF table and the MANUFID in the MODEL table.

I'd really appreciate any help you can provide me to increase my understanding of how Parent and Child lookup tables work.

Thanks in advance.
😕

BLottman

In the initial SQL query, I changed from separate code segments of gathering rows to using one code segment that selects columns from both MANUF and MODEL lookup tables

$sql = "Select MANUF.ID,MANUF.MANUF,MODEL.MANUFID,MODEL.NAME From MANUF,MODELS WHERE MANUF.ID=MODEL.MANUFID";
$myresult=@($sql);
while($row = mysql_fetch_array($myresult))
{
$Manuf[] = $row["MANUF"];
$Model[] = $row["MODEL"];
$SelectBoxManuf[] = "\n<option value\"".$row['MANUF']."\">".$row['MANUF']."</option>";
$SelectBoxModel[] = "\n<option value\"".$row['MODEL']."\">".$row['MODEL']."</option>";
}

According to the error messages I'm receiving, there's something wrong with my syntax for the join $sql statement. Any ideas?