Related Products - HOW DO I?

[discostudio]

I have a table full of products, called incidently products.

I would like to add a section underneath each product related products.

What is the mest method of linking products, or "relating products".

Should I use a seperate table??? I have seen this feature in osCommerce.

[rob76]

You should have more than one table to do this. It gets a little complex because some of your related products might themselves be related to more than one product.

I'm no expert but I believe in database terminology this is called a "many to many" relationship. Have a look at my post (one above yours) - It's asking a similar question

[discostudio]

I was thinking using a table called relate_products, as follows:

id | product_id | related_prods

1 | 10012 | 10022
2 | 10012 | 10023
3 | 10045 | 10065

Then on when I'm on the product page I can do the followning:

SELECT * FROM `related_products` WHERE `product_id` = '$thisproductid';

while { 
// SELECT infomation for current product from `products` table.
// Display Product Infomation
}

Then that would give me a list of related products I presume, but my question is is this the best way?

I have done things in the past and ended up creating a great deal of work by doing things the wrong way round.

[discostudio]

Actually I could do the following to make it a little less server intensive:

<?php 

$sql=mysql_query("SELECT * FROM `related_products` AS 
`r` LEFT OUTER JOIN `products` AS `p` ON `r.product_id` = 
p.product_id WHERE `r.product_id` = '$thisproductid'; ")

while ($related = mysql_fetch_array($sql)) { ?>

<a href="product_info.php?product_id=<?php echo intval($related['product_id']); ?>"><?php echo stripslashes($related['product_id']); ?></a> <br>

<?php  } ?>

[rob76]

I've been researching this today for my own project and believe I've found the answer

The correct way to create a "many to many" relationship is to use 3 tables. Two tables are for the data you want to relate eg.

1) Product Table
2) Related Products Table

You then create a third table (called a "junction table") which has two columns containing the primary keys of the other two tables. This defines the relationship between "Products" and "Related Products" If you are using small integers as primary keys, then it's very efficient.