Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2004
    Location
    Ninth Hell
    Posts
    19

    Unanswered: recursive counts

    is there another way to do the following:

    function get_file_count($cat_id)
    {
    $filesincat = 0;
    if (!$cat_id == "")
    {
    $result = mysql_query("SELECT cat_id FROM cat WHERE cat_parent = $cat_id");
    $filesincat += mysql_query("SELECT count(*) FROM files WHERE file_catid = $cat_id");
    while($i = mysql_fetch_row($result))
    {
    $filesincat += get_file_count($i[0]);
    }
    }
    return $filesincat;
    }

    so that less overall sql queries are used. I am trying to find the number of entries that belong to a category or it's sub-categories. The above, even though effecient, seems to generate alot of queries.

  2. #2
    Join Date
    Feb 2002
    Location
    San Francisco, CA
    Posts
    441
    You might try running the second query as a subquery in the first; i.e.

    Code:
    SELECT cat_id,(SELECT count(*) FROM files WHERE file_catid = $cat_id) as cnt FROM cat WHERE cat_parent = $cat_id
    This would have the advantage of sending less database queries and should be quite a bit faster.

    You may even be able to make the query in to one (i.e. no sub query) - but I don't know would have to know more about your database...

  3. #3
    Join Date
    Feb 2004
    Location
    Ninth Hell
    Posts
    19
    Database structure:
    TABLE: cat
    cat_id int
    cat_parent int
    name varchar(10)

    TABLE: files
    file_catid int
    name varchar(10)

    the first table using cat_id and cat_parent creates a tree like structure for the categories, ie: each category can have unlimited sub-categories and the actual depth of the tree has to be open. Also my database runs on a MySQL 3xxxx version so no subqueries

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •