Cp2DB: Adobe Captivate Quiz Reporting with PHP/MySQL (Version 2)

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.)

New Features

  1. Captures both “Core data” and “Interaction data” (explained below). Version 1 only captured “Core data.”
  2. Relational database structure. Version 1 captured everything in one table.
  3. 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:

  1. Create a quiz in Captivate 4.
    (I’ve included a sample project: cp2db_v2_rc3.cp.)
  2. 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.
  3. 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.
  4. Download and unzip the Cp2DB files.
  5. Open sendmail_v2.js in your favorite text editor. Select all and copy the entire file to your clipboard.
  6. 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.)
  7. Use paste to replace the default sendMail() function with the new-and-improved one on your clipboard.
  8. Save your changes to foo.htm.
  9. 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.
  10. Edit insert_v4.php. On line 20, change “localhost,” “username,” and “password” to your MySQL host, username, and password values.
  11. Edit the same values in lines 66-68 of display_v4.php.
  12. 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.
  13. 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.)
  14. Feedback is welcome. Use the comment thread of this post for your bug reports, feature requests, and other feedback.

Known Issues/Limitations

  1. 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.
  2. As far as I know, it only works with Captivate 4. I haven’t tested it with earlier versions.
  3. 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.

28 comments

  1. Thanks, Michael. I love your site, BTW. I’ve only recently discovered it. I’m looking forward to trying out some of your tutorials on advanced interactions.

  2. Hi
    I followed this and got it sort of working, but i get the error

    Successfully connected to the database.
    Could not insert data into tblperson. Died with this error: No database selected

    when sending the results.

    Any idea what i have done wrong ? Any help would be great.

  3. Hi, Marc. My guess would be that the database itself hasn’t been created, or, if it has been, its name on the database server doesn’t match the one in the script preferences. Can you double-check that? The fact that it is connecting to the database server is good.

  4. Hi again. A database has been created called cp2db and has 3 tables, tblcore, tblinteraction and tblperson. I checked as far as i can and (im no expert on this) the preferences seem ok. It still connects but wont write data to the database. Still says

    “Could not insert data into tblperson. Died with this error: No database selected”.

    I must be overlooking something easy.

  5. Hi James Thank you very much for the great work. I deployed
    your ealier version of the php/mysql captivate 4 database at my
    website covering 40 quiz questions of all types for a SAP basics
    and navigation course. I have since wished I could capture the
    interactions as well. I am experimenting with your version 2. I am
    able to capture the interactions into the mysql database except
    that the true or false, drag and drop and other quiz types populate
    wrong fields. I would be glad to know when you finally get it to
    work. Kind regards.

  6. I’m not using PHP or MySQL, but rather ASP and SQL. Modifying your JavaScript, I was able to get ALL interaction data regardless of question type from CP5.

    I used your swf without a hitch (populated every question (even write-ins)) but
    I do have a problem though. I tried to build an assessment per your instructions, but it takes a click per question plus another to submit the information. If we could communicate offline, I would greatly appreciate it.

  7. I sent you an email with my phone number, Ian. Glad to here you were able to rewrite it for your platform. And I’m intrigued to hear that you were able to make it work with all interaction types in Cp5! I haven’t done any Cp5 testing with this yes. I guess it’s time I did.

    Isn’t one click per question plus a second click to submit standard (albeit annoying) in Cp? I’ve thought about trying to work my way around that, but I’ve never come up with anything.

  8. I’ve continued to work this since we’ve last talked. I seem to have come up with a solution and sent it to your email address. 3 additional lines and it’s golden!

  9. Ah, great work, Ian! I appreciate your efforts, and I’ll be happy to add them to the code base for the next release, with your permission, of course.

    Some who find their way to this page will surely be interested in your re-implementation of Cp2DB in ASP. That deserves as post of its own but here are some links, for anyone interested. It’s laid out in this series of blog posts:

    Publishing Captivate to SQL Using ASP, Part I, Continuing with Captivate, Publishing Captivate to SQL Using ASP, and Publishing Captivate to SQL Using ASP, Part III (Version 2)

    Thanks for your work! I’m glad Adobe Captivate and Cp2DB are working for you!

  10. Hi,

    I’ve tried your solution and it works great. I’m using CP5. The multiple button click are annoying . I was wondering if I could get a look at Ians solution.

  11. Hey

    Thank you for this solution.

    I’ve done everything as per your instructions (even used your sample files) but when I click on “send email) nothing really happens. Im not sure what im doing wrong. Do these files have to be uploaded to a web server or can I use it localy from my pc?

    Thanx in advanced

  12. Per the instructions, these files only work on a web server. You can set up a local web server for testing (e.g. MAMP (OS X), WAMP (Windows), XAMPP (Linux) if you’d like to test locally. I test them using MAMP and generally also test via my the same web server which runs this blog, which is yet another LAMP stack.

  13. Hi,
    I followed it and got it working. No error occurred on the process and data were saved in database, it’s just that the data were not accurate/correct as per quiz results. coreMaxScore and coreMinScore appeared to be the date and time I have saved the data instead of scores. Did I miss something? Please help asap. Thanks!

  14. In cases where I’ve seen this before, the issue is trying to use a question type other than multiple-choice. The current version only supports multiple-choice interactions.

  15. Hi all;

    Could you please help me?
    The popup window (htmlstring) is not working, captivate 4, when I click submit scores, the popup window will not display, I already uncheck the popup blocker but still is not happining.
    Thanks

  16. Point of clarification: this is a web application. It will only work via a web server. If you’re testing locally, on Windows, I recommend WAMP Server http://www.wampserver.com/en/. If you’re on OS X, I recommend MAMP http://www.mamp.info/en/index.html. This was the issue in Jose’s case, and I’ve had at least one other request for help recently for which it was also the case. If you need a web host, you might consider A2 Hosting. That’s what I use. Here’s my refer-a-friend link (yeah, I get a kickback): http://www.a2hosting.com/refer/19457

  17. James

    Have you upgraded this to Captivate 5? Respond to my email address. Would like to talk to you about consulting work.

    thanks
    Clay

  18. Too busy for any consulting at the moment, Clay, though I appreciate the offer. I haven’t tested with Cp5 yet, but I suspect it will work and I haven’t had any bug reports to the contrary.

  19. I have tested your script with Captivate 5.5 and can verify that it indeed still works well with multiple choice questions, and even possibly true and false ones as well. I did have issues with fill-in-the-blank questions and haven’t tested others; I am curious if you might be willing to update it to handle other question types or if anyone has a modified version that supports other question types.

    Thanks for your hard work nonetheless, this at least gives me a starting point with storing results in a database. :)

  20. Ouija, thanks for testing and letting me know! I’m glad to know that it works fine with Captivate 5.5. I don’t know of a modified version which implements different question types.

    I do intend to continue working on this project and to enhance it so that it can accommodate more question/scored interaction types. I would also like to continue work on the backend of it (the web app part where the scores are stored).

  21. I am also intereted in the possibility of working with another kind of questions as maching, fill in blank etc..

    Can you tell us how far is the project on this things so far and when do you think it will be ready?
    Do you know how can i send the results whitout clicking the email send button, i mean in the moment the quiz is finished the reults are sent directly to the database?

    Thanks

Leave a Reply

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