Get Started

IVR: Database Integration

By connecting the IVR we made in the prior sections to your database, you can allow your customers to access their data without having to speak to an agent. Doing this is as easy as using Twilio's <Gather> verb to collect input over the phone and retrieving information in the database based on that input.

Prior Sections

This is part four of a five part series on building IVRs using Twilio's API. If you're just joining us, you can find links to the prior sections below:

Usage

When a customer dials into our IVR, they'll be prompted to enter their tracking number. The information will then be gathered, looked up in a database, and tracking status returned to the customer.

For this section, we'll be working with a simple SQLite database with one table that looks like:

id tracking_number status
1 1234 in transit
2 4321 shipped

You can use the script below to create the database and table. (You'll need to create the database before running the IVR code below.)

  • howtos/ivr/code/ivrs-database-integration/create_db.php
    <?php
    	$db = new PDO('sqlite:ivr.sqlite');
    
    	// Create the SQLite table
    	$db->exec('CREATE TABLE packages (id INTEGER PRIMARY KEY, tracking_number TEXT, status TEXT);');
    
    	// Populate it with some data
    	$db->exec("INSERT INTO packages (tracking_number, status) VALUES ('1234', 'in transit');");
    	$db->exec("INSERT INTO packages (tracking_number, status) VALUES ('4321', 'shipped');");
    
    	if (file_exists('ivr.sqlite'))
    	{
    		echo 'Database and tables created.';
    	}
    	else {
    		echo 'Database was not created. Please check to make sure this directory is writeable.';
    	}
    ?>
        

Concepts

This demo shows how to gather user input using Twilio's <Gather> verb.

Download

IVR.zip

Implementation

  • 1

    • Our Twilio number's Voice URL is set to handle-incoming-call.xml. When someone calls the number, Twilio will request this page with the standard TwiML request parameters.
  • 2

    • The application responds in TwiML to Twilio's request using the Say and Gather verbs. This instructs Twilio to prompt the customer and gather their input.
    • howtos/ivr/code/ivrs-database-integration/handle-incoming-call.xml
      <?xml version="1.0" encoding="UTF-8"?>
      <Response>
      	<Gather action="handle-user-input.php" numDigits="1">
      		<Say>Welcome to TPS.</Say>
      		<Say>For store hours, press 1.</Say>
      		<Say>To speak to an agent, press 2.</Say>
      		<Say>To check your package status, press 3.</Say>
      	</Gather>
      	<!-- If customer doesn't input anything, prompt and try again. -->
      	<Say>Sorry, I didn't get your response.</Say>
      	<Redirect>handle-incoming-call.xml</Redirect>
      </Response>
          
  • 3

    • Twilio prompts the user and gathers their input. This input is then sent back to your application as an HTTP request.
  • 4

    • Your server processes the customer input, looks up the information in a database, and returns it to your customer.
    • howtos/ivr/code/ivrs-database-integration/handle-tracking-number.php
      <?php
      	header('Content-type: text/xml');
      	echo '<?xml version="1.0" encoding="UTF-8"?>';
      
      	echo '<Response>';
      
      	$tracking_number = $_REQUEST['Digits'];
      
      	if (strlen($tracking_number) == 4)
      	{
      		$db = new PDO('sqlite:ivr.sqlite');
      
      		$stmt = $db->prepare('SELECT status FROM packages WHERE tracking_number=?');
      		$stmt->execute(array($tracking_number));
      		
      		$result = $stmt->fetch(PDO::FETCH_ASSOC);
      
      		if ($result)
      		{
      			echo '<Say>Your package status is '.$result['status'].'.</Say>';
      		}
      		else {
      			echo "<Say>Sorry, I couldn't find that tracking number.</Say>";
      			echo '<Redirect method="GET">handle-user-input.php?Digits=3</Redirect>';
      		}
      	}
      	else {
      		echo "<Say>Sorry, that isn't a valid tracking number.</Say>";
      		echo '<Redirect method="GET">handle-user-input.php?Digits=3</Redirect>';
      	}
      
      	echo '</Response>';
      ?>
          

This is part four of a five part series on building IVRs using Twilio's API. When you're ready continue on to the next section: