How can I SELECT from multiple tables in CodeIgniter

Question

This is really boggling my mind on how to do this in ActiveRecord Queries in CodeIgniter. Maybe I'm over complicating it.

I have three tables: (to keep it simple I'll only show the relevant fields)

Articles (id, title, text, author) Comments (id, article_id, text, author) Users (user_id, user_type, first_name, last_name, email, password)

In the 'Articles' table, because an author can have multiple articles, I only store the authors 'user_id' from the 'Users' table, and I do the same for the comments table.

My problem is when I run an ActiveRecord query in the articles_model to get all articles along with that articles associated comments, and then echo the author in my view with $row->author, I only get the authors user_id which is 1, 2, 3, etc etc.

How do I go into the users table (different table) in that same query and get the authors actual first_name and last_name and return that?

Here's my code: In the main controller, I call a function 'getArticles'.

Controller: $data['articles'] = $this->getArticles(); $this->load->view('articles_view', $data);

function getArticles() { $this->load->model('articles_model'); $articles = $this->articles_model->getAllArticles(); return $articles; }

articles_model: function getAllArticles() { $this->db->where('id', $this->uri->segment(3)); $query = $this->db->get('articles'); return $query; }

articles_view: result() as $row) { ?> author ?> text ?>

I have a ton of other fields, but this is an extremely stripped down version for simplicity sake.

If I echo the above $row->author, I'm only going to get the user_id. I need to get the users name from the 'users' table instead. Surely I don't have to do another separate function call and pass more information into the view. Any help here would be greatly appreciated and would actually open up a world for me :)

Thanks.

Answer 1

Try this (or just have an idea):

I assumed that user_id from your users table is foreign key of articles and comments table since you said that the authors and commentators user_id are stored in users table.

On your controller:

$data['articles'] = $this->getArticles(); 
$this->load->view('articles_view', $data);

function getArticles() { 
   $this->load->model('articles_model'); 
   $articles = $this->articles_model->getArticlesById(); 
   return $articles; 
}

On your model:

// this will get the article of a certain author   
function getArticlesById()
{
   $this->db->where('id', $this->uri->segment(3)); 
   $q = $this->db->get('articles'); 
   if($q->num_rows() > 0)
   {
       $q = $q->row_array();
       $result = $this->getCommentsAndAuthors($row);
       return $result;
   }
   return 0;
}

// this will get the comments of each articles of the author and the author of the comments
function getCommentsAndAuthors($row)
{
    $result = $data = array();

    $this->db->select('c.*, u.firstname, u.lastname');
    $this->db->from('users u');
    $this->db->where('u.id', $row['user_id']);
    $this->db->join('comments c', 'c.id = u.id');
    $q = $this->db->get();
    foreach($q->result_array() as $col)
    {
        $data[] = $col;
    }
    $result['article'] = $row;
    $result['comments'] = $data;
    return $result;
}

I'm not sure if its work but that's the idea.

Answer 2

First off, thank you for the help. I have found a solution that works well for me which I will post and try to explain. I'm a noob at CI as well as PHP for the most part, so bare with me.

My Controller is like this:
function fullArticle()
{
$data['article'] = $this->getArticleDetail();
$data['comments'] = $this->getNewsTrendsComments();
$this->load->view('inner_view', $data);
}

function getArticleDetail()
{
$this->load->model('articles_model');
$articles = $this->articles_model->getArticles();
return $articles;
}

function getComments()
{
$this->load->model('articles_model');
$comments = $this->articles_model->getComments();
return $comments;
}


articles_model:
function getArticles()
{
$this->db->where('id', $this->uri->segment(3));
$query = $this->db->get('articles');
return $query;
}

function getComments()
{
$this->db->select('*');
$this->db->from('comments w');
$this->db->where('article_id', $this->uri->segment(3));
$this->db->join('users wc','w.author = wc.user_id');
$data = $this->db->get();
return $data;
}

As you can see, the getComments function in my model was the key. It's the JOIN statement that handles what I was trying to accomplish. I know there are probably cleaner and more efficient ways to do this, by combining the two functions, but as a beginner I wanted to keep it easier to understand.

Hopefully this can help others.