Need help with data arrays

Hi,

Got two tables, Staff and Events. For a view, I need to get all the Staff records and, for each record, get a SUM of all the related (by Staffid) amount fields in Events.

What I'd like is to have all the data from each record in Staff and the SUM total in the same array ...I just can't figure out a way to do that.

Here's what I've got so far ... get all the Staff data and do a foreach loop to call a method to get the SUM total... but how to marry the two together???

Controller:

PHP Code:

public function list_all_staff()
 {
 $this->data['data'] = $this->M_staff->get_all();
 foreach ($this->data['data'] as $item)
 {
 $sum_amount=$this->M_events->count_events($item['staffid']);
 }
 $this->data['title'] = 'Work with Staff';
 $this->data['main'] = 'v_staff_list';
 $this->load->view('v_template', $this->data, 'refresh'); 
 } 


Model method to get sum:

PHP Code:

function count_events($par_id)
 { 
 $this->db->select('SUM(events.amount) as total');
 $this->db->where('staffid', $par_id); 
 $this->db->where('date >=', $this->session->userdata('start'));
 $this->db->where('date <=', $this->session->userdata('end')); 
 $this->db->where('typeid', 1);
 $this->db->group_by('typeid'); 
 $query = $this->db->get('events');
 $ret = $query->row();
 } 


Got two tables, Staff and Events. For a view, I need to get all the Staff records and, for each record, get a SUM of all the related (by Staffid) amount fields in Events.

Getting the parent record and then looping through the child records is indeed how you would do it without the benefit of an SQL-type database. Fortunately, with an SQL-type database, we can do that all in one query. The trick is to join the two tables, and use the SUM() function and the GROUP BY clause to get a summary.

Here's an example. It's not cut-and-paste code, but it should illustrate what I mean. Suppose you have two tables, parents and children. In children, each record is somebody's child, and the children.parents field is the foreign key that points to which parent is that child's parent. The child table also has how many toys each child has. So, the question is, how may toys has each parent bought for the children?

Code:

table parents
parents.id
parents.name

table children
children.id
children.parents
children.name
children.toys

In MySQL you would have something like this.

Code:

SELECT
   parents.id as p_id,
   parents.name as p_name,
   SUM(children.toys) as total_num_of_toys
FROM parents
INNER JOIN children
ON parents.id = children.parents_id
GROUP BY parents.id;

In the above query, the INNER JOIN ties the two tables together according to the ON match, but the intermediate result gives us a table with one row for every child, and we really want one row for every parent. That's what the GROUP BY clause does. 

The output will be a table more or less like this.

Code:

p_id    p_name         total_num_of_toys
====    ==========     =============
32      Smith, John         3
75      Doe, Mary           7
99      Chang, Tony        11

And in Codeigniter, you can return the query in an array. I do not use the DB helper functions. I just use $this->db->query('and put the SQL query here');

Do you have phpMyAdmin or some kind of SQL interface that lets you enter queries? It's very useful to have one because you can then try this and that and see the results.

Hey, don't work without a PHP debugger. Several free IDEs have this features built in. Two are NetBeans and CodeLobster. Without a debugger, it's like you're driving with a blindfold on -- you are going to crash!

Got two tables, Staff and Events. For a view, I need to get all the Staff records and, for each record, get a SUM of all the related (by Staffid) amount fields in Events.

Getting the parent record and then looping through the child records is indeed how you would do it without the benefit of an SQL-type database. Fortunately, with an SQL-type database, we can do that all in one query. The trick is to join the two tables, and use the SUM() function and the GROUP BY clause to get a summary.

Here's an example. It's not cut-and-paste code, but it should illustrate what I mean. Suppose you have two tables, parents and children. In children, each record is somebody's child, and the children.parents field is the foreign key that points to which parent is that child's parent. The child table also has how many toys each child has. So, the question is, how may toys has each parent bought for the children?

Code:

table parents
parents.id
parents.name

table children
children.id
children.parents
children.name
children.toys

In MySQL you would have something like this.

