close ad
Install the LAtest Updates to Work with CC 2017 and CC 2018
open ad
View Menu

Technical Support Forums

Free, outstanding support from WebAssist and your colleagues

rating

structure of categories

Thread began 3/06/2016 1:54 pm by Christopher West | Last modified 3/28/2016 12:21 pm by Ray Borduin | 1026 views | 19 replies |

Christopher WestCommunity Expert

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 &amp; 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>
Sign in to reply to this post

Ray BorduinWebAssist

I'm sure the answer is a carefully placed IF and ELSE statement to check the recordset to see if it has any results and to do something different when it doesn't. However this is too complex and custom for me to really give more than general direction for. This would be better suited for a premium support ticket.

Sign in to reply to this post

Christopher WestCommunity Expert

Yes I should try and use more IF ELSE statements :) To be fair I didn't want to go for a premiere ticket just yet on this one, because I need to identify that it will do everything I need. This is why I opened up the forum post: http://www.webassist.com/forums/posts.php?id=38727

Because I am wondered if that block of code would work better for my website (as the mysql version in that forum post works fine with the previous website project). (as you once said why reinvent the wheel) :)

However what is essential is the code in this forum post where you helped me getting the custom search to work via mysqli and that part works fine. So I need to somehow combine the 2 approaches I guess.

Also I did notice one issue with the navigation code in this forum post. when the user clicks on the parent menu link of a submenu, it will still show the url for the first submenu option. for example, for the menu item: LIVING & DINING the first submenu option is OCCASIONAL CHAIRS
and it appears that they both share the same URL
http://localhost/armadillo/category.php?Department=Living-Dining&Category=Occasional-Chairs
For example the parent (which is LIVING & DINING) in theory should be http://localhost/armadillo/category.php?Department=Living-Dining&Category=


should I separate parent and child menu items in separate tables?

Chris

Sign in to reply to this post

Ray BorduinWebAssist

It might be easier to follow if you break them into separate recordsets if it isn't working properly now. The code is just too custom and complex to try to handle through a forum post. I'll have to work with the code directly to debug and get it working the way you want, which would require a premium ticket.

Sign in to reply to this post

Christopher WestCommunity Expert

Yeah I may actually do a support ticket for this one, if possible this week. I will do a few attempts though, Probably best to keep 2 tables for parent and child categories in the menu as that may be easier to adapt for client. Would you be available for a ticket Friday?

Sign in to reply to this post

Ray BorduinWebAssist

Please contact me via skype to arrange a time.

Sign in to reply to this post

Christopher WestCommunity Expert

Hi sorry for late reply, been busy with other work. I want to plan for this support ticket in advance so its clear what is required. and wondered if we can discuss on here first so that I can make preparations.

So wanted to ask a few questions:

1. Currently the database has 2 tables (first is a top level category and secondly a sub-category) - Note: the top level category - Would this be a good way to create categories and sub-categories?

2. The top level category also includes; New In, Best Sellers and In-Store (this is actually a filter from the product table where the client can use a checkbox when creating/updating products )I did it this way so that the client can add a category image and page description for these headings) - Does this sound logical practice?

3. The top level category also includes a record containing a details for my product search listing page (this way I can include a category image and description for the search results page (the search results page is also the main category page) - Does this sound logical?

4. The product table (at the moment) only contains a field to store the record id from only the sub-category table (I am thinking it may make sense to add another field in the product table to also store the top level category record id) - is this a logical approach

5. In the client admin area when creating products I was thinking the client first chooses a top level category - and then if this category includes sub categories then using javascript an additional select list appears on the page where the admin can chose a sub-category - is this a good approach?

6. On the website front end - when customers hover over the menu - the top level link will send the customer to a category page where it displays ALL products in that top level category (if sub categories have been assigned to a specific top level category then a drop down displays those sub categories and the customer can chose a sub category which then displays those products for the sub category.

7. So from point 6 - I guess I would use an IF statement to control this condition?

So from above does these points make sense in creating main categories and optional sub categories? Is there a different approach that would be more logical (and if so could you give me details so that I can adjust the database before I do a premiere ticket?

Here is A Quick run down on my menu:
Lighting - (current there is no sub-categories but admin has the option to add some in the admin area)
Living & Dining - (this current has sub categories)
Sleeping - (this current has sub categories)
Accessories - (this current has sub categories)
Interior Design - (current there is no sub-categories but admin has the option to add some in the admin area)
New In - This is filtering via a value in the product table via a checkbox in the admin section)
Best Sellers - This is filtering via a value in the product table via a checkbox in the admin section)
In-Store - This is filtering via a value in the product table via a checkbox in the admin section)

if it helps here are the URL parameters I am passing (just examples)
category.php?Department=Lighting&Category=
category.php?Department=Living-Dining&Category=Occasional-Chairs
category.php?Department=Living-Dining&Category=Sofas-and-Chairs
category.php?Department=New-Arrivals
category.php?Department=Best-Sellers
category.php?Department=Search&Product=chair

From above the URL parameter "Department" = top category and the URL paremeter "Category" = a sub category and then there is Department=Search which indicate my search page.


Chris

Sign in to reply to this post

Ray BorduinWebAssist

1) As long as you only have two levels of categories, I think that is fine
2) I think that sounds like a good solution
3) This makes sense
4) I wouldn't add both fields to the products table if you can look up the main category from the subcategory table... seems redundant. I'd probably just add a "none" subcategory to each main category to make sure everything has a subcategory in the database.
5) Yes that would work
6) ok
7) yes

Sign in to reply to this post

Christopher WestCommunity Expert

Hi Ray, I think you just given me an idea for point 4. currently my URL for some categories could be:
category.php?Department=Lighting&Category=
So if the database record has a blank value or 'none' then I could do a IF statement to check for either NULL or 'None'

I need to make sure I account for as much as I can before paying out a support ticket as what I find with clients in the past, I get help with something, pay for it and then client turns around and wants some other feature which the code doesn't allow.

I guess as long as I can have either main top level category listings and then optional sub category listings that should be fine. Hmm. Im just thinking is client wants a 2 or 3 level sub category. I wouldn't of thought so. But need to make sure :)

Sign in to reply to this post

Ray BorduinWebAssist

Yes, we can account for blank subcategories and go to none. I'd confirm how many levels of subcategories... You may just want to use a single categories table with a ParentCategoryID field to nest any number of sublevels and the main categories in a single table.

You should also confirm that a single product can only be in one category at a time.

Sign in to reply to this post
loading

Build websites with a little help from your friends

Your friends over here at WebAssist! These Dreamweaver extensions will assist you in building unlimited, custom websites.

Build websites from already-built web applications

These out-of-the-box solutions provide you proven, tested applications that can be up and running now.  Build a store, a gallery, or a web-based email solution.

Want your website pre-built and hosted?

Close Windowclose

Rate your experience or provide feedback on this page

Account or customer service questions?
Please user our contact form.

Need technical support?
Please visit support to ask a question

Content

rating

Layout

rating

Ease of use

rating

security code refresh image

We do not respond to comments submitted from this page directly, but we do read and analyze any feedback and will use it to help make your experience better in the future.

Close Windowclose

We were unable to retrieve the attached file

Close Windowclose

Attach and remove files

add attachmentAdd attachment
Close Windowclose

Enter the URL you would like to link to in your post

Close Windowclose

This is how you use right click RTF editing

Enable right click RTF editing option allows you to add html markup into your tutorial such as images, bulleted lists, files and more...

-- click to close --

Uploading file...