locked
How can I filter data by sub category RRS feed

  • Question

  • User1122679459 posted

    I'm building an app with webmatrix/webpages with products and categories. That part is setup find and working but what I'm having trouble with is setting up sub categories under each category so user can filter once they drill down a category.

    For example, If I have a Computers category, once the user clicks I'd show Brand, OS, Hard drive...etc.

    The challenge I'm having is how would I design it out?
    How are these relationships made?
    How would I maintain the url's?

    Saturday, November 22, 2014 2:20 PM

Answers

  • User895691971 posted

    You would need a new Table for the Sub-categories here brother. Where you would store the Sub-categories for each of the Category. Time for you to add an ID for your Categories; if you have not set any by now.

    CategoryID | SubCategoryID | SubCategoryTitle

    Above is the structure for your SubCats table. Once done, you can easily add the Sub-categories into that Database, and have the ID of the category added to it in that row of data. This way you can get the data for each of the Category and all of the records for its sub-categories. SQL query would be like this, 

    SELECT * FROM SubCats WHERE CategoryID = 1

    This would yield all of those results from Database that would have the CategoryID of 1, suppose you're having Computer as one, it would return all of the data for the Computer. 

    URL can be written as this one, 

    http://www.example.com/page/category/sub-category

    You can then use UrlData to access the values to get the data for. You can replace them all with your IDs too if you want to. :-) 

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Saturday, November 22, 2014 3:07 PM

All replies

  • User895691971 posted

    You would need a new Table for the Sub-categories here brother. Where you would store the Sub-categories for each of the Category. Time for you to add an ID for your Categories; if you have not set any by now.

    CategoryID | SubCategoryID | SubCategoryTitle

    Above is the structure for your SubCats table. Once done, you can easily add the Sub-categories into that Database, and have the ID of the category added to it in that row of data. This way you can get the data for each of the Category and all of the records for its sub-categories. SQL query would be like this, 

    SELECT * FROM SubCats WHERE CategoryID = 1

    This would yield all of those results from Database that would have the CategoryID of 1, suppose you're having Computer as one, it would return all of the data for the Computer. 

    URL can be written as this one, 

    http://www.example.com/page/category/sub-category

    You can then use UrlData to access the values to get the data for. You can replace them all with your IDs too if you want to. :-) 

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Saturday, November 22, 2014 3:07 PM
  • User1122679459 posted

    Thanks for your response.
    I'm not clear on how I would use the category or sub category name in the url and not the id's.

    Like this:
    http://www.example.com/page/computer/brand/hp

    Not Like this:
    http://www.example.com/page/2/6

    This is the code I'm working with to build the list of categories:

    @{
        var db = Database.Open("data");
        var categories = db.Query("SELECT * FROM Categories");
    }
    
    
    HTML
    
    @foreach (var c in categories)
    {
        <li><a href="@c.Id">@c.Name</a></li>
    }
    

    Saturday, November 22, 2014 6:19 PM
  • User-2051275383 posted

    You could add a loop within a loop - Not sure if this is the best way but should work. I have made some assumptions with your table column names, you will need to adjust accordingly.


    @{ var db = Database.Open("data"); var categories = db.Query("SELECT * FROM Categories"); } HTML @foreach (var row in categories) { var subcats = db.Query("SELECT * FROM SubCategories WHERE ParenCategory=@0"); <ul><a href="@row.Id">@row.Name</a></ul> foreach (var row1 in subcats) <li><a href="@row1.Id">@row1.Name</a></li> }

    Sunday, November 23, 2014 1:02 AM
  • User895691971 posted

    Just write the hyperlink's href source as you want to get the URL in real. For example, try this code

    <a href="~/page/@c.Name/@c.SubCategory">@c.Name</a>
    
    <!-- Might be like this <a href="/page/Computer/hp">Computer</a> -->

    You can use your database table to populate the hyperlinks too. Just pass the data that you want to be shown inside the string. ASP.NET will take care of the rest of the job for you and will create the string for the href in hyperlink. 

    Thursday, November 27, 2014 5:05 AM