CODEWITHSUNDEEP
phpmysql
Dumbo
Dumbo

Reputation: 14142

counting all sub categories whitin a category

I have database with following columns:

id, parent, name, description

The idhas been set to auto incerement. I want to get total number of all the subcategories that are inisde the parent category (which has it's parent set to 0) I also want this to be reccursive. What can be the fastes way of doing so?

I already wrote the following function but I can not test it at the moment:

function CountDeepSubCategories($parentID, $max = 0)
{
    global $_DB;

    $all = $all + $max;

    $q = $_DB->Query("SELECT id FROM category_cats WHERE parent = $parentID");
    $id = $_DB->GetResultValue($q, 0, 'id');

    $num = $_DB->GetResultNumber($q);       

    for($i = 0; $i < $num; $i++)
    {
         CountDeepSubCategories($id, $all);
    }

    return $all;        
}

Upvotes: 0

Views: 870

Answers (4)

Kornel
Kornel

Reputation: 100200

The fastest (in SQL), but definitely not the easiest way, is to use preordered tree structure:

http://www.sitepoint.com/hierarchical-data-database-2/

Upvotes: 0

Taha Paksu
Taha Paksu

Reputation: 15616

does every category have a parent? if that you can do "select count(*) from category_cats" :)

ok, for the second comment that you've posted, why dont you just add an insider query like this: 

select cct.id,
    (select count(1) from category_cats where parent=cct.id) as subcount
from category_cats cct

i've not tested it but it should work.

Upvotes: 1

ma cılay
ma cılay

Reputation: 739

Take a look at the presentation from Bill Karwin at http://www.slideshare.net/billkarwin/sql-antipatterns-strike-back (your problem is described at page 48). He explains pros and cons of your solution and offers three other ones and compares them against each other.

Upvotes: 2

clops
clops

Reputation: 5255

Recursion seems like an elegant solution, it is however by far not the best-performer. You might consider storing you category tree as a Nested Set which is quite an overhead when inserting new records, it, however, provides an advantage of getting the whole tree with one simple SQL query.

Upvotes: 1

Related Questions