Mike Mike - 7 months ago 21
PHP Question

codeigniter need help grouping and sum of tables

I need some help. I am new to codeigniter, so bear with me.

Here is my situation. 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 dont 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 there 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


Thanks

== UPDATE ==

Thank you user3774708. That little bit that you provided I was able to solve the rest.
Here is the rest of the code that I needed to change:
I changed:

activity_hours.hours


to

sum(activity_hours.hours) as hours


final database query

$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();

Answer

Use Group_by on users.id

$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');
$this->db->group_by('users.id');
$query = $this->db->get();
return $query->result();
Comments