SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Enthusiast
    Join Date
    Apr 2005
    Posts
    74
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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!

  2. #2
    SitePoint Addict
    Join Date
    Jan 2003
    Location
    Toronto
    Posts
    234
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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. #3
    SitePoint Enthusiast
    Join Date
    Apr 2005
    Posts
    74
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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!

  4. #4
    SitePoint Enthusiast
    Join Date
    Apr 2005
    Posts
    74
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.



Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •