OK, thanks for the replies chaps, I’ve tested both queries, but still not getting the correct results:
With the select query you posted, if I select either 1 or 2, then I get some results, but if I select 3 or 4, because they are not parent_IDs, then I get no results.
CREATE TABLE `tbl_product` (
`prod_id` int(10) unsigned NOT NULL auto_increment,
`prodtitle` varchar(255) default NULL,
PRIMARY KEY (`prod_id`)
);
INSERT INTO `tbl_product` VALUES (1,'Blue Jeans'),(2,'White Top'),(3,'Green Shirt'),(4,'Black Hat'),(5,'Pink Dress');
CREATE TABLE `tbl_prod_links` (
`id` int(10) unsigned NOT NULL auto_increment,
`parent_id` int(10) unsigned default NULL,
`child_id` int(10) unsigned default NULL,
PRIMARY KEY (`id`),
KEY `parent_id` (`parent_id`),
KEY `child_id` (`child_id`)
);
INSERT INTO `tbl_prod_links` VALUES (1,1,2),(2,1,3),(3,2,4);
This would mean that if I have products page where I’ll looking at the Blue Jeans (1), the products linked to the jeans would be:
2: White Top - as it is linked prod_link[id=1]
3: Green Shirt - as it is linked prod_link[id=2]
4: Black Hat - as it is linked prod_link[id=3], which is linked to prod_link[2]
If I select item 1, 2, 3 OR 4, then 3 linked items would show (so in effect I’ll be searching for parent_IDs and child_IDs of each result (recursion?)), and if I select 5, then nothing shows, as it is not linked to anything else?