I'm building a shopping website. I have my database design set up as below:
tblProducts tblSizes tblColors tblImages tblSpecific_Products
id id id id id
product_name size color image product_id (FK)
style_code size_id (FK)
price color_id (FK)
image_id (FK)
Can someone please tell me how do I display all available colors on the general products page?
If I am not wrong, you are asking to query all available colors of a specific product. In that case, if you go through SQL tutor you will get by yourself.
Here is how it can be done,
SELECT color FROM tblColors WHERE id IN (SELECT color_id FROM tblSpecificProducts WHERE product_id=<YOUR-PRODUCT-ID>)
If you want it on general Product Display Page, where for each product display all available colors, then do iterate over all product Id's.