How to show related products using PHP/MySQL? Help I'm new!

PHP
1

I am only just learning PHP and I can grasp the concepts pretty well, but as I haven’t done any programming since I was 10 (IF THEN GOTO…) I am struggling to fill in the gaps.

I have one table that lists all of my products and a url link to their photo.

I have another table that lists all of the products and 3 related products by their ID

i.e.
TABLE1
1 Product 1 Url1
2 Product 2 Url 2
3 Product 3 Url3
etc
TABLE2
1 Product 1 1 4 5
2 Product 2 4 2 8

I want to relate the two tables so I can pull out the 3 url links to each product.

In real world terms, I’m selling a skirt, when that is selected I want to display next to it 3 pictures of products related to the skirt that someone might like.

I was thinking along the lines of

Select ID Product URL
FROM Table1, Table2
Where Table1.ID=Table2.Related1

Repeating 3 times for each related product.

That works fine, but how do I link it to the product that was originally selected? Normally it would be a WHERE statement for ID = ‘colname’ but I can’t figure out how to add it in…

I’m sure this sounds confusing but if someone could point me in the right direction or to a tutorial/php site that discusses more advanced topics that would be great!!

Thanks a bunch!
:confused:

2

I’m not sure why you’d have two tables both listing your products…unless I’m misinterpreting your post.

I think what you want is probably something like this:

ProductsTable
ID ProductName Photo
1 Skirt image.gif
2 Tshirt image2.gif
3 Sweater image2.gif

ProductRelationsTable
ID ProductID RelatedProductID
1 1 2
1 1 3

So basically this is saying that product 2 is linked to product 1, and product 3 is linked to product 1.

Your queries might look something like this:

Query 1: SELECT * FROM ProductsTable WHERE ID = ‘1’
This will return all the info on your main product.

Query 2: SELECT ProductsTable.* FROM ProductRelationsTable LEFT JOIN ProductsTable ON productsTable.ID = ProductRelationsTable.RelatedProductID WHERE ProductRelationsTable.ProductID = ‘1’
This will return all the info on the linked products.

3

Okay that makes sense. My product is a variable though.

My first query is this
SELECT *
FROM AtTheNet_data
WHERE ID = ‘colname’

Can I still use the join table to bring in related products to my variable product? I will give it a got waiting for your reply.

thanks for helping so quickly! :aparty:

4

Okay, that seemed to work great for bringing in the related product when I test it. But when I try to load my product page I am getting this error.

You have an error in your SQL syntax near ‘WHERE You_may_like.ID = ‘14013’’ at line 1

My Code is as follows
SELECT AtTheNet_data. *
FROM You_may_like LEFT JOIN AtTheNet_data ON AtTheNet_data.ID = You_may_like.Related1
WHERE You_may_like.ID = ‘related’

where my variable ‘related’ is 7510 (one of my products) $_GET[‘ID’]

I can pull up all of info in my recordset but as I said if I go to my product page now it doesn’t work.

www.atthenet.co.uk/product_detail.php

any more help??? Please.

:rolleyes: