CodeIgniter Forums
Query multiple tables and echo the result - Printable Version

+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Archived Discussions (https://forum.codeigniter.com/forumdisplay.php?fid=20)
+--- Forum: Archived Development & Programming (https://forum.codeigniter.com/forumdisplay.php?fid=23)
+--- Thread: Query multiple tables and echo the result (/showthread.php?tid=33756)

Query multiple tables and echo the result - El Forum - 09-07-2010

[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:
&lt;?php foreach($entries->result() as $entry) { ?&gt;
    <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?

Query multiple tables and echo the result - El Forum - 09-08-2010

[eluser]gmask[/eluser]
Bump

Query multiple tables and echo the result - El Forum - 09-08-2010

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

Query multiple tables and echo the result - El Forum - 09-08-2010

[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!

Query multiple tables and echo the result - El Forum - 09-08-2010

[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.

Query multiple tables and echo the result - El Forum - 09-08-2010

[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:
&lt;?php foreach($entries->result() as $entry) { ?&gt;
    <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

Query multiple tables and echo the result - El Forum - 09-08-2010

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

Query multiple tables and echo the result - El Forum - 09-08-2010

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

Query multiple tables and echo the result - El Forum - 09-08-2010

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