How to Communicate Between Storyline and a mySQL Database

Communicating With a mySQL Database from Inside Storyline 360.

Ever wondered how to connect to a database from inside Storyline? Need to run your course from a webserver instead of an LMS and don’t have access to a Learning Record Store to save data? Want to pull data from a large collection that can’t be included in your project? Just want to learn something new? If any of these sound like you, then you may be interested in this article.

Demo: https://360.articulate.com/review/content/c3f3c563-bde2-4fa1-96e4-c8ab5b55f991/review

Note: If the database in the demo stops working, it is probably because I forgot to renew it. This free site requires weekly renewal.

I’ve been toying with parts of this on and off over the last couple of years. When I saw this question come up recently, I thought it might be time to put it all together. This approach was drawn from several online resources, but the specifics on database connections came from this very informative video series.

What You Need

• Storyline 360
• An online web server with PHP available
• A mySQL database that is accessible from the web
• Some knowledge of JavaScript
• Passing familiarity with reading and editing a PHP script
• Basic understanding of mySQL queries (and how to get your data into a database)

Overview

The overall process is that you build a Storyline slide that includes a web object. This does not need to be visible, but it will point to a web site on your web server that includes an index.php or index.html file (more on that later). You will use some JavaScript to pass your data from Storyline to the web object. The index file on your web server will receive this data. It then determines what to do with the data you passed and sends requests to the database. The database responds to the request, returning new data. The web server then sends this data back through the web object, to Storyline. From there, you can do whatever you want with it.

In keeping with the original question, this example queries a database of users using a username that you type. It then returns biographical information and image data to Storyline, which displays it on the slide.

In Storyline

The required variables are:

• action - “fetch” for this example, can be made into whatever action you want
• username – the username you entered in the text entry
• bio, loc, name – receives data returned from the database (for display)
• imgTag_1 (and 2) – these are the images used to display the returned image data

Since we need to communicate through the web object, the JavaScript used in the trigger first checks to see if the iframe is ready on the slide (bottom of script). It won’t appear until ready, so we need to wait until it is. When it’s ready, it calls the postMessage function. This builds your message from the action and your data (username), specifies who is supposed to receive it (approvedTarget), and uses postMessage to send it to the iframe.

The function also creates an event handler to listen for return messages from the iframe. This is where any data returned from the database will get processed. This handler first checks to see if the received message came from where we expected (approvedOrigin). If so, then we use the data found in event.data.

For this example, the returned data is a delimited string holding the name, location, biography, image URL, and base-64 image data from the database. Each entry is separated by a double quote. How you assemble and return the data string is up to you (more later).

Here, we send the information back to Storyline variables and use the image data to swap the displayed images in our tagged slide pictures. If an empty string is returned, then nothing matched our request.

On the Web Server

On your web server, you need a basic webpage (index.php) and a folder with a couple of other files. The easiest way to create these files is to create a new folder somewhere on your PC. Inside this, create a file called “index.php”. You also need to secure your website against allowing people to browse your files. In this folder, create a file called “.htaccess”. Make sure the first letter is a period. Inside that file, enter the following one line of text, and then save this file:

Options -Indexes

Now create another folder called “includes”. Inside includes, create two files called “formhandler.inc.php” and “dbh.inc.php”.

When we are done editing these files, you can zip the index.php file  and the includes folder together to upload to your website folder on the web server. Then unzip them and you’re ready to go. Delete the zip file after unzipping.

index.php (or index.html)

This is your webpage. All you need are the basics:

<!DOCTYPE html>
<html>
<head>
	</head>
	<body>
		<script>
			//JavaScript goes here
		</script>
	</body>
</html>

You will include some JavaScript in this file that will receive the message you sent from Storyline. It will decide how to handle the data you included and then it will make a POST request to transfer that data to another file in the includes folder. That is where the database communication will occur. The POST request here is akin to filling out a web form and clicking a submit button. This page will also listen for return messages from the POST request, and then, in turn, return the data in these messages back to Storyline.

