How to Save Storyline Quiz Data to an External Database

Storyline External Database

Saving Storyline Quiz Data Without An LMS

 

Looking for another way to manage and store your Articulate Storyline quiz results data?  My step-by-step guide covers how to write Storyline quiz data to an external database.  This guide includes some pretty geeky details as is geared toward experienced eLearning programmers who aren’t intimidated by database tables and light PHP coding.   The database structures I present work with any Storyline quiz with no need for custom columns.

Quite a lot of research and time was invested in developing this system including dissecting the Storyline quiz output using developer tools in Chrome. So I hope this saves you time should you be interested in storing Articulate Storyline quiz data this fashion.

My approach uses a combination of Javascript, PHP and Articulate Storyline and requires that you have the tools and knowledge to create a database table or you have access to someone who can create the database for you. I also include sample code that you can download to help you get started.

NOTE: Need help? Contact Us or call 866-771-4449 between 7:00 a.m. – 5:00 p.m. pacific time

The Storyline Quiz Setup

We have a typical quiz with a mix of multiple choice, True/False and drag and drop, but for the first questions we will ask the learner for their name and email address so we have some type of identifier for the learner.  Be sure to save these values in a variable that you will use later.

01-Name 02.Triggers

On the results screen add a Trigger to run some Javascript and since we are thinking ahead here and we don’t want to publish the quiz in the event the code needs to change; so we are just going to make a call to storeQuiz(). Call this function when the timeline starts so it get’s stored every time.

03.Trigger Wizard

*Note this will run every time this page is shown so if they review the results it will log another entry into the database when they get back to this page.  A simple fix for you to set a variable to run the query again or not.

That’s if for Storyline save and Publish!

The Database Setup

Keeping in mind that we want this to work for every quiz we created so it needs to be flexible and not quiz specific.  Setup 2 tables one for the quiz results and one for the questions results of that quiz.  To keep things simple for this article we have just outlined the columns needed below.

 

Quiz Results Question Results
quizId unique identifier for the quiz which is generated when you publish the quiz. quizResultId This will be the quizId in the quiz results table.
usersName Name of the learner description This is the question text.
usersEmail Learners email correctResponse The correct response for the question
questionCount Number of questions in your quiz not including the first one. status Correct or Incorrect information
quizName Name of your quiz, this is set in Storyline userResponse What the user set
passingScore The score needed to pass, set in Storyline weight The weight of the question set in Storyline
dtFinished Timestamp of the quiz date points How many points this question is worth
maxScore Maximum score set in Storyline questionNumber The number question in the quiz
minScore Minimum score set in Storyline.
ptScore The learner’s score
quizStatus This is a pass/fail setting

The Javascript

This is where it gets fun because the quiz data gets dissected.  As mentioned earlier you will save a lot time since we did most of the leg work for you.

First things first create the JSON object to store this information.

var jsonQuizData = new Object();

 

Articulate has made things easier for us so all we need to do is make a call to GetPlayer() to get the variables for the learner’s name and email

var sl = GetPlayer();
var learnersName = sl.GetVar(“usersName”);
var learnersEmail = sl.GetVar(“usersEmail”);

 

The following is to get the hi-level quiz results of the page.  This will correlate with our Quiz Results table in the database, the array g_listQuizzes is a reference to a variable articulate already has so this is setup for us.

var arrayLength = g_listQuizzes[g_quizId].arrQuestions.length;

jsonQuizData[“questionCount”] = g_listQuizzes[g_quizId].arrQuestions.length;

jsonQuizData[“strQuizName”] = g_listQuizzes[g_quizId].strQuizName;

jsonQuizData[“nPassingScore”] = g_listQuizzes[g_quizId].nPassingScore;

jsonQuizData[“dtmFinished”] = g_listQuizzes[g_quizId].dtmFinished;

jsonQuizData[“nMaxScore”] = g_listQuizzes[g_quizId].nMaxScore;

jsonQuizData[“nMinScore”] = g_listQuizzes[g_quizId].nMinScore;

jsonQuizData[“nPtScore”] = g_listQuizzes[g_quizId].nPtScore;

jsonQuizData[“strQuizId”] = g_quizId;

jsonQuizData[“strStatus”] = g_listQuizzes[g_quizId].strStatus;

jsonQuizData[“usersName”] = learnersName;

jsonQuizData[“usersEmail”] = learnersEmail;

 

Then loop through the questions to get each of the questions data, this maps back to our Question Results table.

for(var i = 0; i < arrayLength; i++){

jsonQuizData[“question”+i] = {

“description” : g_listQuizzes[g_quizId].arrQuestions[i].strDescription,

“strCorrectResponse” : g_listQuizzes[g_quizId].arrQuestions[i].strCorrectResponse,

“strStatus” : g_listQuizzes[g_quizId].arrQuestions[i].strStatus,

“strUserResponse” : g_listQuizzes[g_quizId].arrQuestions[i].strUserResponse,

“nWeight” : g_listQuizzes[g_quizId].arrQuestions[i].nWeight,

“nPoints” : g_listQuizzes[g_quizId].arrQuestions[i].nPoints,

“nQuestionNumber” : g_listQuizzes[g_quizId].arrQuestions[i].nQuestionNumber

}

}

 

