Hi. ok I have created the following tables
1. Category (This includes the fields; CategoryID, CategoryParentID, CategoryName, CategoryLink, CategoryDescription, CategoryImage and CategoryDateEdit)
(note: CategoryLink field is story the same value as CategoryName except it replaces any spaces with hypens (I link to use this field in the URL)
2. CategoryProducts (this includes the fields; CategoryProductsID, ProductID and CategoryID)
For data reference take a look at the screenshots of both tables
Because I like SEO friendly URLS the general structure of the URL to the category.php would be (for example)
So Department= would consist on the Parent link and Category= would be the sub-category of the parent.
If there are no sub-categories then the URL would be category.php?Department=Lighting&Category=
On my navigation under LIGHTING (for example if the customer clicks on the Lighting link then it will display ALL products that have category parent of 'lighting' OR if the customer hovers over the LIGHTING link then the drop down will display all the sub-categories that are associated with the Parent of 'Lighting'
Does this make sense so far?
I also need to figure out the SQL to display the products on the category page/
My OLD SQL (before this new approach is)
$rsProducts = new WA_MySQLi_RS("rsProducts",$ecartdb,0);
$rsProducts->setQuery("SELECT products.*, categories.* FROM products LEFT JOIN categories ON products.ProductCategoryID = categories.CategoryID WHERE (categories.CategoryLink = ? OR -1 = ?) AND categories.CategoryLocation = ?");
$rsProducts->bindParam("s", "".(isset($_GET['Category'])?$_GET['Category']:"") ."", "-1"); //WAQB_Param1
$rsProducts->bindParam("s", "".(isset($_GET['Category'])?$_GET['Category']:"") ."", "-1"); //WAQB_Param2
$rsProducts->bindParam("s", "".(isset($_GET['Department'])?$_GET['Department']:"") ."", "-1"); //WAQB_Param3
Culd you point me in the right direction for the SQL my my new approach please?