Active record - Join question

[eluser]Haskabab[/eluser]
Hi!

I've got a question about how to combine 2 queries using JOIN with the active record class.

I got 2 database tables:

`player_items`
- id
- item_id

and

`items`
- id
- name

I know that the player_items.id = 1, and i want to know what that item's name is. Normally i would do it like this:

Code:

$player_items_info = $this->db->query("
   SELECT *
   FROM `player_items`
   WHERE `id` = '1'
")->row();

$item_info = $this->db->query("
   SELECT *
   FROM `items`
   WHERE `id` = '".$player_items_info->item_id."'
")->row();

$item_name = $item_info->name;

But this can get very annoying when I know for certain that there's an easier way using joins.

So if anyone knows how to do it, please tell me.

Thanks in advance

[eluser]n0xie[/eluser]
Use $this->db->join like mentioned in the user guide

[eluser]boldyellow[/eluser]
I've found active records for queries a huge help... especially for joins.

I'm still learning CI, but the documentation for active records makes it easy to understand the example queries in there.

Specifically, for my current project, I had two tables (gallery_names, gallery_types) to join for the gallery name (like, Fall 2010) and the gallery type (like, Student Photos).

Code:

$this->db->select('gallery_names.*, gallery_types.type_name');
        $this->db->from('gallery_names');
        $this->db->where('gallery_names.id', $id);
        $this->db->join('gallery_types', 'gallery_types.id = gallery_names.gallery_type');
        $query = $this->db->get();

[eluser]Haskabab[/eluser]
@n0xie: No shit.. I was asking for an explanation.

@boldyellow: Thanks for the example! Got it working.

Cheers