Need Help Wih SELECT SUM()

[eluser]Jay Logan[/eluser]
Hello. I have a database which shows athlete names and athlete points for sporting events. All events are in 1 big table. And I want to tally up team points, based on the athletes `teamname` and for a specific `meet_id` to display. So far, I have successfully gotten the page to display all the teams that participated in the `meet_id`. I have been trying to show the team points next to the team name but it only shows the word "Array". Here is the code I'm using.

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){
        $sql = "SELECT SUM(teampoints) FROM ievents WHERE meet_id = ? AND teamname = ? ORDER BY teampoints ASC LIMIT 5";
        $q = $this->db->query($sql,array($id,$teamname));
        return $q->result();
    }

VIEW

Code:

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

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

CONTROLLER

Code:

function meet($id){
        $meeting = $this->results_model->get_header($this->host,$id);
        $this->set('meet_name', $meeting['meetname']);
        $this->set('meeting', $meeting);
        $evts = $this->results_model->get_events($id);
                $events = array();
        
        foreach ($evts as $event){
            $events[$event['eg']][$event['type']][$event['eventcode']]['name'] = $this->code2string($event['eventcode'],$event['type']);            
        }
        
        $this->set('events',$events);

        $teams = $this->results_model->get_team_names($id);
        $this->set('teams',$teams);

        $teampoints = $this->results_model->get_team_points($id, 'Lane College');
        $this->set('teampoints',$teampoints);

        $this->load_view('results/meet');
    }

Any help you can provide with this would be appreciated. I really want to learn from my mistakes.

[eluser]Yash[/eluser]
Try to use print_r then go for these foreach

If you can see value by print_f I recommend try for loop much easier to understand.

[eluser]Jay Logan[/eluser]
I replaced

Code:

return $q->result();

with

Code:

print_r ($q);

in my model and this showed at the top of the page:

CI_DB_mysql_result Object ( [conn_id] => Resource id #31 [result_id] => Resource id #51 [result_array] => Array ( ) [result_object] => Array ( ) [current_row] => 0 [num_rows] => 1 [row_data] => )

[eluser]Jay Logan[/eluser]
OK, I can get it to work with some good, old fashion PHP.

Code:

function get_team_points($id, $teamname){
        $sql = mysql_query("SELECT SUM(teampoints) AS points FROM ievents WHERE meet_id = '207' AND teamname = 'Ben Lippen HS'");
        $row = mysql_fetch_assoc($sql);
        return $row['points'];
    }

The problem with this is that I can't pass the variables $id and $teamname from the controller. Is there a CI version of the above code?

[eluser]ray73864[/eluser]
yeah, i recommend having a look at 'select_sum()', for a quick example (similar to the on you want:

Code:

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

I find it easier to construct a query using the 'Active Record class', as i can then have conditions in there such as is the user logged in, if so use this 'where' clause instead of that one, etc...

Ray

[eluser]Jay Logan[/eluser]
Nice. That works. But now I have to go back to the drawing board because I can't get the controller to read a variable named $teamname which will add the row's current team name.

Code:

$teampoints = $this->results_model->get_team_points($id, 'Lane College');

Where it says "Lane College" should be replaced with the current row's team. I'm stumped.