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