How to structure effectively news and category relationship ?

How to structure effectively news and category relationship ? - 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: How to structure effectively news and category relationship ? (/showthread.php?tid=483)

RE: How to structure effectively news and category relationship ? - paju89 - 12-09-2014

Thank you very much i will test it.

RE: How to structure effectively news and category relationship ? - Dracula - 12-09-2014

(12-09-2014, 08:21 AM)paju89 Wrote: i have two tables in database

CATEGORY table below if parent id is set to 0 is main category if it is set to any its subcategory
cat_id | name | parent_id
---------------------------------------
1 | Electronics | 0
2 | Computer | 1
3 | Computer | 1

NEWS table below

id | news_name | news_text | cat_id
---------------------------------------

i wonder how to make them related each others via common id ? Any ideas

Where did my answer go Dodgy

RE: How to structure effectively news and category relationship ? - paju89 - 12-09-2014

i saw and when i replied it disappeared

RE: How to structure effectively news and category relationship ? - paju89 - 12-09-2014

Please post it again

RE: How to structure effectively news and category relationship ? - Dracula - 12-09-2014

(12-09-2014, 08:21 AM)paju89 Wrote: i have two tables in database

CATEGORY table below if parent id is set to 0 is main category if it is set to any its subcategory
cat_id | name | parent_id
---------------------------------------
1 | Electronics | 0
2 | Computer | 1
3 | Computer | 1

NEWS table below

id | news_name | news_text | cat_id
---------------------------------------

i wonder how to make them related each others via common id ? Any ideas

Try something like this:

PHP Code:
$this->db->select('category, news')
    ->from('category as t1')
    ->where('t1.cat_id', $id)
    ->join('news as t2', 't1.cat_id = t2.cat_id', 'LEFT')
    ->get(); 

RE: How to structure effectively news and category relationship ? - Rufnex - 12-11-2014

If you just want to join all articles to the associated category you just need a quey like that.

PHP Code:
$this->db->from('category')
     ->join('news', 'category.cat_id = news.cat_id')
     ->where('category.cat_id', $cat_id)
     ->get(); 

cat_id is the matching key for both tables.