Cross Reference Table

[eluser]dv310p3r[/eluser]
Hey all. I'm just starting out with Codeigniter and so far I really like it.

Anyways, here's my question. I have three tables: athletes, guardians, and xref_athgrd.

The athletes table hold athletes, the guardians table holds guardians and the xref tables holds the cross reference so that I can have a many to many relationship. Essentially one athlete can have many guardians, one guardian can have many athletes.

So I want to create a view that will display the athlete in one row, followed by any guardians the athlete has directly below it in other rows. I've been racking my brain for at least two days now and I can't seem to figure out how to do it. The best I've been able to come up with in my brain is that I need to somehow create in the controller an array or object that will hold all the possibilities prior to sending it to the view.

This is how I'd like it to look:

Id Name
05 Ima Athlete01
Athletes MomGuardian01 555-555-5555 [email protected]
Ahtletes DadGuardian02 555-553-5353 emaildad@emailcom
06 Mee Athletealso
Mees Momguaridan 444-444-4444 [email protected]
Mees Dadsimpotent 444-333-4343 [email protected]


Any help would be great.

[eluser]slowgary[/eluser]
Welcome to the CodeIgniter forums. Nice examples.

First, obviously, you need to get the data. I usually use active record, so in my model I'd have a function like so:

Code:

//model code
class Athletes_model extends Model
{
     function get_all()
     {
          return $this->db
               ->join('xref_athgrd', 'xref_athgrd.athlete_id = athlete.id')
               ->join('guardians', 'guardians.id = xref_athgrd.guardian_id')
               ->order_by('athletes.last_name')
               ->order_by('athletes.first_name')
               ->get('athletes')
               ->result_array();
     }
}

Then in your controller:

Code:

class Athletes extends Controller
{
     function index()  //this would be accessed by visiting http://www.yoursite.com/athletes/
     {
          $this->load->model('athletes_model', 'athletes');
          $data['athletes'] = $this->athletes->get_all();

          $this->load->view('athletes', $data);
     }
}

Then your view:

Code:

// because MySQL returns denormalized data, you'll need to sift through each loop and check if the athlete is the same or new
<?php $temp = false; ?>
<?php foreach($athletes as $athlete): ?>
     <?php if($temp != $athlete['athlete_id']): ?>
          <?php $temp = $athlete['athlete_id']; ?>
          <h2>&lt;?php echo $athlete['last_name'].', '.$athlete['first_name']; ?&gt;</h2>
          &lt;?php echo $athlete['guardian_name']; ?&gt;<br/>
     &lt;?php else: ?&gt;
          &lt;?php echo $athlete['guardian_name']; ?&gt;<br/>
     &lt;?php endif; ?&gt;
&lt;?php endforeach; ?&gt;

Something like that anyways. I hope this helps.

[eluser]dv310p3r[/eluser]
your reply is awesome and while I am working on getting it to work. I was hoping you could provide sort of an explanation as to what's taking place?

Thanks a million either way.

[eluser]dv310p3r[/eluser]
By the way, it's working. There were a few glitches I worked out but it's working! WOOO HOOO.

Thanks a million.

[eluser]slowgary[/eluser]
No problem. CodeIgniter is an MVC framework, so always think in terms of MVC... Model, View, Controller. Controllers request data from models or from $_POST or whatever (possibly manipulating the data), then call some views. Views have links back to controllers, and the cycle continues.

When you find trouble, always check the User Guide first. The only thing better than CodeIgniter's community is its User Guide. The user guide will give you a much better sense of how to do things in CodeIgniter. As long as you'll be using CodeIgniter, you should stop right now and first read the "Introduction" and "General Topics" sections of the user guide. They will be your biggest help.

Good luck.