• 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
news category and subcategory

#1
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
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


Digg   Delicious   Reddit   Facebook   Twitter   StumbleUpon  


  Theme © 2014 iAndrew  
Powered By MyBB, © 2002-2020 MyBB Group.