news category and subcategory - Printable Version

news category and subcategory - Printable Version

+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Using CodeIgniter (https://forum.codeigniter.com/forumdisplay.php?fid=5)
+--- Forum: General Help (https://forum.codeigniter.com/forumdisplay.php?fid=24)
+--- Thread: news category and subcategory (/showthread.php?tid=493)

news category and subcategory - paju89 - 12-10-2014

i have table CAT if parent_id is set to zero its main if not its subcategory
cat_id | name |parent_id
1 Bob 0
2 John 1
3 Mike 1

and table NEWS

id | cat_id | title | text
1 1 text some text
2 2 any any text
3 3 another another text

my url structure is ..mysite/page/cat_id

question is if i click on link BOB whos id is 1 i want to get all data from NEWS table with title (text, any, another)
and if i click on link John whos id is 2 i want to get all data from NEWS table with title (any)
and if i click on link Mike whos id is 3 i want to get all data from NEWS table with title (another)

below is code i write only gives me news set to its cat_id not working for main category bob.

PHP Code:
$this->db->select('*');      
         $this->db->where('cat_id', $cat_id);       
         $query = $this->db->get("news");
       return $query->result(); 

RE: news category and subcategory - _this - 12-10-2014

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 !

RE: news category and subcategory - paju89 - 12-10-2014

the below code gives error

Code:
Error Number: 1052

Column 'cat_id' in where clause is ambiguous

SELECT * FROM (`news`) JOIN `cat` ON `news`.`cat_id` = `cat`.`cat_id` WHERE `cat_id` = '1' OR `parent_id` = '1'

Filename: C:\wamp\www\toyda\system\database\DB_driver.php

Line Number: 330

PHP Code:
$query = $this->db->from("news")
                ->join("cat", "news.cat_id = cat.cat_id")
                ->where("cat_id", "1")
                ->or_where("parent_id", "1")
                ->get();      
  return $query->result(); 

RE: news category and subcategory - paju89 - 12-10-2014

(12-10-2014, 07:32 AM)$this Wrote: 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.

RE: news category and subcategory - _this - 12-10-2014

That's my fault, replace the code by this one :

PHP Code:
$query = $this->db->from("news")
                ->join("cat", "news.cat_id = cat.cat_id")
                ->where("cat.cat_id", "1")
                ->or_where("parent_id", "1")
                ->get();      
  return $query->result(); 

You can also do it like this to avoid using a var that wouldn't be used later :

PHP Code:
return $this->db->from("news")
                ->join("cat", "news.cat_id = cat.cat_id")
                ->where("cat.cat_id", "1")
                ->or_where("parent_id", "1")
                ->get()
                ->result(); 

RE: news category and subcategory - paju89 - 12-10-2014

(12-10-2014, 08:59 AM)$this Wrote: That's my fault, replace the code by this one :

PHP Code:
$query = $this->db->from("news") ->join("cat", "news.cat_id = cat.cat_id") ->where("cat.cat_id", "1") ->or_where("parent_id", "1") ->get(); return $query->result();

You can also do it like this to avoid using a var that wouldn't be used later :

PHP Code:
return $this->db->from("news") ->join("cat", "news.cat_id = cat.cat_id") ->where("cat.cat_id", "1") ->or_where("parent_id", "1") ->get() ->result();

THANKS MAN. all day i crawled on the net and tried so much effort i spent. You saved my day . Your solution works like a Charm. Thank you again

RE: news category and subcategory - _this - 12-10-2014

Ok, great to hear it !

I think you should learn a bit more about SQL, here it was just a problem of junction between tables and logical dilemma about AND/OR operators for the where clause, it's just a classic for beginners ^^ !

Keep working and enjoy CI Smile