CodeIgniter Forums

Full Version: Active Record Help?
You're currently viewing a stripped down version of our content. View the full version with proper formatting.

El Forum

[eluser]ChangedNames[/eluser]
Hi. I'm new to the MVC approach and I'm trying to get the gist of how this should be structured.

I have a podcast episode database that consists of 3 tables. One table, "episodes", has the bulk of the data. Two other tables, "author" and "site", are used relationally within "episodes". An example would be the first row of "episodes" has it's own unique ID, then unique ID of the "author" and the unique ID of the "site" (site referring to location - not website). I'm working on a back-end management side to this site and can't figure out how this should work with MVC in mind.

I have a controller, dashboard, that has a function, episode. when browsing to /dashboard/episode/1 the information from the episode with the unique ID "1" is being displayed. Currently the author ID and site ID are shown ("2", and "4" respectively). The tricky part is then communicating with table "authors" and "sites" to get the 'name' value from the corresponding rows ('name' is a column in each table respective to the data).

How do I go about retrieving this type of information from the database? I don't really need a line by line code that works - I like to figure things out for myself. Perhaps if someone could just nudge me in the right direction I'd really appreciate it.

Thanks Smile

El Forum

[eluser]ChangedNames[/eluser]
It may also be useful to mention that currently I'm just pulling the data in the dashboard controller like so:
Code:
$this->db->where('id',$this->uri->segment(3));
$data['query'] = $this->db->get('episodes');
And then displaying it in my view like so:
Code:
<?php if($query->num_rows() > 0): ?>
    <?php foreach($query->result() as $row): ?>
    
<ul>
    &lt;?php foreach ($row as $key => $value): ?&gt;
        <li><strong>&lt;?=$key?&gt;</strong>: &lt;?=$value?&gt;</li>
    &lt;?php endforeach ?&gt;
</ul>
&lt;?php endforeach ?&gt;
&lt;?php endif; ?&gt;

I'm assuming this will need to change in order to achieve my goal.

El Forum

[eluser]Michael Wales[/eluser]
This prob. doesn't fit your specific schema but it should get you started.

Code:
function episode($id) {
  $this->db->join('authors', 'authors.id = episodes.author_id');
  $this->db->join('sites', 'site.id = episodes.site_id');
  $this->db->select('episodes.*, authors.name as author_name, sites.name as site_name');
  $query = $this->db->get_where('episodes', array('id'=>$id), 1, 0);
  if ($query->num_rows() == 1) {
    $this->data->episode = $query->row();
  } else {
    // We have an invalid episode ID, redirect somewhere
    redirect('');
    return;
  }
}

Code:
&lt;body&gt;
<p>Episode name: &lt;?php echo $episode->name; ?&gt;</p>
<p>Author: &lt;?php echo $episode->author_name; ?&gt;</p>
<p>Site Name: &lt;?php echo $episode->site_name; ?&gt;</p>
&lt;/body&gt;

El Forum

[eluser]ChangedNames[/eluser]
How am I passing the result to the view? I have this in place currently:
Code:
$this->load->view('episode_view',$data);
Would I then need to change that to something else? I tried working with what you put above and kept ending up with the following error:
Quote:A PHP Error was encountered
Severity: Notice
Message: Undefined variable: episode
Filename: views/episode_view.php
Line Number: 9
Line 9 simply reads:
Code:
<p>Author: &lt;?php echo $episode->author_name; ?&gt;</p>

El Forum

[eluser]ChangedNames[/eluser]
Did you edit your post? I just looked at your code example again and it looks a little different...catch something?

El Forum

[eluser]Michael Wales[/eluser]
I did a quick edit - nothing more than using get_where rather than get(), personal preference.

To call your view:
Code:
$this->load->view('episode_view', $this->data);

El Forum

[eluser]ChangedNames[/eluser]
Oh I see. Thanks for the great help and start.

In your opinion would I be better off handling data requests like this via a model?