There is a table of files files as follows:

+-------------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------------+--------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | dir_id | int(11) | NO | | NULL | | | title | varchar(255) | NO | | NULL | | +-------------+--------------+------+-----+---------+----------------+

and there is a dirs folder table:

 +-----------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-----------+-------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | parent_id | int(11) | NO | | 0 | | | title | varchar(50) | YES | | NULL | | +-----------+-------------+------+-----+---------+----------------+

It is necessary for a given ID to display the number of files in the category including internal folders.

  • @ s312, According to the rules of the forum, questions should not be reduced to the decision or the completion of student assignments. Please clarify what you have done yourself and what did not work out. - VenZell

1 answer 1

There is a way to do this in one request.

 SELECT MAX(`cnt`) as `count` FROM ( SELECT @id :=CONCAT_WS(',', @id , `dirs`.`id`), @cnt:=@cnt+( SELECT COUNT(*) FROM `files` WHERE `dir_id`=`dirs`.`id` ) AS `cnt` FROM `dirs` JOIN ( SELECT @id :=1 /*Change it!*/, @cnt:=( SELECT COUNT(*) FROM `files` WHERE `dir_id`=1 /*Change it!*/) )t WHERE find_in_set(`parent_id`, @id ) )t2;

In essence, this is a kind of cycle, at the beginning we set the @id and @cnt , and then at each iteration we add a new directory id to @cnt , and the number of files in it at @cnt . Here is the SQLFiddle .

Disadvantages : the behavior of assigning and getting the value of the same variable in one query is not completely defined; indexes are not used in the search for subdirectories. Although you can probably do the same with join .