Codeigniter: Trying to run a query within a loop in the View

Question

I have a page that displays posts and I want to get the name of the author next to each post. There is a DB table for pages (with a column called title and author) and one for authors (with a column called id and author).

Control

function pages() {
  // QUERIES
  $pages_query = $this->db->get('table_pages');
  $authors_query = $this->db->get_where('table_authors', array('id =' => $row->author));

  // DATA
  $data['pages'] = $pages_query;
  $data['authors'] = $authors_query;

  // CREATE
  $this->load->view('admin_pages', $data);
}

View

    <? foreach ($pages->result() as $row): ?>
         Title: <?=$row->title?>, by <?=$authors['id']?><br />
    <? endforeach; ?>

I think my issue is in calling the author based on the id in control but I just can't seem to figure it out.

Answer 1

Basically, you want to JOIN the two tables, so you can access the author names directly, you don't need to use two queries for that...

You want to do something like:

function pages() {
    // QUERIES
    $this->db->join('table_authors', 'table_pages.table_authors_id = table_authors.id');
    $pages_query = $this->db->get('table_pages');

    // DATA
    $data['pages'] = $pages_query;

    // CREATE
    $this->load->view('admin_pages', $data);
}

View

<? foreach ($pages->result() as $row): ?>
     Title: <?=$row->title?>, by <?=$row->table_authors_name?><br />
<? endforeach; ?>

You might have to adjust some column names, as I don't know your database structure. If both table_pages and table_authors contain name or title fields, you'll have to rename the columns in your query. You can do this using

$this->db->select('table_authors.title AS author_name, ...');