I'm having a bit of difficulty with mysql/php. I have 5 product category's, & each category has x amount of products. Showing the products from each category is simple, I've created 2 tbls; 1 for products, the other for category's as below, with the CategoryID making the relationship

Product tbl

ProductID (Primary ID)
CategoryID (Foriegn Key)
ProductName
ProductPrice

Category tbl

CategoryID (Primary ID)
CategoryName
CategoryDes

So on my navigation I can give each btn a relevant CategoryID, which when pressed would take me to the product page, showing me the products in that category, there's a RS on the products page works this out as below;

SELECT *
FROM products
WHERE products.CategoryID = idName

Variable: idName, 1, $HTTP_GET_VARS['CategoryID']

My problem is that several of the category's have sub - category's, again to deal with this I've created a new table & amended the others to deal with this (see below) , & I would like to use the sub - category names as links.

ProductID (Primary ID)
CategoryID (Foriegn Key)
SubCategoryID (Foriegn Key)
ProductName
ProductPrice

Category tbl

CategoryID (Primary ID)
CategoryName
CategoryDes

SubCategory tbl

SubCategoryID (Primary ID)
CategoryID (Foriegn Key)
SubCategoryName
SubCategoryDes

If I now pressed a navigation btn it would still work, the problem is I can't apply a CategoryID to the sub - category's & using the sub - category's ID wouldn't work as the variable in the record set is set up to deal with CategoryID's.

There are ways that I could work round the problem, e.g. by adding the sub - category's to the category tbl, but in the long run this would cause problems & would make conflicting data. I could make a separate page for the sub - category's, but again this would be making more work & would cause confusion when using a shopping cart.<font face='Arial'></font id='Arial'><font size=2></font id=size2><font size=3></font id=size3>

Have you tried joining the tables? Lookup "inner join"

Which tbls should I look at joining?

Cheers

Darren