A lot of people liked my initial effort at creating a PHP/MySQL reporting solution for quizzes created with Adobe Captivate 4. The holiday break gave me a little extra time to work on the project, so I’m happy to release a new version. I even created a separate page for the project, but I’m still be using the blog to keep you informed about it. (Based on very limited testing, this code seems to work for quizzes created in Captivate 3 as well.)

Relational database structure. Version 1 captured everything in one table.

Debug mode. Allows you to see the contents of the array pre- and post-submission.

Core data is aggregate data for the entire quiz. It includes values for the user’s overall score, along with the maximum possible score, the minimum possible score, the total time the user spent on the quiz, and whether he or she passed the quiz (based on a pass/fail percentage you define in Captivate’s quiz preferences).

Interaction data is data specific to each question/interaction in the quiz. It includes the user’s answer, the correct answer, the result (i.e. was the user’s answer correct or not), the time spent on that question, the interaction type (e.g. T/F, multiple-choice, etc.), and some other metadata specific to the question.

In order to store all of this in a useful way, I’ve expanded the database a bit. It now has three tables (one for core data, one for interaction data, and one for person data). I’ve expanded the score submission form to include an optional employee ID, but, behind the scenes, I will be using the user-submitted email to uniquely identify the user. This provides a way to connect user data to quiz data without resorting to a login/authentication scheme of some sort.

Here are the steps for setting it up:

Create a quiz in Captivate 4.
(I’ve included a sample project: cp2db_v2_rc3.cp.)

Choose Quiz –> Quiz Preferences.
Under the Reporting category:
Select the “Enable reporting for this project” checkbox.
Select the “E-mail” radio button and specify an email address.
Under “Report to LMS as,” select the “Score” radio button.
Under “Reporting Level,” select “Interactions and score.”
Under the Pass or Fail category, set a pass/fail percentage.
Click the OK button to confirm your preferences.

Save and publish your project as a Flash (SWF). Be sure “Export HTML” is checked in the publish options. If you save your project as foo.cp, the HTML file will be named foo.htm.

Open sendmail_v2.js in your favorite text editor. Select all and copy the entire file to your clipboard.

Locate and select the sendMail() function in foo.htm. It should occupy lines 27-42.
(If you are using the sample project, the code has already been pasted into cp2db_v2_rc3.htm, so you can skip this step.)

Use paste to replace the default sendMail() function with the new-and-improved one on your clipboard.

Save your changes to foo.htm.

Create a MySQL database called cp2db. It will contain three tables: tblcore, tblinteraction, and tblperson. You will find the SQL for creating the tables and fields in db_schemas.sql.

Upload your Captivate oputput files (i.e. foo.htm, foo.swf, and standard.js), insert_v4.php, and display_v4.php to your web server and test your quiz. Once you have submitted some data, you can use display_v4.php to pull it back out.

Once you get it up and running, take a look at the code in insert_v4.php and the new sendMail() function. There’s a setting for turning the new debug mode on and off. (You’ll want it on for testing, of course.)

Feedback is welcome. Use the comment thread of this post for your bug reports, feature requests, and other feedback.

Known Issues/Limitations

For now, Cp2DB only handles the multiple-choice question type. If there’s sufficient interest, I’ll work on adding other interaction types in future releases.

As far as I know, it only works with Captivate 4. I haven’t tested it with earlier versions.

The script for echoing out the database data is still in its fledgeling stages. A fully-functional back-end remains to be written. I’ll work on that for the next version.

Standard Disclaimer

As before, this code is beta at best. There are no warranties expressed or implied, and you should have someone with better coding chops than me look it over before you put it into production–especially out in the wild. I hope you enjoy it, find it useful, and can leverage it to solve your own problems. But if it blows anything up, that’s on you.