Get value from joined tables, Codeigniter-CodePudding

I'm trying to get and display the data from joined tables but I can't get anything in return. I reference this and it worked on my other function, but when I tried it again on a different function, I can't get any results.

Here's the Model:

public function viewReview($id)
{
    $this->db->select('clients.firstname, clients.lastname, packages.title, rate_review.review, rate_review.date_created');
    $this->db->where('rate_review.id', $id);
    $this->db->join('clients', 'clients.id = rate_review.user_id');
    $this->db->join('packages', 'rate_review.package_id = packages.id');
    $this->db->from('rate_review');

    $query = $this->db->get();
    return $query->row_array();
}

Controller:

public function view_review($id)
{
    $data['title'] = 'Rate & Reviews';
    $data['review'] = $this->Admin_model->viewReview($id);

    $this->load->view('../admin/template/admin_header');
    $this->load->view('../admin/template/admin_topnav');
    $this->load->view('../admin/template/admin_sidebar');
    $this->load->view('../admin/rate_review/view', $data);
    $this->load->view('../admin/template/admin_footer');
}

View:

    <div >
  <p>User:
    <?php echo $review['firstname'].' '. $review['lastname']; ?>
  </p>
  <p>Package:
    <?php echo $review['title']; ?>
  </p>
  <div >
    <pre ><?php echo $review['review']; ?></pre>
    <br>
    <span ><?php echo $review['date_created']; ?></span>
  </div>
</div>

CodePudding user response：

the query seems to be fine except that we do not know if the data really exists in those joined tables. Are you sure data if there? the best way to debug is to use profiler so that you preview the real query built $this->output->enable_profiler(TRUE), after that take that query and run it on the mysql directly. place this anywhere in your controller $this->output->enable_profiler(TRUE).

CodePudding user response：

Please be sure the path is correct and try this query in your model

public function viewReview($data) {
        $sql = "SELECT c.firstname as firstname, c.lastname as lastname, p.title as title, r.review as review, r.date_created as date_created FROM rate_review r INNER JOIN clients c ON (c.id = r.user_id) 
 INNER JOIN packages p ON (p.id = r. package_id) WHERE r.id = ?";
        $query = $this->db->query($sql, $data);
        return (array)$query->row();
}

Also please change this line in your controller

$data['review'] = $this->Admin_model->viewReview(array($id));