CODEWITHSUNDEEP
mysqlactiverecordcodeigniterselectjoin
Joey Duke
Joey Duke

Reputation: 1

How can I SELECT from multiple tables in CodeIgniter

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 author's 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 author's 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:

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:

<?php foreach($articles->result() as $row) { ?>
<?php echo $row->author ?>
<?php echo $row->text ?>
<?php } ?>

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

If I echo the above $row->author, I'm only going to get the user_id. I need to get the user's 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 :)

Upvotes: 0

Views: 16248

Answers (2)

Joey Duke
Joey Duke

Reputation: 1

I have found a solution that works well for me which I will post and try to explain.

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.

Upvotes: 0

Manie
Manie

Reputation: 2028

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.

Upvotes: 1

Related Questions