Code:

SELECT
   parents.id as p_id,
   parents.name as p_name,
   SUM(children.toys) as total_num_of_toys
FROM parents
INNER JOIN children
ON parents.id = children.parents_id
GROUP BY parents.id;

In the above query, the INNER JOIN ties the two tables together according to the ON match, but the intermediate result gives us a table with one row for every child, and we really want one row for every parent. That's what the GROUP BY clause does. 

The output will be a table more or less like this.

Code:

p_id    p_name         total_num_of_toys
====    ==========     =============
32      Smith, John         3
75      Doe, Mary           7
99      Chang, Tony        11

And in Codeigniter, you can return the query in an array. I do not use the DB helper functions. I just use $this->db->query('and put the SQL query here');

Do you have phpMyAdmin or some kind of SQL interface that lets you enter queries? It's very useful to have one because you can then try this and that and see the results.

Thank you for your detailed reply. Your suggestion works perfectly for me.

Thank you for your detailed reply. Your suggestion works perfectly for me.

You're welcome! Glad to be of help.

Hey, don't work without a PHP debugger. Several free IDEs have this features built in. Two are NetBeans and CodeLobster. Without a debugger, it's like you're driving with a blindfold on -- you are going to crash!

Sorry to be a pain but I've just realised that I need to SUM two differnet types of data from the 'children' table - eg

PHP Code:

table parents
parents.id
parents.name

table children
children.id
children.parents
children.name
children.toytype
children.toys 


If the children table had a toytype filed then I need to separately SUM children.toys where toytype =1 and another SUM for where toytype = 2

How can I do that? Is it possible in one query?

Thanks again.

Sorry to be a pain . . . 

If the children table had a toytype filed then I need to separately SUM children.toys where toytype =1 and another SUM for where toytype = 2

How can I do that? Is it possible in one query?

Hey, no problem. The forum is to ask questions. Anyway, yes, you can do that with one query. Suppose you have tables like this.

Code:

Table: parents
id  name
1  Smith, Joe
2  Doe, Mary
3  Chang, Tony
4  Martinez, R
5  Kramer, Helen
6  Green, Robert

Table: children
id  parents  name     toytype  toycount
1     1     Kimmie      1        3
2     1     Kimmie      2        4
3     1     Josh        1        1
4     1     Josh        2        7
5     1     Betty       1        3
6     2     Jacob       1        4
7     2     Jacob       1        1
8     2     Marnie      2       17
9     2     Taylor      1        8
10     2     Taylor      2        3
11     3     Martin      1        3
12     3     Martin      2        8
13     3     Tony        1        0
14     3     Tony        2        0
15     4     Hailey      1       13
16     6     Barbara     1        3
17     6     Freddie     1        1
18     6     Freddie     2        2
19     6     Suzy        1        7
20     6     Sophie      2        8

Here's the query.

Code:

SELECT
  parents.id AS id,
  parents.name AS name,
  children.toytype AS ttype,
  SUM(children.toycount) AS numtoys
FROM parents
INNER JOIN children
ON parents.id = children.parents
GROUP BY ttype, id
ORDER BY id, ttype

The key is the GROUP BY clause. It affects how the SUM() is done. If you just grouped by ttype, you'd get two lines, one with the grand total of all type 1 toys, and the other with the grand total of all type 2 toys. If you just grouped by id (the parent's id), you'd get a line for each parent with the total sum of toys of both types. When you group it first by ttype and then by id, however, you get this.

Code:

id  name           ttype  numtoys
1  Smith, Joe       1       7
1  Smith, Joe       2      11
2  Doe, Mary        1      13
2  Doe, Mary        2      20
3  Chang, Tony      1       3
3  Chang, Tony      2       8
4  Martinez, R      1      13
6  Green, Robert    1      11
6  Green, Robert    2      10

The ORDER BY clause sorts the results to make sure the rows are sorted by parent id. You could also order by parents.name or any other field.

