Welcome Guest, Not a member yet? Register   Sign In
news category and subcategory
#1

(This post was last modified: 12-10-2014, 05:20 AM by paju89.)

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(); 
Reply
#2

(This post was last modified: 12-10-2014, 07:34 AM by _this.)

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 !
Reply
#3

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(); 
Reply
#4

(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.
Reply
#5

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(); 
Reply
#6

(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
Reply
#7

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
Reply




Theme © iAndrew 2016 - Forum software by © MyBB