I have categories and subcategories, but each subcategory has another subcategory, the following code counts the number of pages from the root category, and its subcategories, how do I count the number of pages from the subcategories of the subcategory?

//Виджет статистики public function show_stats($widget = array()) { //Получаем все подкатегории $categ = $this->db->query("SELECT id,parent_id FROM category WHERE parent_id=".$widget['settings']['category'])->result_array(); //Считаем страницы каждой категории $this->db->select('id,category'); $this->db->where('category',$widget['settings']['category']); foreach($categ as $row) { $this->db->or_where('category',$row['id']); } $query = $this->db->get('content'); return $this->template->display('widgets/'.$widget['name'], array('widget' => $widget,'stats' => $query->result_array())); } 

Category structure
id parent_id name

Content structure
id, category, title, text

$ widget ['settings'] ['category'] = 0

How to calculate unlimited nesting?

    1 answer 1

    If I understood correctly, then the category table has not only parent_id , but also id , then in order to get the number of entries for the end category and categories lower, the query will be suitable:

     SELECT COUNT(*) FROM content INNER JOIN category ON category.id = content.category WHERE (content.category = 0) OR -- Все записи связанные с категорией 0 (category.parent_id = 0) -- Все записи связанные с дочерними категориями от 0 
    • He counted only from subcategories, and from the root category did not count the entries. Tried to do this (content.category = 0) AND (category.parent_id = 0), yields 0 - chuikoff
    • This request counts the total number of entries in root (content.category = 0) and its subcategories (category.parent_id = 0) . - KiTE
    • The output is one value - the total number of records. - KiTE
    • I see. Thanks. Only for some reason, CI complains of such a structure, of the string type of the wrong type. - chuikoff
    • Add the structure of both tables to the question. - KiTE