Register

If this is your first visit, please click the Sign Up now button to begin the process of creating your account so you can begin posting on our forums! The Sign Up process will only take up about a minute of two of your time.

Results 1 to 5 of 5
  1. #1
    Senior Member
    Join Date
    Aug 2006
    Posts
    131
    Member #
    13656
    Hey guys!! Here's a weird question...

    I am designing a site that has categories and subcategories for products. Being that the site is new, there needs to be some function that only displays the categories (or subcategories) if there are actually products that belong to them...

    I know how to accomplish this but what I am trying to avoid is querying the DB too often. Basically it would have to query the entire product catalog for every category and then for every sub category. So all in all. If I had 6 categories and 10 subcategories in each main category, that's 60 queries.

    How can I avoid a crazy query like this? Any advice is appreciated....


    Thanks in advance,
    Christopher Carvache
    Web Developer / SEO
    Northeast Web Design
    +1.860.906.7802

  2.  

  3. #2
    Senior Member
    Join Date
    Jun 2005
    Location
    Atlanta, GA
    Posts
    4,146
    Member #
    10263
    Liked
    1 times
    Does it nest indefinitely? That is to say, can you have a subcategory with subcategories? If not, then you can do it all in a single query using a join to the same table. If so, then it gets more complicated.

  4. #3
    Senior Member
    Join Date
    Aug 2006
    Posts
    131
    Member #
    13656
    Quote Originally Posted by Shadowfiend
    Does it nest indefinitely? That is to say, can you have a subcategory with subcategories? If not, then you can do it all in a single query using a join to the same table. If so, then it gets more complicated.
    Nope... Just categories and subcategories...

    How would the join help me in this situation?

    And what would an example query look like?
    Christopher Carvache
    Web Developer / SEO
    Northeast Web Design
    +1.860.906.7802

  5. #4
    Senior Member
    Join Date
    Jun 2005
    Location
    Atlanta, GA
    Posts
    4,146
    Member #
    10263
    Liked
    1 times
    Tell me really quick how your database is structured -- i.e., how you represent products and categories and their relation. We can go from there.

  6. #5
    WDF Staff smoseley's Avatar
    Join Date
    Mar 2003
    Location
    Boston, MA
    Posts
    9,729
    Member #
    819
    Liked
    205 times
    Categories should be in a recursive table (references itself)

    Main categories have NULL parent_id, subcategories reference the parent category.

    Products then reference the category they belong to.
    Code:
    CREATE TABLE category (
        id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
        parent_id INT NOT NULL REFERENCES category(id),
        name VARCHAR(255) NOT NULL,
        description TEXT NULL
    );
    
    INSERT INTO category (id, parent_id, name)
    VALUES
        (1, NULL 'main category 1'),
        (2, NULL 'main category 2'),
        (3, 1 'sub category 1-1'),
        (4, 1 'sub category 1-2'),
        (5, 2 'sub category 2-1'),
        (6, 2 'sub category 2-2');
    
    CREATE TABLE product (
        id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
        category_id INT NOT NULL REFERENCES category(id),
        name VARCHAR(255) NOT NULL,
        description TEXT NULL
    );
    To select only categories that have products in them, use aggregation:
    Code:
    SELECT c.*, COUNT(p.id)
    FROM category AS c
    INNER JOIN product AS p ON c.id = p.category_id
    GROUP BY c.id
    HAVING COUNT(p.id) > 0;


Remove Ads

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
All times are GMT -6. The time now is 02:45 AM.
Powered by vBulletin® Version 4.2.3
Copyright © 2019 vBulletin Solutions, Inc. All rights reserved.
vBulletin Skin By: PurevB.com