Mutliple table query/result problem

At this point, this is more a matter of theory as opposed to what line for line code I should use.

I populate an array with `mfgID` and `mfgName` from table `mfg`.

Then I pull all the `prodName` and `prodMfg` (which is equal to the mfgID. I know, I should have just called it `mfgID`, but the `mfg` table came about after the original db was setup. I could change it over if it is necessary to accomplish the goal) out of `products` and loop through the results.

THE PROBLEM:
I need to associate the `prodMfg` with `mfgName` to display the manufacturer of the product. I haven't been able to get this to work. Originally, I had put a query to `mfg` to get the `mfgName` inside the loop results, but this made the original results stop looping and I ended up with one product.

I am definitely open to a better way to go about this.

This isn't hard, I'm just not sure exactly what you want....

You could possibly do:

"select mfgID FROM onetable WHERE onetable.mfgName = othertable.prodMfg"

I'm not sure that is entirely correct, though ;)

er wait....yeh, I'm not exactly sure what you want. Give us some code, lol.

I have stripped away all the html, here is the code I am working with:

<?
$query = "SELECT * FROM mfg";
$result = mysql_select_db ($db, $connection);
$result = mysql_query ($query);
while ($myrow = mysql_fetch_array($result)) {
$mfgName = $myrow[1];
$mfgID = $myrow[0];
}
?>


I need to get the $mfgName to display as $prodMfg in the results below:
<?

$query = "SELECT * FROM products WHERE prodStore = 'Health Food' ORDER BY prodName";
$result = mysql_select_db ($db, $connection);
$result = mysql_query ($query);
while ($myrow = mysql_fetch_array($result)) {
$prodID = $myrow[0];
$catID = $myrow[1];
$prodName = $myrow[2];
$prodMfg = $myrow[3];
$prodDesc = $myrow[5];
$photoID = $myrow[10];


?>

<? echo $prodName ?> by
<? echo $prodMfg //(This needs to be the $mfgName) ?>

<? } ?>

$prodMfg stores a $mfgID.
I need to echo the $mfgName (as $prodMfg) WHERE $prodMfg = $mfgID, and have it continue to loop through all the results.

Does this clarify anything? I am still at a point where I can change table/db structure if I really have to.