PHP 5 E commerce Development

Related Product Recommendations

The simplest way to inform customers of related products from within the product view is to maintain a relationship of related products within the database and within the products model, so we could cache the result of a subset of these related products. This way, the controller needs to only detect that there are more than zero

related products, insert the relevant template bit into the view, and then associate the cached query as the template tag variable to ensure that they are displayed.

There are a few ways in which we can maintain this relationship with related products:

• Within the products table, we maintain a serialized array of related product IDs

• We group related products together by themes

• We relate pairs of related products together

A serialized array isn't the most effective way to store related product data. Relating them by themes would prove problematic with multiple themes, and also when it

comes to the administrator relating products to each other, as they would have to select or create a new theme. Relating pairs of products together would require a

little trick with the query to get the product name, as the ID of the product being viewed could be one of two fields, as illustrated by the following table structure:

• ID (Integer, Primary Key, Auto Increment)

• ProductA (Integer) 

• ProductB (Integer)

The difference between using product A or product B to store a particular product reference would be determined by the administration panel we develop. So if we

were to view and edit a product in the administration panel, and we chose to set a related product, the product we were currently viewing would be product A and the

related one, product B.

The SQL for this table structure is as follows:

CREATE TABLE `product_relevant_products` (

  ` ID` int(11) NOT NULL auto_increment,

  ` productA` int(11) NOT NULL,

  ` productB` int(11) NOT NULL,

  PRI MARY KEY  (`ID` ) ,

  KEY `productB` ( `productB` ) ,

  KEY `productA` ( `productA` )

) ENGINE=I nnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;

ALTER TABLE ` product_relevant_products`

  ADD CONSTRAINT ` product_relevant_products_ibfk_2` 

  FOREIGN KEY ( `productB` ) REFERENCES `content` (`ID` ) 

  ON DELETE CASCADE ON UPDATE CASCADE,

  ADD CONSTRAINT ` product_relevant_products_ibfk_1`

  FOREIGN KEY ( `productA` ) REFERENCES `content` (`ID` ) 

  ON DELETE CASCADE ON UPDATE CASCADE;

We can get around the issue of the fact that the current product ID could be found in both the product A and product B columns in the database with an IF statement 

within our query. The IF statement would work by checking to see if product A is the product the customer is viewing; if it is, then the name of product B is returned;

otherwise, the name of product B is returned. This gives us a query such as the following, where CURRENT_PRODUCT_ID is the ID of the product the customer is

currently viewing.

SELECT IF( rp. productA<>CURRENT_PRODUCT_ID, v. name, vn. name) 

           AS product_name,

       IF( rp. productA<>CURRENT_PRODUCT_ID, c. path, cn. path) 

           AS product_path, rp. productA, rp. productB,

          c. path AS cpath, cn. path AS cnpath, c. ID AS cid, 

          cn. ID AS cnid 

FROM content c, content cn, product_relevant_products rp,

     content_versions v, content_versions vn

WHERE (rp. productA= CURRENT_PRODUCT_ID 

       OR rp. productB= CURRENT_PRODUCT_ID) 

      AND c. ID=rp. productA AND cn. ID=rp. productB

      AND v. ID=c. current_revision AND vn. ID=cn. current_revision

As we may have a lot of related products, we may wish to put a limit on the number

of related products displayed, and randomize the results.

SELECT IF( rp. productA<>CURRENT_PRODUCT_ID, v. name, vn. name)

           AS product_name,

       IF( rp. productA<>CURRENT_PRODUCT_ID, c. path, cn. path)

           AS product_path, rp. productA, rp. productB,

          c. path AS cpath, cn. path AS cnpath, c. ID AS cid,

          cn. ID AS cnid 

FROM content c, content cn, product_relevant_products rp,

     content_versions v, content_versions vn 

WHERE (rp. productA= CURRENT_PRODUCT_ID 

      OR rp. productB= CURRENT_PRODUCT_ID) AND c. ID=rp. productA

      AND cn. ID=rp. productB AND v. ID=c. current_revision 

      AND vn. ID=cn. current_revision

ORDER BY RAND() LIMI T 5

 function relatedProducts( $currentProduct )

{

  $relatedProductsSQL = " SELECT " .

    " IF(rp. productA<>{ $currentProduct} , v. name, vn. name) 

          AS product_name,

      IF(rp. productA<>{ $currentProduct} , c. path, cn. path) 

          AS product_path, rp. productA, rp. productB, c. path as cpath,

         cn. path AS cnpath, c. ID AS cid, cn. ID AS cnid " .

                        " FROM ".

    " content c, content cn, product_relevant_products rp,

      content_versions v, content_versions vn" .

                        " WHERE " .

    " (rp. productA={ $currentProduct} 

         OR rp. productB={ $currentProduct} ) ".

    " AND c. ID=rp. productA " .

    " AND cn. ID=rp. productB ".

    " AND v. ID=c. current_revision ".

    " AND vn. ID=cn. current_revision ".

                        " ORDER BY RAND() " .

    " LIMI T 5";

  $relatedProductsCache = $this->registry->getObj ect( ' db' ) ->

    cacheQuery( $relatedProductsSQL ) ;

  $this- >registry- >getObj ect(' template' ) ->getPage() ->

    addTag(' relatedproducts' , array( ' SQL' , $relatedProductsCache ) ) ;

}