Finally, send this over to our script that will do the storing of the data, but we just did the heavy lifting with Javascript.

request = new XMLHttpRequest();

request.open(“POST”, “http://cisiontraining.com/quiz/quizResultsHandler.php”, true);

request.setRequestHeader(“Content-type”, “application/json”);

request.send(JSON.stringify(jsonQuizData));

 

That’s it for the Javascript what we did was parse through all of the data in the quiz and packed it in a really nice JSON object which can essentially be sent to any script to process, JSON like XML is a way to transport data.

Writing Storyline Quiz Data Using PHP

The following is done in PHP, but f you know how to code or know someone who does you can theoretically do this in any language since we are sending over JSON.  We are just consuming the JSON object we sent and store it using SQL.  Again we are providing the code, but if this makes your head hurt the the files are linked below.

We are skipping the connection info since that is specific to your database, but you will need the hostname, the name of your database, user and password.  So, the first thing we are doing here is getting the quiz data and decoding the JSON.

$str_json = file_get_contents(“php://input”);

$quizObj = json_decode($str_json);

 

The timestamp needs to formatted to work with MySQL so that is what the following line of code does, and the next few lines will make the connect to the database.

$mysqltime = date (“Y-m-d H:i:s”, $quizObj->dtmFinished);

$dbQuizId; // will score the quizId for this user.

 

$conn = new mysqli($hostname, $username, $password, $database);

// Check connection

if ($conn->connect_error) {

die(“Connection failed: ” . $conn->connect_error);

}

 

If you remember back to the tables; we are relating the quiz results entry to all of the questions which means we need to submit the overall quiz results to the database first to retrieve the table ID for the questions.  What you need to notice here is that the $quizObj will be able to access all of the data we stored in JSON with the ‘->’ access operator.

 

$sql = “INSERT INTO quizresults (quizId,usersName,usersEmail,questionCount, quizName, passingScore,dtFinished,maxScore,minScore,ptScore,quizStatus) VALUES  (‘”.$quizObj->strQuizId.”‘,'”.$quizObj->usersName.”‘,'”.$quizObj->usersEmail.”‘,'”.$quizObj->questionCount.”‘, ‘”.$quizObj->strQuizName.”‘, ‘”.$quizObj->nPassingScore.”‘,'”.$mysqltime.”‘,'”.$quizObj->nMaxScore.”‘,'”.$quizObj->nMinScore.”‘,'”.$quizObj->nPtScore.”‘,'”.$quizObj->strStatus.”‘)”;

if ($conn->query($sql) === TRUE) {
echo “New record created successfully”;
} else {
echo “Error: ” . $sql . “<br>” . $conn->error;
}

 

Now that is complete we just have to prepare for the questions by retrieving the entry ID with the following line of code.

 

$dbQuizId = mysqli_insert_id($conn);

 

The final process for this step is to loop through the questions and get them inserted into the question results table.

 

for ($i = 0; $i < $quizObj->questionCount; $i++){

$questionVar = “question”.$i;

$sql = “INSERT INTO quizquestionresults (quizResultId,description,correctResponse,status,userResponse,weight,points,questionNumber)

VALUES (“.$dbQuizId.”,'”.$quizObj->$questionVar->description.”‘,'”.$quizObj->$questionVar->strCorrectResponse.”‘,'”.$quizObj->$questionVar->strStatus.”‘, ‘”.$quizObj->$questionVar->strUserResponse.”‘, ‘”.$quizObj->$questionVar->nWeight.”‘,'”.$quizObj->$questionVar->nPoints.”‘,'”.$quizObj->$questionVar->nQuestionNumber.”‘)”;

 

if ($conn->query($sql) === TRUE) {

echo “New question record inserted created successfully”;

} else {

echo “Error: ” . $sql . “<br>” . $conn->error;

}

}

 

$conn->close(); //close the door behind you.

Connecting the Pieces

At this point you have everything in place to make this happen and the final piece is finding your published quiz files and open the story.html file scroll down and insert the following line right before the closing </head>.  You will obviously want to point it to where you saved your Javascript file so be sure to replace with the correct file path.

<script src=”path/to-your/javascript.js” type=”text/javascript”></script>

 

Be sure to put it after the other 2 linked .js files as seen below.

04.JSfiles

 

That’s it! Now when you have someone take your your quiz the results will be stored in your very own database. Click here to download Storyline quiz database setup helper files

Did you find this article useful or do you have any other suggestions for saving quiz data? We’d love to hear from you! Just log in and comment below.  Do you have a geeky cool eLearning trick to share with the community? Consider applying to become an eLearning.net guest blogger! Just call or use the Contact Us form on our website.

Leave a Reply