Getting query results using data from another query, then displaying it in a view

[eluser]kwhitaker[/eluser]
Good afternoon all,

So I've got three tables - items, images and associations, and I need to write a view that displays each item with the image that it's associated with.

Here's how the logic of the tables works:

Each item can only have one image, but each image can be associated with multiple items. The associations table takes the itemid and pairs it with an imageid.

Now I could write the items query in my controller, and then write the thumbs query in my view, but that wouldn't be very MVC, would it?

So right now I have this code:

Code:

$data['query'] = $this->db->get('items');
$this->load->view('view_items', $data);

How would I use the items query to populate a thumbs query for each item and then pass that data onto the view?

Any help is appreciated.

[eluser]parrots[/eluser]
I'd recommend using SQL joins to take care of this, no need to query twice. This way each row in your resultset will contain info on the item (ID, name, description, etc) as well as the url to the image. This should give you the basic idea:

Code:

$this->db->select('items.name, items.id, items.description, images.url')->from('items');
$this->db->join('associations', 'associations.itemid = items.id', 'left');
$this->db->join('images', 'images.id = associations.imageid', 'left');
$data['query'] = $this->db->get();
$this->load->view('view_items', $data);

Then when you loop through the resultset in the view 'url' will be one of the columns for each row, you can use that to display the image without having to requery for each item.

[eluser]kwhitaker[/eluser]
Thanks! I'll give that a try!

[eluser]Jesse2303[/eluser]
[quote author="parrots" date="1214332657"]I'd recommend using SQL joins to take care of this, no need to query twice. This way each row in your resultset will contain info on the item (ID, name, description, etc) as well as the url to the image. This should give you the basic idea:

Code:

$this->db->select('items.name, items.id, items.description, images.url')->from('items');
$this->db->join('associations', 'associations.itemid = items.id', 'left');
$this->db->join('images', 'images.id = associations.imageid', 'left');
$data['query'] = $this->db->get();
$this->load->view('view_items', $data);

Then when you loop through the resultset in the view 'url' will be one of the columns for each row, you can use that to display the image without having to requery for each item.[/quote]

You can simply use a foreach too... Not necessary to use joins. ;-)

[eluser]parrots[/eluser]
Foreach to loop through the results? Yes that works well (even with the join) for looping through them but my concern would be the load caused by requerying for the img url with a query for each item within said loop. If the page starts getting hit a lot having one query per pageview with the join will be a lot more efficient than hitting your database for every item every pageview. Plus having a query in the view isn't very MVC. Databases are made for things like joins

[eluser]kwhitaker[/eluser]
So after looking at my tables I think I was over-complicating things a bit. I've condensed things down to 2 tables.

The items table (for the sake of this example) has an ID, a title, a description and a thumbnail field. The thumbnail field corresponds to a thumbnail ID in the thumbnails table, which has a thumb_id and link.

Running this code:

Code:

$this->db->select('title, id, thumbnail, description')->from('items');
$this->db->join('thumbnails', 'thumb_id = items.thumbnail','left');
$data['query'] = $this->db->get();
$this->load->view('view_items', $data);

produces no database errors.

However, when I test to make sure that the data is coming out with this code:

Code:

<? foreach($query->result() as $row): ?>

<li>
&lt;?=$row->title.",".$row->description.",".$row->id.",".$row->thumbnail."<br />".$row->thumb_id.",".$row->link?&gt;
</li>
&lt;? endforeach;?&gt;

I get this error:
[blockquote]
A PHP Error was encountered

Severity: Notice

Message: Undefined property: stdClass::$link

Filename: views/view_videos.php
[/blockquote]
I get the same error for the thumb_id

But the data from the items table posts correctly. Thoughts?

[eluser]parrots[/eluser]
The error is saying you don't have a link column, which looking at your select statement you never selected it. You need to include the thumbnail link in the select:

Code:

$this->db->select('items.title, items.id, thumbnails.link, items.description')->from('items');
$this->db->join('thumbnails', 'thumbnails.thumb_id = items.thumbnail','left');
$data['query'] = $this->db->get();
$this->load->view('view_items', $data);

If you want to have access to the variable when you use $row->var, you need to make sure you have it within the select call in active record.

I assume the code from your view you provided is only your debug code? You shouldn't need to include items.thumbnail and thumbnails.thumb_id in your select since they are just keys used by the join.

[eluser]kwhitaker[/eluser]
Link is a field inside the thumbnails table, so shouldn't it be pulled with the Join statement as well?

[eluser]parrots[/eluser]
Nope, the join only needs the two columns you merge on. Basically the point of the join clause is to say "use this table too, and link it by matching up column X and column Y between the two tables." You still use the select clause to say what columns you want to select, but you'll have access to columns in either table because of the join.