• 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Get data from two tables in single foreach

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

 
       //print_r($data['userID']);

 
       // Load template
 
       $this->template->load('admin''default''comments/index'$data);
 
   

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->select('*');
 
       $this->db->from($this->table);
 
       $this->db->join('ci_relationship''ci_relationship.user_id = ci_users.id');

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

 
       if($query->num_rows() >= 1){
            return 
$query->result_array();
        } else {
            return 
false;
 
       }
 
   

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>' ;
 
         ?>
          <tr>
            <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>
            <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"'); ?>
            </td>
          </tr>
<?php endforeach; ?>

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

#2
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')
    ->
where('ci_comments.comment_id''ci_relationship.comment_id'false)
    ->
where('ci_relationship.user_id''ci_users.user_id'false)
    ->
get('ci_commetns, ci_relationship, ci_users'); 
Reply

#3
@kirasiris,

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.
Reply


Digg   Delicious   Reddit   Facebook   Twitter   StumbleUpon  


  Theme © 2014 iAndrew  
Powered By MyBB, © 2002-2020 MyBB Group.