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;
}
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.
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.
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.
The fastest (in SQL), but definitely not the easiest way, is to use preordered tree structure: