4 Replies Latest reply on Sep 20, 2016 1:26 PM by Nancy OShea

    Display related products question.

    Nancy OShea Adobe Community Professional & MVP

      Hi,

      I need a "People who viewed this also viewed these?" type of thing.  You've probably seen them on shopping carts.  They are pretty common but I'm not sure about the logic.

       

      I'm working with PHP & MySQL on a dynamically populated page.  The page could contain any product from the database.

      So if the product being viewed now is from Category A, I want to display 4 random thumbnail images from Category A..  Likewise, if the current product is from Category B, I want thumbnails from Category B and so on...

      I've got A LOT of Categories.  So what's the best way of doing this?

       

       

      Nancy O.

        • 1. Re: Display related products question.
          osgood_ Level 8

          You would need a 'product_category' column in your database.

           

          When you get the initial product details also get the 'product_category' and assign it to a variable:

           

          $getProductDetails = $conn->query("SELECT * FROM products WHERE product_id='$product_id'") or die($conn->error);

          $getProductDetails = $getProductDetails->fetch_assoc();

          $product_category = $getProductDetails['product_category'];

           

           

          Then check the $product_category variable against the database 'product_category' column:

           

          <?php if($getProductDetails['product_category'] == $product_category) { ?>

           

          <?php

          // Get 4 random products to show by category from products table

          $selectRandomProducts = $conn->query("SELECT * FROM products WHERE product_category = '$product_category' ORDER BY RAND() LIMIT 4") or die($conn->error);

          ?>

           

           

          <?php } ?>

           

           

          Then echo out the results of $selectRandomProducts varible using a while loop:

           

          <?php while($row = $selectRandomProducts->fetch_assoc()) { ?>

           

          <?php echo $row['product_name']; ?>

          <?php echo $row['product_price']; ?>

           

          <?php } ?>

           

           

          That should get you to where you need to be.

          1 person found this helpful
          • 2. Re: Display related products question.
            Rob Hecker2 Adobe Community Professional & MVP

            I've never created something like this, but here is how I think I would approach it:

             

            Create a table with two columns, product_id and session_id. Give it an index of UNIQUE based on both columns. Activate sessions on the website and every time the user goes to a product page, use INSERT IGNORE to update the table with the product_id/session_id pair.

             

            Then, the query to return the four top products also viewed by other people who viewed the current product would be as follows (Not tested, so I can't promise it would work as is).

             

            SELECT B.product_id FROM (SELECT session_id FROM tablename WHERE product_id = '$product_id')A LEFT JOIN (SELECT product_id,  COUNT(product_id)AS similar_product FROM tablename WHERE A.session_id = session_id AND product_id !='$product_id' GROUP BY product_id ORDER BY similar_product LIMIT 4 )B

             

            . . . this would not return random products, but would return the most relevant products, as in the case of "people who viewed this also viewed. . ."

            1 person found this helpful
            • 3. Re: Display related products question.
              osgood_ Level 8

              If the question is related to the top 4 products in a category which have been viewed and not just 4 random products selected from the same category then things would get more difficult.

               

              I would create a column in the products table and add to it each time the item is viewed and call out the top 4 based on the number of views. You would need to use a session to stop any potential cheating or at least try to make it harder.

              • 4. Re: Display related products question.
                Nancy OShea Adobe Community Professional & MVP

                I'm not using sessions for this.  Just 4 random products from the same category.  Perhaps later I'll add star ratings or something to filter results by popularity.  For now, I just need 4 random thumbnail images.  I'm so close.  I just need to sort out the presentation...

                 

                <?php $same = ($row_get_products['CATEGORY']);

                $qry = mysql_query("SELECT * FROM PRODUCTS WHERE CATEGORY = '$same' ORDER BY RAND() LIMIT 4");

                 

                while($rand = mysql_fetch_assoc($qry)){ 

                    echo $rand['PRODTHUMB'] . "

                "; echo $rand['PRODNAME'] ."

                ";

                }

                ?>

                 

                Thanks to both of you for all your help

                I really appreciate it.

                 

                Nancy O.