news category and subcategory

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 !

thank you for your reply but i think somewhere i did mistake or this code gives error. below i post how i did and what error occured.