I have 2 tables. I am trying to combine the 2 tables, which I have been successful with join. Now I need to add up the values, but I don't want to repeat the same user when I display.
Example tables:
Table 1:
users
[ id ][name ]
[ 1 ][John Doe]
[ 2 ][Jane Doe]
[ 3 ][Joe Doe]
Table 2:
activity_hours
[ id ][user_id][hours]
[ 1 ][ 1 ][ 3 ]
[ 1 ][ 2 ][ 1 ]
[ 1 ][ 3 ][ 4 ]
[ 1 ][ 1 ][ 2 ]
[ 1 ][ 2 ][ 3 ]
I want to add up all the hours for each user from activity hours, but want to match the amount to their name
Here is my current code: Hours_model.php
$this->db->select('
users.id as id,
users.name,
activity_hours.hours
');
$this->db->from('users');
$this->db->join('activity_hours', 'users.id = activity_hours.user_id');
$query = $this->db->get();
return $query->result();
hours_veiw.php
foreach ($result as $activity) {
echo $activity->name.' : '.$activity->hours;
}
Desired output:
John Doe : 5
Jane Doe : 4
Joe Doe : 4
Use Group_by on users.id
$this->db->select('
users.id as id,
users.name,
sum(activity_hours.hours) as hours
');
$this->db->from('users');
$this->db->join('activity_hours', 'users.id = activity_hours.user_id');
$this->db->group_by('users.id');
$query = $this->db->get();
return $query->result();