Adobe Captivate 4: Storing Quiz Scores with PHP/MySQL

[This article describes Cp2DB v1. A newer version of this software is available, see for details. Among other things, the latest version captures both “core data” (i.e. summary data) and “interaction data” (i.e. responses to each question).]

At the Adobe Developer Connection, Tim Mushen has a good article about storing Adobe Captivate quiz data in a server-side Microsoft Access database using ColdFusion.  I decided to see if I could rework his concept using PHP and MySQL. I was happy with the results, so I’m sharing them with you. I’m using Captivate 4.  These techniques might work with earlier versions, but I’m not sure that they will.

Tim’s idea is simple but effective, he rewrites Captivate’s sendMail() function, which is contained in the HTML page that Captivate creates when you publish your project for the web.  The published version of a Captivate 4 project generally contains three files:  a SWF, an HTML file that embeds that SWF, and a JavaScript file (standard.js) which defines a few functions.

If you enable reporting (Quiz → Quiz Preferences → Enable reporting for this project) and choose email as the reporting method, three function that are not otherwise available will be included in the HTML file when you publish:  appendEmailBody(), sendMail(), and padMail().

The sendMail() function uses a few lines of JavaScript to send the quiz results as an email.  Tim’s workaround is to overwrite this sendMail() function with one that will, instead of sending email, pop a new HTML window with a form containing the quiz results data, two fields to capture the user’s name and email address, and a submit button to send it off to the server.  Once submitted, the form calls a ColdFusion file named insert.cfm, which handles the database connectivity and the insert statements, storing the data in a server-side Microsoft Access database (*.mdb).

But I’m a PHP guy.  I don’t have access to a ColdFusion server, so I wrote my own take on insert.cfm, calling mine insert.php and writing to a MySQL database instead.  And, so I could show that it works, I created an additional file called display.php which pulls all of the data back out and echoes it to the screen.

Here are the steps to make it happen:

  1. Download and unzip Tim’s demo files.  Keep newSendMail.txt.  Pitch the rest.
  2. Download and unzip my demo files.
  3. Create a quiz in Captivate 4.  Use the Create Project → From Template option on the spash screen and choose one of the quiz templates.  They live in My Documents\My Adobe Captivate Projects\Templates\Project Templates\Quiz.
  4. Chose Quiz -> Quiz Preferences.  Be sure that “Enable reporting for this project” is checked.  Choose “email” as the reporting method and specify your email address.
  5. Create a few quiz questions (by copying the demo slides andy modifying them).  Delete any extraneous slides.  Save and publish your project.  Be sure “Export HTML” is checked in the output options.  If you save your project as foo.cp, the HTML file will be named foo.htm.
  6. Open newSendMail.txt and foo.htm in a text editor. Find the sendMail() function in foo.htm and replace it with the one from newSendMail.txt.
  7. Do a find/replace on foo.htm and change the one instance of “input.cfm” to “input.php”.
  8. Create a MySQL database called “captivatequizdb.”  Create a table called “quizdata.”  Here’s the SQL:
  9. create table quizdata(
    id int not null auto_increment,
    primary key(id),
    total varchar(30),
    correct varchar(30), 
    accuracy varchar(30),  
    name varchar(30),
    email varchar(30) 
  10. Modify the connect strings in insert.php and display.php, adding your database host name, user name, and password.  You’ll find this on line 6 of each file.
  11. Upload your Captivate output files (foo.htm, foo.swf, and standard.js) along with insert.php and display.php to your web server and test your quiz.  Once you’ve submitted some data, load display.php to pull it back out.

A quick disclaimer:  This is proof-of-concept code.  I hope it works for you, but I don’t warrant that it will.  If you’re going to use it in a production environment, you should let a real code monkey take a look at it and make sure it’s ready for prime time.  I’ve done a little to sanitize inputs, but I’m sure the security of the code could be beefed up.  If the destination is some corporate intranet, you’re probably fine, but it’s not my problem if something goes south.

If I tinker with this any more, the next step will be to password-protect the display.php page and beef up the output with some sorting, so it’s not just a simple listing.  I’d also want to add some email validation code and other protections on the front end and pretty up the feedback window that is displayed to the user.