Hello,
First of all, you could make your query much clearer & easier like this :
PHP Code:
return $this->db->get_where("news", array("cat_id" => $cat_id))
->result();
... which produces :
Code:
SELECT * FROM (`news`) WHERE `cat_id` = $cat_id
If I follow what I think is in your mind, you first want to retrieve all news from "Bob" category as well as news from "children" of "Bob" category. Here is the query to do so :
Code:
SELECT *
FROM (`news`)
JOIN `cat` ON `news`.`cat_id` = `cat`.`cat_id`
WHERE `cat_id` = '1'
OR `parent_id` = '1';
With CI it gives this code :
PHP Code:
return $this->db->from("news")
->join("cat", "news.cat_id = cat.cat_id")
->where("cat_id", "1")
->or_where("parent_id", "1")
->get();
And returns this result :
Code:
______________________________________________________________________
| id | cat_id | title | text | cat_id | name | parent_id |
| 1 | 1 | text | some text | 1 | Bob | 0 |
| 2 | 2 | any | any text | 2 | John | 1 |
| 3 | 3 | another | another text | 3 | Mike | 1 |
----------------------------------------------------------------------
Now this function also works with other categories because if you try with an id
2 it returns :
Code:
______________________________________________________________________
| id | cat_id | title | text | cat_id | name | parent_id |
| 2 | 2 | any | any text | 2 | John | 1 |
----------------------------------------------------------------------
I think that I answered your question, if not feel free to tell me and I'll try to help more !