CODEWITHSUNDEEP
phpmysqlcodeigniter
kanarifugl
kanarifugl

Reputation: 10007

Fetch username and user ID from another table in CodeIgniter

I have created a model and controller where i fetch a list of users reviews and I want to show their username and ID in my view which are stored in another table called users, however I haven't been able to figure out exactly how to do so.

This is my model

public function fetch_company_reviews($id = NULL) {

    $this->db->select('*');
    $this->db->where('customer_company_reviews_company_id', $id);
    $this->db->from('customer_company_reviews');
    $this->db->order_by('customer_company_reviews_id', 'desc');
    $this->db->limit(1);
    $query = $this->db->get();
    if($query->num_rows() == NULL) {
        return false;
    }
    else {
        return $query->result();
    }

}

This is my controller

public function index($id = NULL)
{
    $id = $this->input->get('id'); 
    $data['company_info'] = $this->Company_model->fetch_company($id);
    $data['company_reviews'] = $this->Company_model->fetch_company_reviews($id);
    $get = $data['company_info'];
    $this->load->view('includes/header');
    $this->load->view('company/company_index', $data);
    $this->load->view('includes/footer');
}

This is my view

<?php foreach ($company_reviews as $review) { ?>
<div class="review-box">
  <div class="row">
    <div class="col-sm-3">

    </div>
    <div class="col-sm-9">
      <h4><?= $review->customer_company_reviews_title; ?></h4>
      <?= $review->customer_company_reviews_comment; ?>
    </div>
  </div>
<?php } ?>

Table structure users

id | username
-------------
 1 | admin

Table structure customer_company_reviews

cu...reviews_id | cu...reviews_author | cu...reviews_title
---------------------------------------------------------
 1              | 1                  | Some title

How can i fetch and access id and username from users-table where customer_company_reviews_author-row is storing the user ID in my review table (customer_company_reviews) and then finally show it in my view as a variable inside my foreach loop?

Upvotes: 1

Views: 3571

Answers (2)

PHPExpert
PHPExpert

Reputation: 943

Use join with User table as below

$this->db->select('customer_company_reviews.* ,users.*');
$this->db->join('users', 'users.id = customer_company_reviews.customer_company_reviews_author');
$this->db->where('customer_company_reviews_company_id', $id);
$this->db->from('customer_company_reviews');
$this->db->order_by('customer_company_reviews_id', 'desc');
$this->db->limit(1);

use * from both the tables as we want both id and username from user table.

Upvotes: 1

Angel
Angel

Reputation: 612

Friend You can easily fetch the result by using join.I wil help you

In Model

public function fetch_company_reviews($id = NULL) 
{       
    $this->db->select('*');
    $this->db->from('customer_company_reviews');
    $this->db->join('users', 'customer_company_reviews.customer_company_reviews_company_id = users.reference_review_id', 'left');
    $this->db->where('customer_company_reviews_company_id', $id);
    $this->db->order_by('customer_company_reviews_id', 'desc');
    $this->db->limit(1);
    $query = $this->db->get();
    return $query->result();
}

Upvotes: 1

Related Questions