Query multiple tables and echo the result

[eluser]gmask[/eluser]
Hello, I'm having a really hard time getting the results I want from the database. Here's what I'm trying to do: I have an entries view that lists all of the entries in my database. In each of these entries is a field called 'category' which points to the `categories` table. I would like to be able to have the ability to show which category my entry was posted in, but I can't seem to get it working. Here's the way I'm going about it, please correct me if I'm being silly:

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 controller

Code:

function entries()
{
    $config['base_url'] = base_url().'/entries/';
    $config['total_rows'] = $this->db->count_all('entries');
    $config['per_page'] = '10';
    $config['full_tag_open'] = '<div id="pagination">';
    $config['full_tag_close'] = '</div>';
    $this->pagination->initialize($config);
        
    $this->load->model('fetch_model');
    $data['entries'] = $this->fetch_model->entries($config['per_page'],$this->uri->segment(3));
    $this->template->load('templates/default', 'entries', $data);
}

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?

[eluser]gmask[/eluser]
Bump

[eluser]bcorcoran[/eluser]
Post your database tables, and I might be able to give you an answer.

[eluser]gmask[/eluser]
The `entries` table has an entry with the following structure

Code:

id -> '1'
title -> 'My Title'
content -> 'My Content'
category -> '1'

The `categories` table has an entry with the following structure

Code:

id -> '1'
title -> 'General'

In case I wasn't too clear in the first post what I want is to be able to change the

Code:

$entry->category

call in my view.php to display the corresponding category title instead of its id (which is obviously stored in the entry table itself). I understand this requires two queries, but I'm not sure how to go about it.

Thanks!

[eluser]bcorcoran[/eluser]
Ahhh, ok.

Here's a lesson in SQL:

If you have two tables you need to join (in your case), and the column names are colliding (i.e. both tables have ID field and you need to use each of them separately).

You want to do the following:

Code:

SELECT entries.id, entries.title, categories.id as cid, categories.title as ctitle

Then this will be $entry->cid and $entry->ctitle for cat id and cat title respectively.

Let me know if this doesn't make sense.

[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

[eluser]bcorcoran[/eluser]
Haha, same response (more or less), 1 minute apart!

[eluser]kaejiavo[/eluser]
you were a bit faster

[eluser]gmask[/eluser]
You guys are awesome, thanks a lot!