I'd assume this is a JOIN question...

[eluser]Jesse Schutt[/eluser]
Hello guys,

I have a question about how to gather data from several tables. My schema is as follows:

Code:

events
  event_id
  event_type_id
  event_name

event_types
  event_type_id
  event_type_name

What I am trying to do is create an html table that has the event_name and event_type_name for all the rows in the events table. Can someone please steer me in the right direction?

Thanks,

Jesse

[eluser]Dam1an[/eluser]
You didnlt state if you're using AR or plain SQL, so I'll assume the former

Code:

$this->db->join('event_types', 'events.event_type_id = event_types.event_type_id');
$query = $this->db->get('events');

You can obvioulsy add any other parts to the query, just as selects and aliases, where clauses etc

Hopefully that code makes sense, if not, have a read up on joins, and if still not, then post back and I'll try my best to explain it

[eluser]Jesse Schutt[/eluser]
Thanks Dam1an!

I'll give this a shot and let you know how it goes.

Jesse

PS - I love the CI community... post a question and have an educated response in less than 10 minutes! +1 Dam1an

[eluser]Jesse Schutt[/eluser]
It worked perfectly!

[eluser]Dam1an[/eluser]
Excellent You're welcome

[eluser]daparky[/eluser]
Sorry to hijack the thread, we are all learning CodeIgniter (well the new ones are ) so i thought i'd post here rather than make a new thread.

So from the code you posted:

Code:

$this->db->join('event_types', 'events.event_type_id = event_types.event_type_id');
$query = $this->db->get('events');

This is basically saying join event_types with events where event_type_id matches in both tables?

What will the above output and what is this in pure SQL?

Thanks!

[eluser]jedd[/eluser]
[quote author="daparky" date="1252986983"]
... and what is this in pure SQL?
[/quote]

The best way of finding this out is to use the [url="http://ellislab.com/codeigniter/user-guide/libraries/output.html"]CI Profiler[/url] - which will show you the actual SQL it generates.