Adobe Captivate 4: Storing Quiz Scores with PHP/MySQL

[This article describes Cp2DB v1. A newer version of this software is available, see wheatblog.com/software 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) 
);
  9. 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.
  10. 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.

71 thoughts on “Adobe Captivate 4: Storing Quiz Scores with PHP/MySQL”

  1. There is also an article about Sending Captivate Quiz Data to JavaScript:
    http://pipwerks.com/journal/2008/09/07/send-captivate-quiz-data-to-javascript/

    I suggest that Captivate may offer the simple quiz report feature as samples like wondershare quizcreator does, which includes free ASP/PHP samples within application help to enable users to build Web database to receive a simple test report. http://www.sameshow.com/quiz-creator/quiz-creator-database.html#110

  2. Thanks Sarena. I found that pipwerks.com piece at one point, but Tim’s seemed more easily adaptable to my purposes. I think it would be great if Adobe offered the ability natively in their product (as they already do for some other reporting schemes).

  3. Thank you for posting this article. For those of us who are not using a subscription or expensive LMS, this gives us options for using our own databases created in MySQL. Could this process be applied to an open source LMS like Moodle or a CMS like Druple? (I’m just learning and trying to teach myself as much as possible. Thanks for giving back to the community!)

  4. CBTScheid, I’m glad you enjoyed the article. I’m glad to help out when I can. Moodle is SCORM compliant, so that would be the best route. Drupal is PHP/MySQL-based, so this could be rewritten as a Drupal module. I’m a fan of Drupal and use it on one of my sites, but I haven’t (yet) written any modules for it. That’s a good idea, though.

  5. If you end up writing a Drupal module, I would love any help or insights/pitfalls you might share. PHP and MySQL are new animals to me, but I’m fascinated by the potential of these open source projects. I was thrilled when I happened onto your site. With Captivate 4 new on the scene, it’s nice to see new solutions being developed along with the maturing of the product. Thanks again, and keep posting, it’s very appreciated.

  6. I’ve been using Captivate since version 2, but I’ve been really excited about version 4. I’ve converted all of my projects to it and have been doing a lot of new work in it. So I expect to have lots to share on the subject. Glad to have you as a reader. It’s good to meet another Captivate developer.

  7. Thanks for writing this. I’m very new to everything (teacher actually) but am in the process of moving to the online teaching world. I’ve been having way too many problems with Moodle handing Scorm data especially through Firefox and I just wanted something very simple.

  9. The demo below illustrates how you can collect user information before a aptivate 4 quiz, use the user information to customize question feedback messages, display user information on a quiz completion certificate, use JavaScript to display quiz results and other information, and give the participant the option to post their quiz results to a Microsoft Access database.

    You can test to demo here: http://www.myshbp.com/captivate/cap4demo.htm

  10. I’ll note that Issac’s solution (the comment above this one) appears to use Classic ASP for scripting (rather than PHP, as in my solution or ColdFusion, as in the solution that inspired my own). Many options, folks.

  11. Pingback: wheatblog » Blog Archive » Adobe Captivate 4 Aggregator Issue

  12. I am having trouble with step 7. I can’t find input.cfm within my foo.htm. Has anyone else had this issue? I created a quiz, and replaced the sendmail function, but now cannot locate an “input.cfm” within my .htm file. thoughts?

  13. You caught a typo of mine, Timothy. It’s not “input.cfm” it’s “insert.cfm.” That’s on line 24 of newSendMail.txt (from Tim Mushen demo files in Step 1, above). I’ll correct that in the origial post to avoid confusing anyone else.

  14. Could you clarify something for me. Are these examples just sending the score? Is it possible to send the actual responses for each question? Also Isaac Tabe’s link isn’t working. Is there any way to contact him to get the correct link?

  15. FrostyDew: this implementation only sends the score. Actually, it sends the raw score, the number correct, and the “accuracy,” which it computes. Those three are stored in the database. Several people have asked for a version that captures each interaction. I didn’t go that route because I don’t need it. But I might take a stab at it. I believe that data can be parsed out. The parsing of it out will be the tricky part, for me at least.

  17. Thanks. I want to get your version to work first. Then I’ll take a stab at getting the interactions too. For fun, I created a one-question quiz, published it and actually used the email reporting option. The reporting preferences I used include the results AND the interactions. When “took” the quiz, it generated an att file that contained the score and the answers but it looks like each interaction/question would generate a record in the table and it sends a bunch of stuff I don’t need. So, you’re right the parsing will be the challenge. In my situation, the real quiz has 100 questions so its a BIG challenge. Great blog!

  18. I am new to captivate. I have been struggling with this project where I need to email and save quiz results to database. I have followed all steps, but once I publish the senmail() stops responding. Please help.

  19. CAdeveloper: I’d need more information to try and help. If you send your files (wheat at wheatdesign dot com) I’ll be happy to test them. I probably should work up a functional demo that people can download and test. Based on the limited information you’ve provided, I’m not sure what’s wrong.

  20. I apologise for the short description. I am emailing the contents of my .html file,
    please take a look at it and advice.

  21. Keep this in mind: every time you publish, foo.htm is going to be overwritten. So you’ll have to either copy/paste in your hacked version, or keep a hacked copy of foo.htm that you use to overwrite the one that Captivate creates. There’s no way I’ve found yet–though I haven’t looked very hard, to be honest–to override Captivate’s default HTML template. This would explain why your custom sendMail() function quits working.

    You’ll want to either save customizing sendMail() for the last step in your design workflow (or, near the last). Each time you publish, Captivate will overwrite foo.htm, blowing away your customized sendMail() function.

  22. I apply the custom sendmail() each time I publish, it does not respond, I am not even sure if it is invoking that function(). I did send you the code, please check when you get a chance..Thanks for your help.

  24. Guys this works…Thanks so much.
    For the guys who think it dones not call the function..Please add the file to IIS and then run it ..It works Appreciate it guys for this great forum

    cheers

  25. Hi,

    Awesome website. Thanks so much. Does this work with Enterprise SQL? I am struggling big time with trying to track viewings and test scores…we do not use MySQL though…Thanks.

  26. This solution is MySQL specific, but PHP supports many databases as well as ODBC. So it could be modified to support other DB servers.

  27. I’m using Navicat to create mysql database and I can’t seem to figure out the primary key (id) line. Is the “(id)” suppose to be the type? I’m so new to sql trying to understand the language.
    Thanks, Hatch

  28. Hatch: in SQL, there are two ways of specifying the primary key (“id” is the name of the field that I’m using as the primary key). You can either do this:

    id int not null auto_increment primary_key

    Or you can do it in two lines, like I did above:

    id into not null auto_increment,
    primary key(id)

    So, “id” is the name of the field. It is of type “int” (i.e. integer), and it has three additional properties: it is “not null” (i.e. it must have a value), it is “auto_increment” (i.e. each new record in the DB will have an “id” value one higher than the last record entered), and it is the primary key for the table.

    I’m not familiar with Navicat. But I hope that clears it up enough that you can make it work using that tool. If Navicat has an SQL view (like phpMyAdmin does), then you can just paste my code in.

  29. Hi,
    I am able to extract quiz score into database table. How can I extract the quwstions the user attempted during the quiz. I could not find any variables that give quiz questions. I only found a variable for chosen answer. PL. help..Thanks

  30. Hi,
    I was wondering if anyone has any ideas on accessing the questions the user answered in the quiz. Pl. refer to my question from sep 4th.. Thanks

  31. CAdeveloper: It can be done. Depending upon the quiz setting options in Captivate, that data will be put into the output stream. The trouble is writing some code to parse it all out. Once it’s parsed out, writing the code to store it in the DB would be trivial.

    I’ve taken a cursory look at the output stream, but I haven’t figured out the logic of it enough to try parsing out data about each interaction/question in the quiz. When I created my implementation, I didn’t need that data. I just needed a cutoff score and an indication that it had been met. So my needs were fairly modest, and that’s reflected in the code.

    There’s been enough interest in this project that I’m thinking about putting some more work into it and see if I can expand it a bit to meet the needs of other who are in this I-don’t-have-an-LMS and/or I-don’t-want-an-LMS situation, as it seems there are quite a few of you. My time is consumed with other projects at present, but I’m going to put this one on deck and take a closer look at it when I finally get some breathing room.

  32. Thanks you. Could you please advice what settings I need to make to pull that info into the output stream. I have a working fuinction that parses data right now that I am using for parsing the scores. Would that setting also tell me what answer was chosen.

    Thanks

  33. Choose Quiz, then Quiz Preferences. Under the Reporting category, there’s a Reporting Level section with radio buttons for choosing between “Report score” and “Interactions and score.” You’ll want the second of these.

  34. Hi,
    I followed the instructions and modified my settings. But the email only shows the following

    Core Data
    “Status”,”Location”,”Raw Score”,”Max Score”,”Min Score”,”Time”
    “passed”,”3″,”20″,”20″,”0″,”00:00:16″

    I donot see the interaction data. Am I doing something wrong. Pl. advice.

  35. CAdeveloper: You’re doing something wrong, but I’m not sure what. You’re clearly getting the core data, but, after that (and after two carriage returns) you should get the interaction data as well.

    Are you parsing it with JavaScript? Or are you handing it off somehow to PHP and parsing it there? I’d love a look at your code, if you’d care to share (you have my email address, I believe).

  36. That is the default email content sent by captivate. I have not parsed it. That is all that it brings up.

    Thanks.

  37. Update: I spent some time looking at the output stream and figuring out all the character encoding entities that Captivate uses. I did a bunch of find/replace steps in my editor and was able to see the logic of it. Now I’ll take a stab at rewriting the JavaScript to parse out the interaction data as well as what Captivate calls the “Core Data” (which is aggregate data about the entire quiz). My JS chops are pretty weak, but I think I can make it happen. Then we’ll worry about changing the DB scheme to accommodate the extra data and some rewrite of the back end to display it.

  40. Anthony, I have made considerable progress on it. I’ve managed to chop out the interaction information and other data in the output stream into an array. My main problem now is getting parsed into invisible fields in the pop-up form (which is created with JavaScript) so they can be written to a database. So, right now, it’s my lack of JavaScript savvy that’s slowing down progress. But I expect I’ll get it to work soon.

  41. Admin,
    Can someone post the files for the ASP solution as stated in;

    Isaac Tabe says:
    June 8, 2009 at 5:26 pm

    The demo below illustrates how you can collect user information before a aptivate 4 quiz, use the user information to customize question feedback messages, display user information on a quiz completion certificate, use JavaScript to display quiz results and other information, and give the participant the option to post their quiz results to a Microsoft Access database.

    You can test to demo here: http://www.myshbp.com/captivate/cap4demo.htm

  42. This blog is so wonderful. I use Captivate 2, and wonder if any of the suggested work-arounds would work for me? I have a project that really needs to have quizzes saved and results sent via email. We’re currently unable, and are about to use paper tests, ugh. Have you tackled sectional scores at all? That’s another “need” I seem to have, maybe others would benefit from it too. Thanks for all your work.

  43. Great site. I’m new to using e-learning and am trying my wings at captivate. I’m a trainer (end-user) so never had reason to learn how to write programming scrip. I’m also restricted in software have – is there anyway to have this information sent via an email without the additional software – captivate 4 seems to have the options but they don’t work the way one would anticipate – esp Email Results.

  44. Hi,

    I use Captivate 4 to create quizzes for virtual campus. I created the file to send the scores to MySQL database and works fine. But my problem is other. Sometimes, when the students click to Finalize button on Score slide (the last slide) for to send the data, doesn’t work. Other times works correctly. What’s the problem? Flash configuration? Navigator configuration?

    I’m lost because from my computer always works fine.

    Thanks in advance!

  45. @aehare70: I don’t have those files, nor do I do any ASP development. You’ll have to contact the developer of that solution.

    @JoEllen: I’ve used Captivate since Version 1, but I’ve only gotten into using it for quizzing and reporting recently. So I don’t know of a way to make this work in Captivate 2. There may be one, but I’m not sure. Captivate 4 is really a huge improvement, and Captivate 5 is currently in development (there are some previews on the web: it looks great). I hate to be one of those people who says the solution is to upgrade, especially as this is really expensive software, but I think Cp4 is really worth a look, if you can swing it.

    @Riyana: Emailing results is problematic. It does work, for the most part, so long as you’re in a really controlled environment. This is the case because Captivate doesn’t really send the emails, it leverages the local (desktop) email client and its associated email server. So, in a corporate environment where everyone has the same desktop email client and uses the same mail server, you get consistent functionality. But, in the wild, many people, myself included, use web-based email. So bypassing email in favor of a web-based solution is your best bet.

    @xpetit: There’s a comment in this thread which addresses this problem. It seems that there’s some inconsistency in Cp4 with respect to which of these email functions gets called. Here’st the entire thread: http://pipwerks.com/2008/09/07/send-captivate-quiz-data-to-javascript/

    And here’s a quote from the relevant part. There’s a solution in the thread itself:

    “It’s been my recent experience with IE that it’s not possible to consistently say which of either padMail() or sendMail() (and sometimes both) is called – luckily, I’ve not encountered the ‘neither’ case as yet. This seems to be ‘random’, in that I certainly can’t spot a pattern – certain machines seem to be disposed to 90% of the time use padMail(), some are biased the same towards sendMail(), etc, unlike one of the previous commentators, where it was consistently one or the other.”

  46. Hi,
    I followed the instructions many times, and I can’t get it to work. I’m using php5, could this be causing my promlems? Also under the activity window, I get an error MMtracking can’t be found.

    Thanks

  48. Thanks for the great post! It works great. But I’d like to store the users name as well in the database. Would it be possible to output the content of user defined variables to the database?

    Thanks – Gecco

  49. Has anyone done this exact thing with a ASP.NET/SQL combo? What you’ve done looks amazing but unfortunately my company is a microsoft shop…

    thanks!

  50. Gecco, I think that could surely be done. Right now, I’m just capturing the user’s name and email via the HTML form, since that’s easy. But the user-defined variables in Cp4 could be used for that same purpose. And there should be a way to pass them to the script. I’ll have to start a feature request list and put that on it.

  51. Marie, I feel your pain. The company where I work is also a Microsoft shop, by and large. In fact, I created this project partly as a proof-of-concept to see if they could develop something similar in ASP.NET/SQL Server. And they did, in fact. But I’ve only done some limited testing of it and haven’t implemented it yet.

  52. great! well let me know how the testing goes.

    i would love love love to implement something as it would be a vast improvement over what we have now (nothing).

    thanks!

  53. Pingback: wheatblog » Blog Archive » Cp2DB: Adobe Captivate Quiz Reporting with PHP/MySQL (Version 2) - personal weblog of James "Wheatbread" Martin

  54. I have Captivate version 3, and I just downloaded a demo of Captivate version 4.

    I am using a javascript similar to what the wheat blog described…. no matter which version 3 or 4… .padmail is not consistently called in IE. (our company machines are primamrily XP with IE 6)

    I have chased various “ideas” to fix this problem.. making my JS a separate file… upon publishing choosing flash player 8+ (instead of 7 within cap 3)… and obviously creatign a module in cap 4…

    same results… inconsistently .padmail is not called… once the computer has experienced this error.. it works every time after that (for that module) a new module the machine may or may not have that error… GRRR!!

    Does anybody have any ideas?

  55. Steve, my solution calls sendmail() instead of padmail(). I haven’t had any trouble with sendmail() not getting called. But, I have read of others who have seen inconsistencies with both sendmail() and padmail(). The solution there would be to override both of those functions (maybe with a little code to make sure they don’t both get called). I’m not sure why both sendmail() and padmail() exist. Is one for email reporting in the body of the email and the other for email reporting as an attachment? Those are two choices in the Quiz Options. Any clue? I haven’t experimented with it.

  56. Pingback: Cp2DB: Adobe Captivate Quiz Reporting with PHP/MySQL (Version 2) «

  57. I am using your sample project files cp2cb_v2_rc3.cp) to familiarize with the process. I am using Godaddy.com as hosting, I got to set up my computer dreamweaver cs4 with the services PHP/mySQL from the host. Files and database are working fine.
    But, even though all the test server results are successful, when I take the quiz (using cp2db_v2_rc3.htm) after clicking Send Email, nothing happens, and can not get the popup screen to enter my name and email.
    could you please help to get it fix.
    Thanks

    joe

  58. Hi,
    I have a different question, I want to store data (such as: image,sound,text & etc) in mysql and then restore them into Captivate file (Export:Swf) , in otherwise I want more security for saving copyright.
    Is there any solution for do this?
    {please guide me}

  59. Hi. Very new to this but does anyone think the captivate report can be sent to a google spreadsheet?
    Jase

  62. @Joe I would suspect this has to do with either where Sendmail is located on GoDaddy’s servers, or with access to the same. You might have to contact GoDaddy to see if there’s some additional step necessary for enabling Sendmail access on your account (that or find a better host).

  63. If this project interests you, be sure to see version 2, which, among other things, allows you to capture data about each question, rather than just the summary data about the quiz:

    http://wheatblog.com/2010/01/cp2db-adobe-captivate-quiz-reporting-with-phpmysql-version-2/

    I’ve also created a separate page for this project. It’s the best place to check for further development, documentation, etc.:

    http://wheatblog.com/software/cp2db-captivate-to-database/

  65. Currently, I just have the step-by-step instructions that are included with the post announcing each new version (i.e. follow the Version 1 or Version 2 links on the Cp2DB project page). A full tutorial is a great idea. I might wait for that until I release Version 3, which is currently in development.

  66. Pingback: Class Notes Captivate Bootcamp July 2010 | My Blog

  67. I will immediately snatch your rss as I can’t find your email subscription link or e-newsletter service. Do you have any? Kindly let me understand so that I may just subscribe. Thanks.

  68. RSS is the only subscription option at present. I generally announce new posts via Twitter. I’m @jamesnotjim. Please follow me there.

  70. Well, in the results, you’re getting a NaN error when accuracy is calculated, which usually means you’re trying to divide by zero. Not sure why that would be happening.

Leave a Reply

Your email address will not be published. Required fields are marked *