Welcome Guest, Not a member yet? Register   Sign In
Get data from two tables in single foreach

(This post was last modified: 07-19-2018, 04:44 AM by kirasiris.)

So I currently have three tables:

AI = auto increment.

ci_comments: comment_id(AI), email, website, body.

ci_users: user_id(AI), username, slug, biography.

ci_relationship: id(AI), user_id, comment_id.

And I'm trying to display all the comment that have been made in the website by the users. In order to display them I have to do a foreach which returns a $query->result(); as I'm a big lover of the '->'s and works fine but I'm as well trying to display the user_id who created the comment which stored in a different table, the problem is how do I do it in the same loop?

This is my controller method:

PHP Code:
   public function index()
       $data['comments'] = $this->Comments_model->get_list();
       $data['userID'] = $this->Comments_model->get_author();


       // Load template

This is the method that I use in the model to get data from the table ci_comments:

PHP Code:
   public function get_list()
       $query $this->db->get($this->table);
       return $query->result();

and this is the what that I'm trying to create/modify/figure to get the comment_id and user_id data from the ci_relationship table in the same foreach in which I display the comments:

PHP Code:
   public function get_author(){
       $this->db->join('ci_relationship''ci_relationship.user_id = ci_users.id');

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

       if($query->num_rows() >= 1){
        } else {

and this is my view:

PHP Code:
<?php foreach($comments as $page) : ?>
          <?php if($page->status == 'draft') {
             $publish_icon '<span class="label label-info">Draft</span>';
         } elseif($page->status == 'published') {
             $publish_icon '<span class="label label-success">Published</span>';
         } else {
       $publish_icon '<span class="label label-danger">Deleted</span>' 
         $formatted_date date('F j, Y, g:i a'$date);
         $edit_icon '<i class="fa fa-pencil-square-o" aria-hidden="true"></i>';
         $delete_icon '<i class="fa fa-trash" aria-hidden="true"></i>' ;
            <td><?= $page->id?></td>
            <td><?= $publish_icon?></td>
<!-- This user_id should come from ci_relationship table -->
            <td><?= $userID->user_id?></td>
            <td><?= $page->body?></td>
              <?= anchor('admin/comments/edit/'.$page->id.''$edit_icon'class="btn btn-default btn-xs"'); ?>
              <?= anchor('admin/comments/delete/'.$page->id.''$delete_icon'class="btn btn-danger btn-xs"'); ?>
<?php endforeach; ?>

Thanks in advance.
I do Front-End development most of the time 

(This post was last modified: 07-19-2018, 06:45 AM by Pertti.)

Because comment is only made by one user, I think it wouldn't be wrong to move user_id itself to comment, however this is what you could do with ->

PHP Code:
$query $this->db
->select('ci_comments.*, ci_users.user_id, ci_users.username')
get('ci_commetns, ci_relationship, ci_users'); 


Another suggestion would be to take the query suggested by Pertti and make it into a database view. Then all you would have to do is do basic call to the database view and then push the results to the display view.

Theme © iAndrew 2016 - Forum software by © MyBB