I'm having difficulty wrapping my teeth around this 'ere problem:
I've got three tables: products, categories, and products_xtracategories.
products contains full details on the products, with a field called category_id, which links to the categories table, which contains information on the product categories available.
The product_xtracategories table contains two fields, product_id and category_id. This is used to tell the system that the product matching product_id in the products table should also be displayed when browsing through the category matching category_id in categories.
My query for browsing the catalogue as it stands is:
$query = "SELECT * FROM `products` WHERE `category_id`={$category} ORDER BY `product_title` ASC";
This just retrieves products from a single category.
However, I also need to retrieve all products where category_id = $category, and any products where the category_id in product_xtracategories matches $category.
I presume I need to use JOIN somewhere, but I'm not too hot on JOIN syntax etc. so could do with some help.
I hope I've explained this well enough. If anyone can help, I'd appreciate it muchly 🙂
//edit: An example bit of data might be good, I suppose 😉
Product ID 1: A hardback book, to be displayed in category ID 1.
Category ID 1: Books
Category ID 2: Hardback.
Category ID 3: Softback.
Entry in product_xtracategories:
Product ID 1, Category ID 2.
This means that the hardback book should be displayed in category ID 1 (as in the main product definition, in products) and should ALSO be displayed in category ID 2.
