Active Record Help? - Printable Version

Active Record Help? - 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: Active Record Help? (/showthread.php?tid=11144)

Active Record Help? - El Forum - 08-27-2008

[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

Active Record Help? - El Forum - 08-27-2008

[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:
&lt;?php if($query->num_rows() > 0): ?&gt;

    &lt;?php foreach($query->result() as $row): ?&gt;

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

Active Record Help? - El Forum - 08-27-2008

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

Active Record Help? - El Forum - 08-27-2008

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

Active Record Help? - El Forum - 08-27-2008

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

Active Record Help? - El Forum - 08-27-2008

[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);

Active Record Help? - El Forum - 08-28-2008

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