Query multiple tables and echo the result

[eluser]kaejiavo[/eluser]
[quote author="gmask" date="1283905681"]
...

This is in my model

Code:

function entries($num, $offset)
{
    $query = $this->db->query("SELECT categories.title, categories.id, entries.* FROM entries,categories WHERE entries.category=categories.id ORDER BY entries.type DESC, entries.id DESC LIMIT $num");
    return $query;
}

...
This is the relevant section of my view

Code:

<?php foreach($entries->result() as $entry) { ?>
    <div class="result">
        <p class="left-title">&lt;?php echo anchor('edit_entry/'.$entry->id, $entry->title); ?&gt;</p>
        <p class="right-title">&lt;?php echo anchor('edit_category/'.$entry->category, 'Parent'); ?&gt;</p>
    </div>
&lt;?php } ?&gt;

Obviously I'd like to be able to echo the category title field in the anchor, replacing the 'Parent' text.

What's the best way to do this?[/quote]

1. Change your query

Code:

function entries($num, $offset)
{
    $query = $this->db->query("SELECT categories.title as category_title, categories.id as category_id, entries.* FROM entries,categories WHERE entries.category=categories.id ORDER BY entries.type DESC, entries.id DESC LIMIT $num");
    return $query;
}

In your actual query the field categories.title has the same name as entries.title
entries.title will overwrite categories.title because it is defined later, so you have to alias this column. same with the id columns.

2. in your view change

Code:

anchor('edit_category/'.$entry->category_id, $entry->category_title)

Marco