Many applications need a connection to a database to function – cloud databases like couchDB are one option (which we discuss in more detail here), but often a local database is more practical.  In this blog, we’ll be using the Tropo WebAPI with a local MySQL database via MAMP, a personal webserver with both Apache and MySQL built in.  We’re going to connect them in order to save important data provided by Tropo about a call, in this case specifically the callerID, timestamp of the call, and a recording of the call.  MAMP isn’t the only option – any webserver will work.

To start, open up MAMP – once it launches, you should be directed to a welcome page like the following:

Take note of your User and Password - these credentials will be used in our actual Tropo application.  Example code is mentioned at the very bottom of the screenshot, we’ll be using that same basic function later.

The next step is to click the phpMyAdmin link as shown by the top arrow; this is where we’ll access and create our database.  As displayed in the screen shot that follows, click the SQL tab towards the top of the phpMyAdmin window; this redirects us to a page where we need to run a SQL script in order to create our database structure:

Inside the box which says ‘Run SQL query/queries on server “localhost”:’, you will copy and paste the SQL script below (note the database name is Tropo and the two table names are calls and recordings):

CREATE DATABASE `Tropo`;

USE `Tropo`;

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";

CREATE TABLE IF NOT EXISTS `calls` (
`callId` VARCHAR(255) COLLATE utf8_unicode_ci NOT NULL,
`number` VARCHAR(255) COLLATE utf8_unicode_ci NOT NULL,
`channel` VARCHAR(255) COLLATE utf8_unicode_ci NOT NULL,
`network` VARCHAR(255) COLLATE utf8_unicode_ci NOT NULL,
`timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
UNIQUE KEY `callId` (`callId`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE TABLE IF NOT EXISTS `recordings` (
`callId` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
`recording_url` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
UNIQUE KEY `callId` (`callId`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

After pasting in that code, hit the Go button at the bottom right and you should be redirected to the page below. If you see Tropo, as indicated by the arrow, the script worked. You can go ahead and click Tropo(2) to view your two tables.

The tables should look like this:

To summarize, you’re connecting through localhost to access the database Tropo,  which contains two tables – calls and recordings.

Now that our database is ready, we can start coding the app.  This app will be initiated an inbound call; it initially greets the user and prompts them to record a message. After the message is recorded, the Tropo thread will either be directed to a continue route if the message was recorded successfully or sent to the error route if an error occurred.

Since this is a WebAPI application, the first bit of code you should include is the Tropo PHP library – it’s not an absolute necessity, but it makes generating accurate JSON immensely easier.

//First we include the Tropo WebAPI classes.
require('tropo.class.php');

The next piece of code will retrieve the URI of the defined route (when you define the URL for your Tropo WebAPI app, be sure to declare this variable in the query string; an example would look like this – http://www.yourwebsite.com/voice.php?uri=start) for the thread and a function which will access our database – the function is the section of code you’ll need to modify with your actual MySQL credentials:

//The next step is to determin the URI (or position) within the script the caller is at.
//To do this we use a GET method to find the URI from the url which Tropo is requesting in the background.
$uri = $_GET['uri'];

function connectToMySQL(){
  $db_user = "UserName";
  $db_pass = "Password";
  $db_db = "Database_Name";
  mysql_connect("localhost", $db_user, $db_pass) or die("ERROR: MySQL cannot connect.");
  mysql_select_db($db_db) or die("ERROR: MySQL cannot select database.");
}

Now we will use the $uri variable in a switch statement to decipher which route to go to. This app is setup to have three independent routes – start, continue and error.

The start route defines the section of code where the caller is greeted and asked to submit a recording.  As shown in the following code, the app uses the Tropo session to extract important data (e.g. callID, callerID, channel, network) to save to the database – this data is stored in the calls table. If the recording portion works, the app will move on to the continue route; if it fails, it moves on to the error route – this is handled by the on event.

Below is the start route code:

//Now we want to be able to execute different lines of code when different URI's are called (when the caller is at different stages in the call).
//This can be done by using IF and ELSEIF statements but we're going to keep things simple and use SWITCH, CASE, BREAK and DEFAULT.
switch ($uri){

  //Everything between case "start": and the break; commands will be run when Tropo initially requests the script using voice.php?uri=startinthe URL.
  case "start":

  //We are going to need a connection to MySQL in this section so we include the function we created to deal with the MySQL connection.
  connectToMySQL();

  //We also need to know various details about the caller, we can create an instance of the Session() object which deals with retreiving these
  //details from the initial JSON data request which is sent to this script from Tropo when a new call is received.
  $tropoSession = new Session();

  //Below demonstrates how we can retreive data from our new Session() object and store those details (or in some cases arrays) intolocalvariables,
  //which I've given names that are easier to understand.
  $call_id = $tropoSession->getCallId();

  //The below line sets $caller_from as an array which has all of the FROM data passed accross in the JSON request.
  $caller_from = $tropoSession->getFrom();

  //Below we can see how that information stored in $caller_from can be accessed and pulled out into other variables.
  $caller_number = $caller_from['id'];
  $caller_channel = $caller_from['channel'];
  $caller_network = $caller_from['network'];

  //This is a very basic MySQL query which can be used to insert data into the MySQL database table called 'calls' which stores
  //the call id, the callers phone number (or skype username etc), the channel used (voice or text) as well as the network which could be
  //SIP, SKYPE, etc.
  mysql_query("INSERT INTO `calls` (callId, number, channel, network) VALUES ('".$call_id."', '".$caller_number."', '".$caller_channel."', '".$caller_network."')");

  //We create a new instance of the Tropo() object which we use to process commands on the Tropo service.
  $tropo = new Tropo();

  //This line of code simply sets up Tropo to speak the string data which is inbetween the quote marks.
  $tropo->say("Welcome to the PHP code sample system.");

  //The below record function speaks out to the user asking them to record their message and then asks them to press the hash key
  //to continue and save their message. The record is sending the recording to another php file which extracts the recording and the
  //name variable and saves the recording in that same folder as this app on your server
  $tropo->record(array(
    'name' => 'recording',
    'say' => 'Please leave your message after the beep. Press the hash key when you are finished recording your message.',
    'url' => 'http://yourWebsite/recordings/recording.php?name='.$call_id,
    'terminator' => '#',
    'bargein' => 'false',
    'beep' => 'true',
    'timeout' => 10,
    'maxSilence' => 7,
    'maxTime' => 180,
    'format' => 'audio/wav',
  ));

  //Now we need to set some events and commands to be run when they are executed.
  //The first on event we will be executed if the recording (or prior) command is completed successfully.
  //This simply says to Tropo that we need to go to the voice.php?uri=continue URL (and run the code for that section) as well as speaking out the
  //text from the say command so the caller knows everything has gone well.
  $tropo->on(array("event" => "continue", "next" => "voice.php?uri=continue", "say" => "Thank you, here is the message you have just recorded."));

  //We also needd to know what to do in the case of an error. Instead of getting Tropo to start executing the success (?uri=continue) code we instead  
  //ask Tropo to access the incomplete URI instead.  
  $tropo->on(array("event" => "incomplete", "next" => "voice.php?uri=error"));

  //Now we have told the Tropo object what we want to do we need to get the object to render our the JSON code which will be outputted onto the page   
  //for the Tropo service to read and execute. It is crucial that you remember to render your JSON as this step makes the "instructions" you've just   
  //created readable by Tropo.
  $tropo->renderJSON();

  //We also want to close the MySQL database connection.
  mysql_close();

  //The break command simply signals that we are at the end of the code which will be executed for the "start" URI.
  break;

If the recording from the section of code above was successful, then the on event will register continue and send the thread to the continue route. In this route, the script will do two things – first, it makes a call to the database and sends the recordURL and the callerID to the recordings table. Second, it replays the caller’s recording back to them, says goodbye and hangs up the call – which effectively ends the Tropo app.  Below is the actual continue route code:

case "continue":

  //As with the start case we need MySQL access so lets add that in by calling the function we created earlier.  
  connectToMySQL();

  //As we are expecting to have received JSON data back from the Tropo service we need to initiate an instance of the Result() object.
  $result = new Result();

  //Now we have read in the resulting JSON data we can access it through the Result() object we have just created. We need the calls id so that we
  //know what data to store along side the recording the caller has just created and to track the call in general.
  $call_id = $result->getCallId();

  //Insert the new recording into the MySQL database table called 'recordings', make sure you change the URL so that it corosponds to your
  //own server/hosting or this wont work.
  mysql_query("INSERT INTO `recordings` (callId, recording_url) VALUES ('".$call_id."', 'http://yourWebsite/recordings/".$call_id.".wav')");

  //Create a new Tropo object.
  $tropo = new Tropo();

  //Replay the recording back to the caller and speak out "Good bye" after the recording has finished. Make sure you change the URL to work properly with
  //your own server/hosting.
  $tropo->say("http://yourWebsite/recordings/".$call_id.".wav Good bye.");

  //Hang up the call.
  $tropo->hangup();

  //Render the JSON to be read back by the Tropo service.
  $tropo->renderJSON();

  //Close the MySQL database connection.
  mysql_close();

  //Signal the end of the continue case section.
  break;

Now, if there was an error with the recording (such as no detected audio) the error route will be invoked. The only objective of this route is to alert the user that an error occurred and to try again later:

case "error":

  //The code in between case "error": and break; is used when our error event is called which we set up in the start case.
  //First we create a new instance of the Tropo object.
  $tropo = new Tropo();

  //Next we use the say method to tell the user that an error has occured and that they should call back to try again.
  $tropo->say("Sorry I can't hear anything. Check your microphone isn't on mute and call back to try again. Good Bye.");

  //Hangup the call.
  $tropo->hangup();

  //Render the JSON which will be used to 
  $tropo->renderJSON();

  break;

default:

  //The default case is used in the event that the URI in the URL (?uri=) is left blank or doesn't match one of the above cases we have identified.
  //We simply want to die() the script in the case that this is called for security as it is most likely a security threat.  
  die("An error has occured.");

  break;
}
?>

That actually finishes up the Tropo portion; now we can really see the benefit of the MySQL database.  Below you’ll find the code that extracts the information from your database and publishes it to your browser in a readable format:

<?php

//First we need to create a connection to our MySQL database and also set up some details into variables for the database connection.  
$db_user = "User_Name";
$db_pass = "Password";
$db_db = "Database_Name";

//We use the details we've stored into our variables to connect to the database.
mysql_connect("localhost", $db_user, $db_pass) or die("ERROR: MySQL cannot connect.");

//Next we need to select the database which we wish to work with.
mysql_select_db($db_db) or die("ERROR: MySQL cannot select database.");

//Next we get all of the data from our calls table of the database.
$calls_sql = mysql_query("SELECT * FROM `calls`");

//We then want to set up a while loop which will allow us to output the details for each individual row of the table as it loops.
while($call = mysql_fetch_object($calls_sql)){

  //The below 7 lines of code are used to simply obscure the telephone numbers and skype usernames to only display the last 4 characters for privacy reasons.
  $length = strlen($call->number);
  $starlength = $length - 4;
  $number = "";
  for ($i = 1; $i <= $starlength; $i++){
    $number .= "*";
  }
  $number .= substr($call->number , $starlength ,4);

  //We then use the echo command to construct the HTML markup which will be used to create the page the user will see when they are viewing the calls and
  //recordings. Notice how we close and open quote marks so we can join in the PHP variables which are then printed out to the page HTML source.
  echo "<strong>CALL ID:</strong> ".$call->callId." <strong>CALL FROM:</strong> ".$number." <strong>CALL CHANNEL:</strong> ".$call->channel." <strong>CALL NETWORK:</strong> ".$call->network." <strong>TIMESTAMP:</strong> ".$call->timestamp."<br />";

  //While we are printing out the details for a call we also want to print out any recordings which corrospond to the call. I've opted to use
  //a while loop for this incase you wish to change the script slightly so that it can allow for multiple recordings per call. This is also the best way
  //to output calls which might not actually have a recording at all due to hanging up or an error when they record their message.
  $recordings_sql = mysql_query("SELECT * FROM `recordings` WHERE `callId` = '".$call->callId."'");

  //So this basically echos out a link to the audio file we have stored into the database.
  while($recording = mysql_fetch_object($recordings_sql)){
    echo "<strong>@ RECORDING URL</strong> <a href='".$recording->recording_url."'>Listen</a><br />";
  }
  //After every call we want to have 2 line breaks to make it a little easier to read our index.
  echo "<br /><br />";
}
?>

Launch the app in browser using a URL that looks something like this:

http://www.yourServer.com/index.php

You should see something like this:

The app is accessing the database to pull in the CALL ID, the last four digits of the callerID defined as CALL FROM, the CALL CHANNEL and CALL NETWORK, the TIMESTAMP of the call and finally, the RECORDING URL with link to hear the recording labeled Listen. This, while basic, is the starting point to a more fully realized (and prettified) website that could provide a variety of uses – management for a customer service company able to listen to call recordings, cloud based access to personal voicemail, spot for callers to be able to review their own recordings (provide independent filtering so a user can only view their calls), etc and so on.

While that covers the app as a whole, if you go to github you can view a couple of extra apps that help in other areas – debugging (KLogger.php), saving an audio file (recording.php), and checking if your php is compatible (compatibility.php).

Big shout out to Jamie Street for both the idea and the application code – if you’re interested in learning more about Jamie, check out his website: http://jamiestreet.co.uk

Hope you find this example useful, thanks for reading and enjoy!

No related posts.

You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.