MySQL Table Help

[mrzebra81]

Hello,

 

So I need a bit of help with tables. I currently have my website set up with the following tables:

 

products (productId, productName, link, productPrice, productDesc)

 

productscents (scentId, scentName, scentDesc)

 

salves (productId, productName, productDesc)

 

 

So now most products in the products table can be made in any of the scents in the productScents table (except for the salves and reed diffuser jar).  I have a separate table of the salve types that can be ordered. How do I join each product with each productScent.

 

For example:

 

If I have the following products with their Id number:

1 Body Mist

2 Massage Oil

3 Reed Diffuser Jar

 

 

and the following scents with their id (id has leading zeros):

0001 Mango

0002 Passionfruit

0003 Grapefruit

 

How would I create a master products table that will display the following:

3        Reed Diffuser Jar

10001 Mango Body Mist

10002 Passionfruit Body Mist

10003 Grapefruit Body Mist

20001 Mango Massage Oil

20002 Passionfruit Massage Oil

20003 Grapefruit Massage Oil

 

 

I hope this makes sense. lol.

 

Any help would be greatly appreciated.

[Drummin]

Instead of running a query to list scents for each product, I query one time to get the scents and put the scentId and scentName into an array.  Then while listing your products, if the product name is not the "Reed Diffuser Jar" and not "salves" we run a foreach statement to pull out the scentId and scentName and display these with the product information.  I made the product names links assuming you are directing to the product details page.  The scentId is added as a GET value to be picked up on the product details page.

<?PHP
//Get scents and create array with id as the key and name as the value
$scents=array();
$getscents = mysql_query("SELECT scentId,scentName FROM productscents ORDER BY scentId ASC");
  WHILE($gtscents = mysql_fetch_array($getscents)){
$scentId = ($gtscents['scentId']);
$scentName = ($gtscents['scentName']);
$scents[$scentId]=$scentName;
}
//print_r($scents);
//List products and create table
echo "<table>";
$getproducts = mysql_query("SELECT productId,productName,link FROM products ORDER BY productId ASC");
  WHILE($gtproducts = mysql_fetch_array($getproducts)){
$productId = ($gtproducts['productId']);
$productName = ($gtproducts['productName']);
$link = ($gtproducts['link']);
//Below is assuming link includes extension. we add scent id as GET value to be picked up on page.
IF ($productName!="Reed Diffuser Jar" && $productName!="salves"){
	foreach($scents as $key => $value){
echo "<tr><td>$productId$key</td><td><a href=\"$link?scentid=$key\">$value $productName</a></td></tr>";
	}// end foreach
} // end IF ($productName!="Reed Diffuser Jar" && $productName!="salves")
ELSE{
echo "<tr><td>$productId</td><td><a href=\"$link\">$productName</a></td></tr>";
} // end IF ELSE ($productName!="Reed Diffuser Jar" && $productName!="salves")
}// end WHILE($gtproducts = mysql_fetch_array($getproducts)) 
echo "</table>"; 
?>