The index.php file contains the <script></script> section. This holds the JavaScript. This script has a few functions and a main routine. The main routine first creates an event handler to listen for messages sent from Storyline. It verifies the origin sent with the one specified here to make sure the message is one you want to process. If not, it is ignored. The passed message data is split apart (using comma delimiters). It looks for “fetch” or “put” actions in the first position (we only use fetch here). You can change these to whatever suits your needs (adjust formhandler.inc.php accordingly).

Fetch will send your specified username to the database and then return its response. Because we need to wait for the response, we want to use an asynchronous function here. First, we create a request object (for sending the POST). I used XMLHttpRequest because I know how, but there are other possibly better ways. Then we set up an event handler to lister for this object to say it finished its task. If it succeeded, then we can call another function that will return the message with the database data to Storyline. Once the handler is set up, we make the POST request with the data bound for the database and then exit, waiting for a response.

formhandler.inc.php

The POST request goes to the PHP script on this page. Where JavaScript runs on your webpages in the browser (as you use it Storyline), PHP runs only on the server. It runs before the webpages go to the browser, so it is invisible. No one can see what goes on here.

This PHP script verifies that the call to this page was for a POST. If not, it is ignored. If POST, get the passed variable (username). Then establish a database connect by including another PHP script file (dbh.inc.php). Then, we create a mySQL query statement. The passed data is bound separately to the query (instead of including it in the query directly).  This separation prevents malicious data from hijacking your query. The statement is executed. We expect a single row to match, so we use fetch() to get it. We assemble the fetched data into a string delimited by a double quote character (use whatever is appropriate for your data). When ready, we use echo to output the text string, which is what gets returned to the POST request.

<?php  //Note: don’t use a closing tag in this script, leave it open
if ($_SERVER["REQUEST_METHOD"] == "POST") {
	// get data from mySql, and echo output to return the data to the requester
	$username = $_POST["username"];
	try {
		require_once "dbh.inc.php";//has DB connection information
		$query = "SELECT * FROM users WHERE username = :username;";	
		$stmt = $pdo->prepare($query);
		$stmt->bindParam(":username", $username);
		$stmt->execute();
		$row = $stmt->fetch();//get next line of data (all requested fields)
		$sep = '"';//specify data seperator for returned data
		//echo text to return the data to the POST requesting routine 
		echo $row["name"].$sep.$row["location"].$sep.$row["bio"].$sep.$row["url"].$sep.$row["image"];//get column contents
		//clear the database connecction
		$pdo = null;
		$stmt = null;
		die();//exit script
	} catch (PDOException $e) {
		die("Query failed: " . $e->getMessage());//if connection problem, exit script
	}
} else {
	die("Invalid Request");//if not a POST request, exit script
}

dbh.inc.php

This PHP script is included into the previous script. It contains the connection details for the database and creates the connection. Update the database host, database name, database username, and database password with your own information. Do not share it.

<?php  //Note: don’t use a closing tag in this script, leave it open

	$dsn = "mysql:host=hhhhhh;dbname=nnnnnnn";
	$dbusername = "uuuuuuuuu";
	$dbpassword = "ppppppppp";
	try {
		$pdo = new PDO($dsn, $dbusername, $dbpassword);
		$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
	} catch (PDOException $e) {
		echo "Connection failed: " . $e->getMessage();
	}

Final Thoughts

Requesting data from the database requires the asynchronous function so your script isn’t stuck waiting for a response. Just sending data to the database (the “put” section of the script in the index.php file) does not have to wait, so it uses a synchronous function.

To add additional functionality, you can create more action tags, pass them to index.php, and either make POST requests to additional formhandler script files, or update the existing fornhandler.inc.php file to accommodate more functions.

You can have your database on or separate from your web server. As long as you can communicate with it, this should work. I am unfamiliar with other types of databases, so I don’t know what specific changes might be required to connect to them.

The database structure is as shown below.