Displaying Categories & sub categories
I need to display a menu showing categories and associated sub categories, here is the scenario:
Here is the Database Schema
categories (Table)
CategoryID
Category Name
sub_categories (Table)
SubCategoryID
SubCategoryName
SubCategoryCategoryID
Here are the Recordsets:
<?php
$rsCategories = new WA_MySQLi_RS("rsCategories",$DatabaseConn,0);
$rsCategories >setQuery("SELECT * FROM categories ORDER BY CategoryName ASC");
$rsCategories >execute();
?>
<?php
$rsSubCategories = new WA_MySQLi_RS("rsSubCategories",$DatabaseConn,0);
$rsSubCategories >setQuery("SELECT * FROM sub_categories ORDER BY SubCategoryName ASC");
$rsSubCategories >execute();
?>
And here is the menu structure:
<label>Filter by Category / Sub Category</label>
<?php
while(!$rsCategories->atEnd()) {
?>
<input name="category" type="checkbox" value="<?php echo($rsCategories->getColumnVal("CategoryID")); ?>" />
<label for="category"> <?php echo($rsCategories->getColumnVal("CategoryName")); ?></label>
<br>
<!--show if -->
<?php if (($rsSubCategories->getColumnVal("SubcategoryCategoryID") == ($rsCategories->getColumnVal("CategoryID")))) { // Show if sub category exists ?>
<?php
while(!$rsSubCategories->atEnd()) {
?> <div id="subcategory"> <input name="subcategory" type="checkbox" value="<?php echo($rsSubCategories->getColumnVal("SubCategoryID")); ?>" />
<label for="subcategory"> <?php echo($rsSubcategories->getColumnVal("SubCategoryName")); ?></label></div>
<?php
$rsSubCategories->moveNext();
}
$rsSubcategories->moveFirst(); //return RS to first record
?>
<?php } // Show if sub category exists ?>
<!--end show if -->
<?php
$rsCategories->moveNext();
}
$rsCategories->moveFirst(); //return RS to first record
?>
The following displays ALL the sub Categories immediately below the first Category. How can I split them up so the sub categories are associated with the Categories?