What is your MySQL setup? I really suggest you get phpMyAdmin, which lets you test different queries. There's an actual free desktop application out there called MySQL Admin, but it is very sluggish, and I found it hard to understand.
Consider also getting an SQL book. I like "Simply SQL," by Rudy Limeback. It's $9.99 on Kindle, and you can also get it used. The book really helped me understand joins.

Hey, don't work without a PHP debugger. Several free IDEs have this features built in. Two are NetBeans and CodeLobster. Without a debugger, it's like you're driving with a blindfold on -- you are going to crash!

Sorry to be a pain . . . 

If the children table had a toytype filed then I need to separately SUM children.toys where toytype =1 and another SUM for where toytype = 2

How can I do that? Is it possible in one query?

Hey, no problem. The forum is to ask questions. Anyway, yes, you can do that with one query. Suppose you have tables like this.

Code:

Table: parents
id  name
1  Smith, Joe
2  Doe, Mary
3  Chang, Tony
4  Martinez, R
5  Kramer, Helen
6  Green, Robert

Table: children
id  parents  name     toytype  toycount
1     1     Kimmie      1        3
2     1     Kimmie      2        4
3     1     Josh        1        1
4     1     Josh        2        7
5     1     Betty       1        3
6     2     Jacob       1        4
7     2     Jacob       1        1
8     2     Marnie      2       17
9     2     Taylor      1        8
10     2     Taylor      2        3
11     3     Martin      1        3
12     3     Martin      2        8
13     3     Tony        1        0
14     3     Tony        2        0
15     4     Hailey      1       13
16     6     Barbara     1        3
17     6     Freddie     1        1
18     6     Freddie     2        2
19     6     Suzy        1        7
20     6     Sophie      2        8

Here's the query.

Code:

SELECT
  parents.id AS id,
  parents.name AS name,
  children.toytype AS ttype,
  SUM(children.toycount) AS numtoys
FROM parents
INNER JOIN children
ON parents.id = children.parents
GROUP BY ttype, id
ORDER BY id, ttype

The key is the GROUP BY clause. It affects how the SUM() is done. If you just grouped by ttype, you'd get two lines, one with the grand total of all type 1 toys, and the other with the grand total of all type 2 toys. If you just grouped by id (the parent's id), you'd get a line for each parent with the total sum of toys of both types. When you group it first by ttype and then by id, however, you get this.

Code:

id  name           ttype  numtoys
1  Smith, Joe       1       7
1  Smith, Joe       2      11
2  Doe, Mary        1      13
2  Doe, Mary        2      20
3  Chang, Tony      1       3
3  Chang, Tony      2       8
4  Martinez, R      1      13
6  Green, Robert    1      11
6  Green, Robert    2      10

The ORDER BY clause sorts the results to make sure the rows are sorted by parent id. You could also order by parents.name or any other field.

What is your MySQL setup? I really suggest you get phpMyAdmin, which lets you test different queries. There's an actual free desktop application out there called MySQL Admin, but it is very sluggish, and I found it hard to understand.
Consider also getting an SQL book. I like "Simply SQL," by Rudy Limeback. It's $9.99 on Kindle, and you can also get it used. The book really helped me understand joins.

Thanks again for coming back and explaining clearly. I've got phpmyadmin and have been using it most of the day to try to get what I want from my database. I don't think I was very good at explaining the nature of the problem. I'm building a staff absence tracking application so  Ihave multiple staff and multiple absence 'types' the 'parent' table is staff and the 'child' table is events ... therecan be multiple different absence types in in my child table and I only need totals of two of them ... I also have to select events that occur in a date range. However, the good news for me is that I've come up with a solution (inspired by your previous post) and here it is:

Code:

$SQLstring="SELECT *
FROM staff t LEFT JOIN
(
  SELECT events.staffid, SUM(amount) total_absence
    FROM events
   WHERE events.typeid = 1 and events.date between '{$this->session->userdata('leave_start')}' and '{$this->session->userdata('leave_end')}'  
  GROUP BY events.staffid
) a
  ON t.staffid = a.staffid LEFT JOIN
