Need help with data arrays

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.