db join & "alias"

[eluser]prototype20[/eluser]
Hi everybody!

(sorry for my bad english
I have an interesting problem since days.
I have two tables: article, category

Article table:
id int(11)
title (varchar 128)
description (varchar 128)
text (text)
cat_id(int 11)

Category table:
id (int 11)
name (varchar 128)

I would like to connect this two table:
article.cat_id = category.id

This is from his greatest part properly till now, I cannot solve it in order for the article.cat_id to receive the name of the category.name however

I am amateur on space like this, so I would like this:
article.cat_id = category.id
query final result => category.name by id

Please help me, or send tutorial link.

Thanks!

[eluser]cahva[/eluser]
Seems like simple join to me:

Code:

$this->db->select('*');
$this->db->from('article');
$this->db->join('category', 'category.id = article.cat_id');

$query = $this->db->get();

Or am I missing something?

[eluser]prototype20[/eluser]

Code:

class Home extends Controller
{
    
    function Home()
    {
        parent::Controller();
        $this->load->model('categorymodel');
        $this->load->model('frontpagearticlemodel');
    }

...

function article()
    {
        $home['title'] = 'Article details';
        $home['category']=$this->categorymodel->fetchAllRows('category');
        $home['article'] = $this->categorymodel->fetchRow($this->uri->segment(3),'id','article');
        $this->load->view('header/header_home', $home);
        $this->load->view('article_view', $home);
    }

Model

Code:

class categorymodel extends Model
{
    function categorymodel()
    {
        parent::Model();
        $this->load->database();
    }

    function fetchAllRows($table)
    {
        $query=$this->db->get($table);
        
        if($query->num_rows()>0)
        {
            return $query->result_array();
        }
        
    }
    
    function fetchRow($param,$field,$table)
    {
        $this->db->where($field,$param);
        $query=$this->db->get($table);
        return $query->result_array();
    }

View

Code:

<?php if(isset($article)) : foreach($article as $a) : ?>
    <a href="&lt;?php echo base_url();?&gt;">Home</a> » <[b]?php echo anchor('home/category/'.$a['cat_id'],''.$a['cat_id']);?&gt;[/b]

    » &lt;?php echo $a['title'];?&gt;
  </div>
<h2>&lt;?php echo $a['title'];?&gt;</h2>
<p>&lt;?php echo $a['description'];?&gt;</p>
<div>&lt;?php echo$a['text'];?&gt;</div>
&lt;?php endforeach;?&gt;

i want rename $a['cat_id] to category name => in db -> category.name

how to? >:-(

[eluser]cahva[/eluser]
I would create a new model for the article(just so its clear that its articles you are fetching with it).

Code:

<?php
class Article_model extends Model {

    function __construct()
    {
        parent::__construct();
    }

    function fetchRow($id)
    {
        $this->db->select('article.id,title,description,cat_id,category.name catname');
        $this->db->from('article');
        $this->db->join('category', 'category.id = article.cat_id');
        $this->db->where('article.id',$id);

        $query = $this->db->get();

        return $query->result_array();
    }

}

In the constructor you ofcourse load the model:

Code:

$this->load->model('article_model');

And fetch the results in article method:

Code:

$home['article'] = $this->article_model->fetchRow($this->uri->segment(3));

BTW, you should check that $this->uri->segment(3) actually exists. You can also give parameter to your article method so you wont have to use that longish $this->uri...:

Code:

function article($id = FALSE)
    {
        if (!$id)
        {
            redirect('home');
        }
        
        $home['title'] = 'Article details';
        $home['category']=$this->categorymodel->fetchAllRows('category');
        $home['article'] = $this->article_model->fetchRow($id);
        $this->load->view('header/header_home', $home);
        $this->load->view('article_view', $home);
    }

That is just an example, but you get the point.

Oh yeah, and in the view you can find the categoryname with $a['catname']