|
/****************************** |
|
* First create a products table: |
|
*******************************/ |
|
CREATE TABLE IF NOT EXISTS `products` ( |
|
`product_id` int(10) NOT NULL auto_increment, |
|
`product_title` varchar(255) collate utf8_unicode_ci NOT NULL, |
|
`product_description` text collate utf8_unicode_ci, |
|
`product_price` decimal(12,5) NOT NULL, |
|
`product_sku` varchar(255) collate utf8_unicode_ci NOT NULL, |
|
`product_image` varchar(255) collate utf8_unicode_ci NOT NULL, |
|
PRIMARY KEY (`product_id`) |
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1; |
|
|
|
/************************************* |
|
* And add some data to products table: |
|
**************************************/ |
|
INSERT INTO `products` (`product_id`, `product_title`, `product_description`, `product_price`, `product_sku`, `product_image`,) |
|
VALUES (1, 'Product 1', 'Product 1 Description', 10.00, '123product123', 'image1.jpg'), |
|
(2, 'Product 2', 'Product 2 Description', 12.00, '321product321', 'image2.jpg'); |
|
|
|
/**************************** |
|
* Then create the tags table: |
|
*****************************/ |
|
CREATE TABLE IF NOT EXISTS `tags` ( |
|
`tag_id` int(10) NOT NULL auto_increment, |
|
`tag_name` varchar(255) collate utf8_unicode_ci NOT NULL, |
|
PRIMARY KEY (`tag_id`) |
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1; |
|
|
|
/********************************* |
|
* And add some data to tags table: |
|
**********************************/ |
|
INSERT INTO `tags` (`tag_id`, `tag_name`) VALUES |
|
(1, 'Tag1'), |
|
(2, 'Tag2'), |
|
(3, 'Tag3'), |
|
(4, 'Tag4'); |
|
|
|
/********************************************************************** |
|
* Now we need a table to hold connection between the products and tags: |
|
***********************************************************************/ |
|
CREATE TABLE IF NOT EXISTS `productsXtags` ( |
|
`productID` int(10) NOT NULL, |
|
`tagID` int(10) NOT NULL, |
|
PRIMARY KEY (`productID`,`tagID`) |
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; |
|
|
|
/************************************************* |
|
* And we add some data to the productsXtags table: |
|
**************************************************/ |
|
INSERT INTO `productsXtags` (`productID`, `tagID`) VALUES |
|
(1, 1), |
|
(1, 2), |
|
(1, 3), |
|
(2, 2), |
|
(2, 3), |
|
(2, 4); |
|
|
|
/*************************** |
|
* The Related Products Query |
|
****************************/ |
|
-- PRODUCT ID IS ID OF THE PRODUCT YOU ARE SHOWING RELATED FOR |
|
SELECT |
|
p.*,COUNT(*) AS matchedTags |
|
FROM products p |
|
INNER JOIN productsXtags pt ON pt.productID = p.product_id |
|
WHERE pt.tagID IN(SELECT tagID |
|
FROM productsXtags |
|
WHERE productID = PRODUCT_ID) |
|
AND p.product_id != PRODUCT_ID |
|
GROUP BY p.product_id |
|
HAVING COUNT( * ) > 0 |
|
LIMIT 0,3 |
|
|
|
/******************************************************************** |
|
* http://leonpanjtar.eu/web-design-blog/mysql-related-products-query/ |
|
*********************************************************************/ |
