Newb - M2M Relationship, Adding all records

[jmills]

I have searched and searched, but I don't know if I just have the wrong terminology, or if I am taking the wrong approach. The idea behind this part of my application is a simple attendance system. I have the following tables:

members
mem_id
first
last
status
...

event
event_id
title
date
description


memevent (m2m table)
mem_id
event_id
attended (boolean)
excused (boolean)

When I create a new event I would like to have a list of all members with an active status so I can easily go down through the list and check attendance. I need to be able to track if a member attended, was excused or was absent. I have gotten to the point where I could have a checkbox for each member to check off if they are absent or present, but I can't have a third option. Any suggestions, ideas...

[shannah]

You could do a one-to-many relationship just to your join table. Use a lookup field on the member_id field. Then use the grid widget on the events table to edit that relationship. It's not exactly what you're asking for, but it would allow you to add multiple members and edit the other fields of the join table.

-Steve

[jmills]

Is there a way to add a record to the join table for every record in the member table where the 'status' is active. Then this action could be performed every time a new event is created, or possibly by a button somewhere.

[shannah]

You could put this in the afterInsert() trigger on the event table just using an SQL query statement. Then you would be able to make changes on the edit form after the record is saved the first time.

-Steve

[jmills]

Thanks for the guidance. I managed to get it working with this:

Code: Select all

    function afterInsert(&$record){
   $event_ID = $record->val('event_ID');
        $app =& Dataface_Application::getInstance();
   $db = $app->db(); // database resource handle...

   $res = mysql_query("INSERT INTO memberevent (event_ID, mem_ID)
   SELECT DISTINCT '$event_ID', mem_ID FROM member WHERE status='ACT';", $db);
    }