Trying to join tables to display the author of blog entry

[eluser]Brad Morse[/eluser]
I am trying to add more to the blog tutorial.

Here is the blog view:

Code:

<?php foreach($query->result() as $row): ?>
    <h2>&lt;?=$row->title?&gt;</h2>
    &lt;?php
        $body_text = $row->body;
        print character_limiter($body_text, 210);
    ?&gt;
    <p>Author: &lt;?=$row->author_id?&gt;</p>
    <p>&lt;?=anchor('blog/more/'.$row->id, 'Read on');?&gt;</p>
    <p>&lt;?=anchor('blog/comments/'.$row->id, 'Comments');?&gt;</p>
    <hr>
&lt;?php endforeach; ?&gt;

It displays the author id for now (entries.author_id = authors.id), I want it to display the author first (authors.first_name) and last name (authors.last_name). Could you please help me?

Here is the blog controller:

Code:

function index() {

    $data['title'] = "My Blog Title";
    $data['heading'] = "My Blog Heading";
    $data['query'] = $this->db->get('entries');
    
    $this->load->view('blog_view', $data);
}

[eluser]missionsix[/eluser]
well, since i'm not an active record db guy, i'll just give you the sql used:

Code:

$SQL = "SELECT blog_entries.*, users.* FROM blog_entries
LEFT JOIN users ON (blog_entries.author_id = users.user_id) WHERE
blog_entries.id = {$blog_entry_id}";

I hope you know how to read SQl. The two tables are blog_entries & users, i think you can figure it out though.

[eluser]Brad Morse[/eluser]
This is what I came up with with a little digging thru the forums:

Please let me know if there is a better way to do this:

Code:

Controller

<?php
function index() {

    $data['title'] = "My Blog Title";
    $data['heading'] = "My Blog Heading";
    //$data['query'] = $this->db->get('entries');
    
    $this->db->select('*');
    $this->db->from('entries');
    $this->db->join('authors', 'authors.id = entries.author_id');
    $data['query'] = $this->db->get();
    
    $this->load->view('blog_view', $data);
}
?>

View

<?php foreach($query->result() as $row): ?>
    <h2>&lt;?=$row->title?&gt;</h2>
    &lt;?php
        $body_text = $row->body;
        print character_limiter($body_text, 210);
    ?&gt;
    <p>Author: &lt;?=$row->first_name.' '.$row->last_name?&gt;</p>
    <p>&lt;?=anchor('blog/more/'.$row->id, 'Read on');?&gt;</p>
    <p>&lt;?=anchor('blog/comments/'.$row->id, 'Comments');?&gt;</p>
    <hr>
&lt;?php endforeach; ?&gt;

[eluser]crumpet[/eluser]
i like to add
$this->db->join('authors', 'authors.id = entries.author_id', 'inner');
so that if the user account gets deleted then the blog entry will not appear
this is a matter of taste however - maybe you want the blog entry to still appear if the user account is deleted. if so you will need to have error checking so you dont get Undefined first_name error