
Storyline is great for writing scripted skills, and also has facilities for connecting to other online applications to exchange data, via JSON and simple GET/POST connections.
I wrote this very simple PHP routine to allow Storyline to read and update data held on a mySQL database. It isn’t particularly elegant, I wouldn’t even go as far to say it is good code, but it works! It was written more to prove a concept than anything else, although I am now using this general structure in one of my skills.
To do a lookup (in this example, tell you the last 3 books you read), you would set up a Storyline GET and point it at:
http://www.example.com/booklist.php?amazonUserId={{amazonUserId}}&lookup
The code will return its response as a JSON which can be incorporated back into Storyline as
SessionDetails ➞ api_response.Alexa
To do an update (add a book) you need Storyline to call:
http://www.example.com/booklist.php?amazonUserId={{amazonUserId}}&title={{title}}&update
Again, using SessionDetails ➞ api_response.Alexa will give you a confirmation that the update has happened.
Early on, I adopted the idea that instead of simply presenting the data, then having to write stuff in Storyline to incorporate that data into spoken text, I prepared whole phrases here in my code, and Storyline/Alexa just echoes it. Aside from anything else, it means I can edit the backend to make subtle changes to phrasing, without the need to republish the skill.
You will also see that if I detect a new user, I queue up a phrase to introduce them to the Skill for the first time.
<?
# Alexa Backend – Example Code
# October 2018#################################################
# readme section# This script does two functions:
# 1. Updates a mysql database with content from Storyline.
# 2. Searches the database.# end readme
##################################################################################################
# sources setup// Include the full path to the Alexa config file.
$path_to_config = “config.php”;
// Make sure we can get to the config.php file
if(file_exists($path_to_config))
{
include_once($path_to_config);
} else {
die();
}// Connect to the MySQL server and select the right database
@mysql_connect($dbhost, $dbuser, $dbpasswd) or die();
@mysql_select_db($dbname) or die();# end sources
##################################################################################################
# run section$amazonUserId = $_GET[“amazonUserId”];
$title = $_GET[“title”];if(isset($_GET[“update”])) {
$query = “INSERT INTO booklist (amazonUserId, title) VALUES (‘$amazonUserId’,’$title’)”;
if(mysql_query($query) == ‘true’) {
$phrase = “You have taken ” . $title . “. Your book list has been updated successfully.”;
}
else {
$phrase = “A problem has occurred.”;
}
}
elseif(isset($_GET[“lookup”])) {
// Construct the SQL statement.
$query = @mysql_query(“SELECT timestamp, title
FROM booklist
WHERE amazonUserId = ‘$amazonUserId’
ORDER BY tableID DESC
LIMIT 3″
);$num_rows = mysql_num_rows($query);
// Prefix the phrase to be passed to Alexa.
$phrase = ”;
//$phrase .= ‘Add starting phrase, if necessary <break time=”0.2s” />’;
// Loop through each session found
for ($i = 0; $session = @mysql_fetch_array($query); ++$i) {
$date = date_create($session[0]);
if(date_format($date, ‘Y-m-d’) == date(‘Y-m-d’)) {
$SessionDate = ‘ today.’;
}
elseif(date_format($date, ‘Y-m-d’) == date(‘Y-m-d’,strtotime(‘-1 days’))) {
$SessionDate = ‘ yesterday.’;
}
else {
$SessionDate = ‘ on ‘ . date_format($date, ‘l F jS’);
}$SessionTime = date_format($date, ‘g:ia’);
// Build the phrase.
switch ($i) {
case 0:
$phrase .= ‘We last logged you reading ‘ . $session[1] . ‘ at <break time=”0.2s” />’;
break;
case 1:
$phrase .= ‘Before that, we logged you reading ‘ . $session[1] . ‘ at <break time=”0.2s” />’;
break;
case 2:
$phrase .= ‘Previous to that you read ‘ . $session[1] . ‘ at <break time=”0.2s” />’;
break;
}$phrase .= $SessionTime . ‘ <break time=”0.2s” />’;
$phrase .= $SessionDate . ‘ <break time=”0.2s” />’;
}// End the phrase
//$phrase .= ‘End phrase here, if required.’;
// If no date, we have a first time user. Queue the same Alexa text that is available in the Help block.
if ($num_rows == ‘0’) {
$phrase = ‘It looks like you are a new user of this skill. <break time=”0.2s” />’;
$phrase .= ‘Book List is a skill that lets you keep a diary of books you have read. <break time=”0.2s” />’;
$phrase .= ‘If you have used it before, it will tell you the last 3 books read, before letting you make a note of what you are reading now. <break time=”0.2s” />’;
}
}else {
$phrase = “Unexpected calling of Alexa routine.”;
}
# end run
##################################################################################################
# alexa stage$alexa->Alexa = $phrase;
// Add a JSON Header
header(‘Content-Type: application/json’);
echo json_encode($alexa);
# end alexa stage
#################################################
?>
Be First to Comment