(
  SELECT events.staffid, SUM(amount) total_sickness
    FROM events
   WHERE events.typeid = 2 and events.date between '{$this->session->userdata('leave_start')}' and '{$this->session->userdata('leave_end')}'    
  GROUP BY events.staffid
) s
  ON t.staffid = s.staffid
JOIN department ON t.deptid = department.deptid";

This gives me to total of event type 1 and the total of event type 2 between the relevant dates for each member of staff. The result set contains the staffid 3 times (once from the SELECT * and once each because of the select in the subqueries) and I don't know how to get rid of the two 'extra' ones. Also, if there are no records of the specified type in the date range for any staff member the the totals return as NULL ... is there a way to get the query to return 0 rather than NULL?

also, is this solution a good one or could I have done better?

Ah, I think I understand now. You want one row per staff member and the total for each absence type on the same row, like this?

Code:

id  name     absent  sick
01  Michael     1      2
02  Jennifer    3      0
03  Thomas      0      0
04  Carlos      0      1
05  Mary        2      1

blackbulldog:
Also, if there are no records of the specified type in the date range for any staff member the the totals return as NULL ... is there a way to get the query to return 0 rather than NULL?

I'm getting out of my depth here (my SQL isn't that good), LOL, but there's a function called COALESCE() that should do the trick. The COALESCE() function returns the first non-NULL value from a list of values. I think you'll have to use it on both fields.

Code:

instead of
SELECT events.staffid, SUM(amount) AS total_absence

try this
SELECT COALESCE(events.staffid, 0),
       COALESCE(SUM(amount) AS total_absence, 0)

Hey, don't work without a PHP debugger. Several free IDEs have this features built in. Two are NetBeans and CodeLobster. Without a debugger, it's like you're driving with a blindfold on -- you are going to crash!

This gives me to total of event type 1 and the total of event type 2 between the relevant dates for each member of staff. The result set contains the staffid 3 times (once from the SELECT * and once each because of the select in the subqueries) and I don't know how to get rid of the two 'extra' ones.

Oh, cool, that's easy! Instead of doing SELECT *, just name the fields that you want.

From your two subqueries, I think you only need the SUM() fields, a.total_absence and s.total_sickness.

also, is this solution a good one or could I have done better?

I played and played with it and couldn't find anything better. I think you got it! Don't forget the COALESCE() tip I posted below. I tested it, and it works.

That was a great question you posted! I learned from trying to find a solution!

Hey, don't work without a PHP debugger. Several free IDEs have this features built in. Two are NetBeans and CodeLobster. Without a debugger, it's like you're driving with a blindfold on -- you are going to crash!

From your two subqueries, I think you only need the SUM() fields, a.total_absence and s.total_sickness.

I've tried leaving out the events.eventid in the subquery select but that throws the following error:

Code:

Error Number: 1054

Unknown column 'events.staffid' in 'on clause'

SELECT * FROM staff t LEFT JOIN ( SELECT COALESCE(events.staffid, 0), COALESCE(SUM(amount),0) AS total_absence FROM events WHERE events.typeid = 1 and events.date between '2015-03-12' and '2016-03-11' GROUP BY events.staffid ) a ON t.staffid = events.staffid LEFT JOIN ( SELECT events.staffid, SUM(amount) total_sickness FROM events WHERE events.typeid = 2 and events.date between '2015-03-12' and '2016-03-11' GROUP BY events.staffid ) s ON t.staffid = s.staffid JOIN department ON t.deptid = department.deptid

so it seems to need the fields explicitly selected so it can do the join maybe? It's no big deal though, I can live with having the extra data in the result set.

Just another point to note ... I've found that specifying the COALESCE like this, with the closing bracket after the AS variable:

Code:

COALESCE(SUM(amount) AS total_absence, 0)

throws an error. Whilst specifying it like this (with closing brackets after the SUM(amount))

Code:

COALESCE(SUM(amount),0) AS total_absence

works just fine.

Thanks for your help, I learned a lot too. I'll pick up a copy of that book too!