Code Example – Alexa Backend

I’ve been playing with writing Alexa skills recently, using the Storyline tool.

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
#################################################
?>