another multiple query question

[cganim8]

I need some help figuring out how to do the following.

 

I have a mysql table that has lots of fields (about 20) that are to be used as IDs for getting data from many other tables. For example the following four tables and their fields.

 

Product_tbl:MainTableID,product,ColorID,MaterialID,sizeID

 

Color_tbl:ColorID,color,description

Material_tbl:MaterialID,material,description

Size_tbl:sizeID,size,description

 

This is the part I don't get. For example I want the data in fields 'material' and 'description' from the table Material_tbl in the record# that matches the value of Product_tbl.MaterialID.  Do I have to do a separate query for each table each time I advance to the next record in the products table?

 

<?php

$product_results = mysql_query("SELECT MainTableID, product, ColorID, MaterialID, sizeID FROM product_tbl");

$description_results = mysql_query("SELECT * FROM Color_tbl, Material_tbl, Size_tbl");

 

while($row = mysql_fetch_array($product_results))

{

 

$color = ???

$color_description =???

$material =???

$material_description = ???

$size = ??? 

$size_description = ???

 

    echo "Product: {$row['product']}  <br>" .

        "Color:    $color    $color_description    <br>" .

        "Material: $material $material_description  <br>" .

"size:    $size    $size_description      <br>";

}

 

<?php

[xtopolis]

This is the part I don't get. For example I want the data in fields 'material' and 'description' from the table Material_tbl in the record# that matches the value of Product_tbl.MaterialID.  Do I have to do a separate query for each table each time I advance to the next record in the products table?

 

You will be using JOINS if I'm reading your question correctly.

Read up on JOINS here: (by Barand): http://www.phpfreaks.com/tutorial/data-joins-unions

 

SELECT p.MaterialID, m.material, m.description FROM Product_tbl p
  JOIN Material_tbl m ON(p.MaterialID = m.MaterialID)
  WHERE p.MaterialID = 1;

 

That should help you understand how to pull the rest of the fields in other parts of your post.

 

edit: To "advance" through all the records, just leave off the WHERE clause, it should display all product ids with a matching material and description  *untested*