How to structure effectively news and category relationship ?

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

Thank you very much i will test it.

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

Romanian CodeIgniter Team :: Translations :: Comunity :: Developers
http://www.codeigniter.com.ro

i saw and when i replied it disappeared

Please post it again

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(); 


Romanian CodeIgniter Team :: Translations :: Comunity :: Developers
http://www.codeigniter.com.ro

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.