structure of categories
Hey I need some guidance with some database logic
here is the outline of my problem: I have top level categories in the menu as well as other top level categories that include sub-categories. I got the subcategories working. however I am trying to figure out how to display products for the top level categories (I will include my code and some screenshots to help illustrate this the best I can).
I jut need to know where to change the code so that when a category has no subcategories (for example LIGHTING) how can I adjust the code to do this. (Note: in the admin product create section - I was going to hard code the top level categories in the dynamic list where it displays the subcategories - see the screenshot for admin)
php:
[b]This code is from my categories.php file it includes code for search page and the case statements for additional menu navigation (Coded by Ray from a support ticket)[/b]
<?php
if(true){
switch(isset($_GET['Department'])?$_GET['Department']:""){
case "New-Arrivals":
$rsProducts = new WA_MySQLi_RS("rsProducts",$ecartdb,0);
$rsProducts->setQuery("SELECT * FROM products LEFT JOIN categories ON products.ProductCategoryID = categories.CategoryID WHERE products.ProductNewArrival = 1");
$rsProducts->execute();
break;
case "Best-Sellers":
$rsProducts = new WA_MySQLi_RS("rsProducts",$ecartdb,0);
$rsProducts->setQuery("SELECT * FROM products LEFT JOIN categories ON products.ProductCategoryID = categories.CategoryID WHERE ProductBestSeller = 1");
$rsProducts->execute();
break;
case "InStore-Now":
$rsProducts = new WA_MySQLi_RS("rsProducts",$ecartdb,0);
$rsProducts->setQuery("SELECT * FROM products LEFT JOIN categories ON products.ProductCategoryID = categories.CategoryID WHERE ProductInStoreNow = 1");
$rsProducts->execute();
break;
case "Search":
$rsProducts = new WA_MySQLi_RS("rsProducts",$ecartdb,0);
$rsProducts->setQuery("SELECT * FROM products");
if (isset($_GET["Price"])) {
$prices = explode(":",$_GET["Price"]);
$rsProducts->addFilter("ProductPrice",">=","d",$prices[0]);
$rsProducts->addFilter("ProductPrice","<=","d",$prices[1]);
}
$productTypes = array();
if (isset($_GET["Wood"])) {
$productTypes[] = "Wood";
}
if (isset($_GET["Metal"])) {
$productTypes[] = "Metal";
}
if (isset($_GET["Glass"])) {
$productTypes[] = "Glass";
}
if (sizeof($productTypes) > 0) $rsProducts->addFilter("ProductType","=","s",$productTypes);
if (isset($_GET["Product"])) {
$productName = $_GET["Product"];
$splitName = explode(" ",$productName);
for ($x=0; $x<sizeof($splitName); $x++) {
$splitName[$x] = "%" . $splitName[$x]."%";
}
$rsProducts->addFilter("ProductKeywords","LIKE","s",$splitName);
}
$rsProducts->setFilter();
$rsProducts->execute();
break;
default:
$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
$rsProducts->execute();
break;
}
}
?>php:
[b]Here is the code to display the products [/b]
<?php
while(!$rsProducts->atEnd()) {
?>
<div class="col product-tile-columns"><a href="product.php?Product=<?php echo($rsProducts->getColumnVal("ProductName")); ?>" class="b-link-fade b-animate-go"> <img src="images/products/<?php echo($rsProducts->getColumnVal("ProductImage")); ?>" class="responsive-image" />
<div class="b-wrapper">
<h2 class="b-from-left b-animate b-delay03"><?php echo($rsProducts->getColumnVal("ProductName")); ?></h2>
<p class="b-from-right b-animate b-delay03">£<?php echo($rsProducts->getColumnVal("ProductPrice")); ?></p>
</div>
</a> </div>
<?php
$rsProducts->moveNext();
}
$rsProducts->moveFirst(); //return RS to first record
?>php:
[b]this code displays some additional category information on page (category name and top level description as I want a generic description for all the sub-categories within a category[/b]
<?php
$rsDepartment = new WA_MySQLi_RS("rsDepartment",$ecartdb,1);
$rsDepartment->setQuery("SELECT * FROM topcategory WHERE TopCategoryLink = ?");
$rsDepartment->bindParam("s", "".(isset($_GET['Department'])?$_GET['Department']:"") ."", "-1"); //colname
$rsDepartment->execute();
?>php:
[b]Here is the menu code[/b]
<?php
$rsLighting = new WA_MySQLi_RS("rsCategory",$ecartdb,0);
$rsLighting->setQuery("SELECT * FROM categories WHERE CategoryLocation='Lighting'");
$rsLighting->execute();
?>
<?php
$rsLivingDining = new WA_MySQLi_RS("rsCategory",$ecartdb,0);
$rsLivingDining->setQuery("SELECT * FROM categories WHERE CategoryLocation='Living-Dining'");
$rsLivingDining->execute();
?>
<?php
$rsSleeping = new WA_MySQLi_RS("rsCategory",$ecartdb,0);
$rsSleeping->setQuery("SELECT * FROM categories WHERE CategoryLocation='Sleeping'");
$rsSleeping->execute();
?>
<?php
$rsAccessories = new WA_MySQLi_RS("rsCategory",$ecartdb,0);
$rsAccessories->setQuery("SELECT * FROM categories WHERE CategoryLocation='Accessories'");
$rsAccessories->execute();
?>
<?php
$rsInteriorDesign = new WA_MySQLi_RS("rsCategory",$ecartdb,0);
$rsInteriorDesign->setQuery("SELECT * FROM categories WHERE CategoryLocation='Interior-Design'");
$rsInteriorDesign->execute();
?>
<nav id="nav" role="navigation"> <a href="#nav" title="Show navigation">Show navigation</a> <a href="#" title="Hide navigation">Hide navigation</a>
<ul class="clearfix">
<li><a href="../category.php?Department=Lighting&Category=<?php echo($rsLighting->getColumnVal("CategoryLink")); ?>">Lighting</a>
<ul class="dropdown">
<?php
while(!$rsLighting->atEnd()) {
?>
<li> <a href="../category.php?Department=Lighting&Category=<?php echo($rsLighting->getColumnVal("CategoryLink")); ?>"><?php echo($rsLighting->getColumnVal("CategoryName")); ?></a> </li>
<?php
$rsLighting->moveNext();
}
$rsLighting->moveFirst(); //return RS to first record
?>
</ul>
</li>
<li> <a href="../category.php?Department=Living-Dining&Category=<?php echo($rsLivingDining->getColumnVal("CategoryLink")); ?>">Living & Dining</a>
<ul class="dropdown">
<?php
while(!$rsLivingDining->atEnd()) {
?>
<li> <a href="../category.php?Department=Living-Dining&Category=<?php echo($rsLivingDining->getColumnVal("CategoryLink")); ?>"><?php echo($rsLivingDining->getColumnVal("CategoryName")); ?></a> </li>
<?php
$rsLivingDining->moveNext();
}
$rsLivingDining->moveFirst(); //return RS to first record
?>
</ul>
</li>
<li> <a href="../category.php?Department=Sleeping&Category=<?php echo($rsSleeping->getColumnVal("CategoryLink")); ?>">Sleeping</a>
<ul class="dropdown">
<?php
while(!$rsSleeping->atEnd()) {
?>
<li> <a href="../category.php?Department=Sleeping&Category=<?php echo($rsSleeping->getColumnVal("CategoryLink")); ?>"><?php echo($rsSleeping->getColumnVal("CategoryName")); ?></a> </li>
<?php
$rsSleeping->moveNext();
}
$rsSleeping->moveFirst(); //return RS to first record
?>
</ul>
</li>
<li> <a href="../category.php?Department=Accessories&Category=<?php echo($rsAccessories->getColumnVal("CategoryLink")); ?>">Accessories</a>
<ul class="dropdown">
<?php
while(!$rsAccessories->atEnd()) {
?>
<li> <a href="../category.php?Department=Accessories&Category=<?php echo($rsAccessories->getColumnVal("CategoryLink")); ?>"><?php echo($rsAccessories->getColumnVal("CategoryName")); ?></a> </li>
<?php
$rsAccessories->moveNext();
}
$rsAccessories->moveFirst(); //return RS to first record
?>
</ul>
</li>
<li> <a href="../category.php?Department=Interior-Design&Category=<?php echo($rsInteriorDesign->getColumnVal("CategoryLink")); ?>">Interior Design</a>
<ul class="dropdown">
<?php
while(!$rsInteriorDesign->atEnd()) {
?>
<li> <a href="../category.php?Department=Interior-Design&Category=<?php echo($rsInteriorDesign->getColumnVal("CategoryLink")); ?>"><?php echo($rsInteriorDesign->getColumnVal("CategoryName")); ?></a> </li>
<?php
$rsInteriorDesign->moveNext();
}
$rsInteriorDesign->moveFirst(); //return RS to first record
?>
</ul>
</li>
<li> <a href="../category.php?Department=New-Arrivals">New In</a></li>
<li> <a href="../category.php?Department=Best-Sellers">Best Sellers</a></li>
<li> <a href="../category.php?Department=InStore-Now">In Store</a></li>
</ul>
</nav>
