Suggestions for an Attendance Tracking application?

Hi Greg,

Thanks for sticking with this one.

I've been trying to simplify the sign-in process, so adding a second level for the attendee to deal with would only complicate matters.

However, It would be OK if the one simple form had to go through a second page before the sign-in was confirmed (as long as it ended up on the original sign in form for the next attendee).

The searching of attendance records done by admins after the fact for statistical purposes and for email list generation.

WHERE I AM RIGHT NOW...

The salon_name field is a multi value check box  list  field that pulls it's possible values from records in another database (option values from the ‘num’ field and option labels from the ‘title’ field).

The sign in form for the AWC series update the values of salon_name with:  

$query = "UPDATE `{$TABLE_PREFIX}salon_listings` SET
salon_name = '1',

('1' is the record number associated with the salon_name “AWC”)

Unfortunately, this replaces the existing value for salon_name with the new one, instead of adding the new value to the multi-value list.

WHERE I NEED TO BE...

I need to add the new value to the salon_name field (if it doesn’t already exist).

Also, I would rather specify the value to be added as an alpha value, instead of the numeric value (the record number), so I guess that there would need to be some way of translating the numerical value expected into the alpha value (title field) for that value.

Thanks,

Jerry Kornbluth

As promised, here’s a working solution to the “Attendance Tracking” system for 2 concurrent series of lectures.

It uses 3 editors.
A multi-record editor called “Attendance Listings”, with the following fields.

First Name (text field)
Last Name (text field)
Email Address (text field)
Lecture Name (list field values pulled from the Lecture Names database)
Attended (text field populated with the month, year and lecture code of the last attended lecture)

A multi record editor called Lecture Names with only one field. (In my case there are 4 records; Lecture 1 (record # 1), Lecture 2 (record # 2), Category 1 (record # 3),  and Category 2 (record # 4)

And a single record editor called “Attendee Messages”, with text fields called welcome_email_message_1 and welcome_email_message_2

THEORY

When an attendee fills out and submits the sign in form with either their Attendee ID number (record number) for express sign in, or their first name, last name and a valid email address, the “Attendance Listings” database is checked for a matching ID number or a matching email address.

If neither match is found, a new record is created with first_name, last_name and email_address field values, the “Lecture 1" check box is checked in the lecture_name field and the current month, year and lecture code are inserted into the “Attended” field. In addition, a welcome email is sent to the attendee with their Attendee ID number.

If a matching Attendee ID number is found the record is updated by appending the current month, year and lecture code to the “Attended” field and, if required, the appropriate additional lecture name check box is checked.

If a matching email address is found and there are no other errors, the record is updated by updating the first and last name, and where necessary, appending the current month, year and lecture code  to the “Attended” field. If required, the appropriate additional lecture name check box is checked..

A report viewer allows series administrators to generate email lists for all those who attended a particular lecture series, or a particular lecture or who belong to a specific category.  The lecture date queries are automatically generated so that their format is consistent.

For simplicity, I decided to create a separate sign in form for each lecture series (one set is attached as an example)

You can download the sign in form for the Lecture 1 series, the report viewer, and the php.ini files for the 3 editors involved from:

http://www.thecmsbcookbook.com/downloads/attendance.zip

I’ve sanitized the viewers and .ini files to be more generic, so if you come across any issues, post what you find and I’ll see if I can resolve them.

GOING FURTHER
There are still 2 issues to resolve:

1) Modifying the code so that any number of lectures can be automatically added and tracked. (I don’t mind generating a separate viewer for each new lecture, but I’d like to do that automatically as well).

2) Generating the welcome email in a more dependable way (right now I’m getting around the fact that a new record needs to be generated before its record number can be captured, by delaying the code for .5 seconds with a usleep(500000); command).

Any help would be appreciated.

Hope this proves useful.

Jerry Kornbluth