Having Trouble Linking Values of 2 Functions

[eluser]Jay Logan[/eluser]
I am trying to show a table with a team name in column 1 and the team points in column 2. I can acheive showing the names in column 1. But when I attempt to show the points in column 2, it only shows the points for the 1st team on the list and then gives those points to each team in the list. So the same point value is shown for all teams. Here the the code I'm using. I figured the problem lies in the controller somewhere but I'm such a noob.

MODEL

Code:

function get_team_names($id){
        $sql = "SELECT DISTINCT teamname FROM ievents WHERE meet_id = ?";
        $q = $this->db->query($sql,array($id));
        return $q->result_array();
    }

    function get_team_points($id, $teamname) {
          $this->db->select_sum('teampoints','points');
          $this->db->from('ievents');
          $this->db->where('meet_id',$id);
          $this->db->where('teamname',$teamname);
          $query = $this->db->get();
          return $query->row()->points;
    }

VIEW

Code:

<table class="table" border="0" cellpadding="0" cellspacing="0">
    <tr>
        <th colspan="2">Team Scores</th>
    </tr>

&lt;? foreach ($teams as $team): ?&gt;
<tr>
<td>&lt;? echo $team['teamname'] ?&gt;</td><td>&lt;? echo $teampoints ?&gt;</td>
</tr>
&lt;? endforeach; ?&gt;
</table>

CONTROLLER

Code:

$teams = $this->results_model->get_team_names($id);
        foreach ($teams as $team){
        $teampoints = $this->results_model->get_team_points($id, $team['teamname']);
        }
        $this->set('teampoints',$teampoints);
        $this->set('teams',$teams);

Please help!

[eluser]jcopling[/eluser]
It looks like you are almost there. It appears that the problem is that you are overwriting the value of $teampoints for each team. And then, you are only outputting the 1 value for $teampoints.

There are a number of ways you can fix this. I will demonstrate one here:

CONTROLLER

Code:

$teams = $this->results_model->get_team_names($id);
$teampoints = array();
foreach ($teams as $team){
    // by adding the [] we are telling it to add the new value to the end of an array
    $teampoints[] = $this->results_model->get_team_points($id, $team['teamname']);
}
$this->set('teampoints',$teampoints);
$this->set('teams',$teams);

VIEW

Code:

<table class="table" border="0" cellpadding="0" cellspacing="0">
    <tr>
        <th colspan="2">Team Scores</th>
    </tr>
&lt;? $i=0;?&gt;
&lt;? foreach ($teams as $team): ?&gt;
<tr>
<td>&lt;? echo $team['teamname'] ?&gt;</td><td>&lt;? echo $teampoints[$i]; ?&gt;</td>
</tr>
&lt;? $i++;?&gt;
&lt;? endforeach; ?&gt;
</table>

Hope this helps.

[eluser]Jay Logan[/eluser]
Wow. That did the trick. Now to learn more about this. I get that the brackets in the controller tell the view to give each array a separate variable. And in the view, you define what the difference is - in this case, the first variable will be $teampoints[0]. So that $i++ before the end of the loop just means to add 1 to the value for each item, right? Giving each item the variable $teampoints[0], $teampoints[1], $teampoints[2], etc. And I'm assuming $i is just what variable you chose to use, but anything could be used. Sorry for seeming such a noob. I'm trying.

[eluser]GSV Sleeper Service[/eluser]
you could save yourself a ton of calls to the DB by joining those two tables.

[eluser]Jay Logan[/eluser]
I'll have to research a way to do that. Gotta take baby steps. That's my next lesson. Lol.

[eluser]jcopling[/eluser]
J-Slim, the answer to all of your questions is yes. The $i variable is a commonly used variable name when used for nothing more than iteration.

I will agree with GSV, that by changing some of your database schema you could make things a bit easier, but given what you already have, this solutions should work for you.

[eluser]Jay Logan[/eluser]
Thanks for all your help. The new dilemma is trying to limit the amount of record SELECT SUM calculates. The this sport scores is the lowest 5 points for a team are calculated and whichever team has lowest score is winner. I have this in my model and it seems like it should work but it doesn't.

Code:

function get_men_points($id, $teamname) {
          $this->db->select_sum('teampoints','points');
          $this->db->from('ievents');
          $this->db->where('meet_id',$id);
          $this->db->where('teamname',$teamname);
          $this->db->where('eventgender','M');
        $this->db->order_by("teampoints", "asc");
        $this->db->limit(5);
          $query = $this->db->get();
          return $query->row()->points;
    }

Can anyone point me in the right direction?

[eluser]jcopling[/eluser]
How do you mean that it doesn't work? Are you receiving a Database error? Or is it simply not returning the expected result?

[eluser]Jay Logan[/eluser]
Not returning the expected result. I've been messing with some MySQL in phpMyAdmin to see what statement works and came up with this:

Code:

SELECT SUM(teampoints) FROM (SELECT teampoints from ievents WHERE meet_id = ? AND teamname = ? AND eventgender = 'F' AND teampoints != 0 ORDER BY teampoints LIMIT 5)

This returns the correct result. Now, to try it work in CI is another story.

[eluser]jcopling[/eluser]
Yeah, CodeIgniter does not have any built in way to run sub-queries but there is nothing wrong with taking exactly what you have and using the query function:

Code:

$query = $this->db->query("SELECT SUM(teampoints) FROM (SELECT teampoints from ievents WHERE meet_id = ? AND teamname = ? AND eventgender = 'F' AND teampoints != 0 ORDER BY teampoints LIMIT 5)");