Solved

how to use php to get vxml inputs to update mysql database

Posted on 2014-09-17
35
665 Views
Last Modified: 2014-09-30
Hi -
With some help from Chris Stanyon and Ray Paseur in a previous question (ID: 40319620) I have managed to create a working vxml script that allows the caller to input a crew code and a status code and echo these inputs back to the caller. I have been trying with no success to use the crew and status code inputs as variables to update a MySQL database. How can I use the caller's  inputs as variables in a php sql statement to update my db.
The code creating the vxml script is below.
Thanks in advance for any help.
Richard

<?php 
error_reporting(E_ALL);
// USE THE REQUEST VARIABLE OR A DEFAULT VALUE
$cid = !empty($_REQUEST['caller_id']) ? $_REQUEST['caller_id'] : "9999";
// CREATE THE XML STRING USING HEREDOC NOTATION
$doc = <<<EOD
<vxml version="2.1">
<form id="form_Main">
<field name="crew_code" type="digits?minlength=1;maxlength=6">
<prompt bargein="true">Welcome. Please enter your crew code followed by the pound key</prompt>
<filled>
<log expr="'**** FILLED ******'"/>
<log expr="'**** crew_code =' + crew_code + ' ***'"/>
</filled>
</field>
<field name="status_code" type="digits?minlength=1;maxlength=6">
<prompt bargein="true">Please enter the status code followed by the pound key</prompt>
<filled>
<log expr="' *** FILLED *********'"/>
<log expr="' *** status_code =' + status_code + ' ***'"/>
<break/>
<prompt>
your crew code is <value expr="crew_code"/> and your status code is <value expr="status_code"/> and your caller id is $cid.
</prompt>
</filled>
</field>
</form>
</vxml>
EOD;
// WRITE THE XML TO THE BROWSER
echo $doc;
?>

Open in new window

Code moved into code snippet ~Ray
0
Comment
Question by:rwinnick
  • 18
  • 16
35 Comments
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 40328602
This article shows some of the basics of MySQL, mapping the familiar but obsolete MySQL extension to the current object-oriented MySQLI and PDO extensions.
http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/PHP_Databases/A_11177-PHP-MySQL-Deprecated-as-of-PHP-5-5-0.html

Have you built MySQL queries in PHP scripts before?
0
 

Author Comment

by:rwinnick
ID: 40328819
Hi Ray -
Yes I am very comfortable with php and creating scripts -
My problem is how to get the vxml variables into a form I can use for the php sql insert -
The caller's inputs are not 'available' until the vxml script has run and if I try to 'use' them within the vxml script to create a sql insert the vxml doesn't work anymore -
and I can't figure out how to get them 'outside' the vxml script to run the sql insert after the vxml script has executed and they are available -
I have tried to parse the completed script with simplexml to get the caller's inputs but have had no luck -
Not sure if I made this clear enough for you to help -
Let me know -
Richard
0
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 40328925
OK, not sure this is the right direction, but it's something that looks odd to me.  I've indented the script so we can see the nesting levels.  Have a look at line #25-27.  This appears to nest a <prompt> tag inside a <filled> tag, and the inner data in the prompt tag contains both text and XML markup.  Are you sure that's a valid structure?  PHP SimpleXML seems to lose the <value> tags without notice.  Use "view source" to look at the output here.
http://iconoun.com/demo/temp_rwinnick.php

<?php // demo/temp_rwinnick.php
error_reporting(E_ALL);

// USE THE REQUEST VARIABLE OR A DEFAULT VALUE
$cid = !empty($_REQUEST['caller_id']) ? $_REQUEST['caller_id'] : "9999";

// CREATE THE XML STRING USING HEREDOC NOTATION
$doc = <<<EOD
<vxml version="2.1">
  <form id="form_Main">
    <field name="crew_code" type="digits?minlength=1;maxlength=6">
      <prompt bargein="true">Welcome. Please enter your crew code followed by the pound key</prompt>
      <filled>
        <log expr="'**** FILLED ******'"/>
        <log expr="'**** crew_code =' + crew_code + ' ***'"/>
      </filled>
    </field>
    
    <field name="status_code" type="digits?minlength=1;maxlength=6">
      <prompt bargein="true">Please enter the status code followed by the pound key</prompt>
      <filled>
        <log expr="' *** FILLED *********'"/>
        <log expr="' *** status_code =' + status_code + ' ***'"/>
        <break/>
        <prompt>
your crew code is <value expr="crew_code"/> and your status code is <value expr="status_code"/> and your caller id is $cid.
        </prompt>
      </filled>
    </field>
  </form>
</vxml>
EOD;

// MAKE AN OBJECT
$obj = SimpleXML_Load_String($doc);
echo '<pre>';
var_dump($obj);

foreach ($obj->form->field as $f)
{
    $p = $f->prompt;
    var_dump($p);
}

Open in new window

0
 

Author Comment

by:rwinnick
ID: 40328958
Hi Ray -
That structure (25-27) comes from a sample from the ivr provider and works in the sense that if you call the number the script works appropriately -
If there is a way to send you the phone number etc so that it would not be reflected here I would be glad to do so -
why the lost value tags are above my pay grade :-)
Richard
0
 

Author Comment

by:rwinnick
ID: 40328998
Hi Ray -
I put your example up on my site and took out the prompt in question -
It did not change anything on the simple xml output -
The output appears to only show the field info and not the filled, etc. tags within it -
In which case it would be correct to just show the two 'fields' for crew and status codes with their prompts -
Yes???
Richard
0
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 40329006
This may be too much of a research project for an E-E question.  I'm having trouble even understanding the workflow; it seems that there is XML within XML, and that's kind of counter-intuitive.  Does your IVR provider have any documentation online?
0
 

Author Comment

by:rwinnick
ID: 40329013
Hi Ray (again)
Sorry I keep thinking of things I've tried -
If I use a <submit next= "to a web page that uses your $_Request to get the variables and email them to me "/> in the last "filled" after the "prompt that reads back the variables" -
All the variables show up ok in the email -
So evidently they are there - somewhere :-)
Richard
0
 

Author Comment

by:rwinnick
ID: 40329017
Very little
I apologize if this is too big -
can you see a simpler way to accomplish what I'm attempting to do -
Thanks again I always enjoy your help -
Richard
0
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 40329061
Well, I'd like to see the online documentation from the provider.  Maybe something in there will brighten things up.
0
 

Author Comment

by:rwinnick
ID: 40329068
https://docs.nexmo.com/index.php/voice-api/call
the examples are all the way at the bottom under voice xml
and that's all there is
I have had lengthy discussions with their support folks who express no knowledge of php and are unable to help :-(
Richard
0
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 40329104
Sure - why should they bother to learn PHP?  It's only the most popular scripting language on the web.  Sheesh!

Let me read the docs and I'll try to come up with some ideas.

Did you consider using Twilio?  If so, why did you reject Twilio in favor of Nexmo?
0
 

Author Comment

by:rwinnick
ID: 40329122
I also have an account with twilio -

check out the difference in pricing !!!!!
an inbound call with these guys would be .017 cents per minute and they bill by the second !!!
and it's only 0.87 cents per month for an 800 number -

twilio is a few orders of magnitude more expensive
0
 

Author Comment

by:rwinnick
ID: 40329128
I also have an account with voxeo which is several orders of magnitude more expensive -
but has very extensive documentation and a neat design interface with minimal coding -
To date with my app I am totally copper based and make on the order of 1.25M phone calls per year -
talk about expensive - copper is crazy - but very very reliable -
0
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 40329131
Yeah, I understand economics!  If you want to send me the API credentials you can use Ray.Paseur at Gmail.

What it looks like to me (similar to Twilio) is that you present XML or JSON in a POST request to their machine.  Your XML tells their voice machine what to do.  When the voice machine is done, it makes a request to a separate, asynchronous script on your server.  The results of the call will be present in the request to your server.  So you need two scripts to accomplish the task.
0
 

Author Comment

by:rwinnick
ID: 40329137
Will send you email
0
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 40329402
OK, some progress...  I used two scripts to make a call to my phone and get some digits returned.  The first script is the script that made the POST request to Nexmo's Text To Speech Prompt.  The process seemed to make sense, however the caller id was "UNKNOWN" and the "bye_text" was not played after the pound key.  Maybe the pound key counts as one of the max_digits and I had overstayed my welcome?  The text-to-voice pronunciations were pretty lame; this seems to be an issue in all computer telephony.  I would recommend recording the audio file yourself if there is an option to use a recording instead of text.
<?php // demo/temp_rwinnick_vxml.php
error_reporting(E_ALL);

// REF: https://docs.nexmo.com/index.php/voice-api/call
// REF: https://docs.nexmo.com/index.php/voice-api/text-to-speech-prompt

$url = 'https://rest.nexmo.com/tts-prompt/xml';
$req = array
( 'api_key'    => 'xxx'
, 'api_secret' => 'xxx'
, 'to'         => '17033460600'
, 'text'       => 'Please enter your code, followed by the pound key'
, 'callback'   => 'http://iconoun.com/demo/temp_rwinnick_callback.php'
, 'max_digits' => '4'
, 'bye_text'   => 'Thank you'
)
;

$res = curl_post($url, $req);
echo htmlentities($res);

// UTILITY FUNCTION TO POST VIA THE CURL LIBRARY
function curl_post($url, $post_array=array(), $timeout=3, $error_report=TRUE)
{
    // PREPARE THE POST STRING
    $post_string = NULL;
    foreach ($post_array as $key => $val)
    {
        $post_string .= $key . '=' . urlencode($val) . '&';
    }
    $post_string = rtrim($post_string, '&');

    // PREPARE THE HEADERS
    $length = strlen($post_string);
    $header = array
    ( 'Content-type: application/x-www-form-urlencoded'
    , "Content-length: $length"
    )
    ;

    // PREPARE THE CURL CALL
    $curl = curl_init();
    curl_setopt( $curl, CURLOPT_URL,            $url           );
    curl_setopt( $curl, CURLOPT_HTTPHEADER,     $header        );
    curl_setopt( $curl, CURLOPT_POST,           TRUE           );
    curl_setopt( $curl, CURLOPT_POSTFIELDS,     $post_string   );
    curl_setopt( $curl, CURLOPT_ENCODING,       'gzip,deflate' );
    curl_setopt( $curl, CURLOPT_TIMEOUT,        $timeout       );
    curl_setopt( $curl, CURLOPT_RETURNTRANSFER, TRUE           );
    curl_setopt( $curl, CURLOPT_FOLLOWLOCATION, TRUE           );
    curl_setopt( $curl, CURLOPT_FAILONERROR,    TRUE           );

    // EXECUTE THE CURL CALL
    $htm = curl_exec($curl);
    $err = curl_errno($curl);
    $inf = curl_getinfo($curl);

    // ON FAILURE
    if (!$htm)
    {
        // PROCESS ERRORS HERE
        if ($error_report)
        {
            echo "CURL FAIL: $url TIMEOUT=$timeout, CURL_ERRNO=$err";
            echo "<pre>\n";
            var_dump($inf);
            echo "</pre>\n";
        }
        curl_close($curl);
        return FALSE;
    }

    // ON SUCCESS
    curl_close($curl);
    return $htm;
}

Open in new window

The return value from the cURL call to Nexmo was this XML string (note call-id):
<?xml version='1.0' encoding='UTF-8' ?>
<tts-submission-response> 
  <call-id>cd1eb02156ebf77eeca46b5416337697</call-id> 
  <to>17033460600</to> 
  <status>0</status>
</tts-submission-response>

Open in new window

The callback URL script is shown here.  As you can see, all it does is record the request data:
<?php // demo/temp_rwinnick_callback.php
error_reporting(E_ALL);
ob_start();
var_dump($_REQUEST);
$req = ob_get_clean();
file_put_contents('temp_rwinnick.txt', $req);

Open in new window

The request variables found in the text file are shown here.  The call-id matches the data in the return from the cURL.  The digits value is what I keyed into my phone.
array(12) {
  ["call-id"]=>
  string(32) "cd1eb02156ebf77eeca46b5416337697"
  ["status"]=>
  string(2) "ok"
  ["call-direction"]=>
  string(3) "out"
  ["call-price"]=>
  string(8) "0.002375"
  ["call-rate"]=>
  string(10) "0.00950000"
  ["call-duration"]=>
  string(2) "15"
  ["to"]=>
  string(11) "17033460600"
  ["digits"]=>
  string(4) "1291"
  ["call-request"]=>
  string(19) "2014/09/17 23:43:00"
  ["network-code"]=>
  string(6) "310004"
  ["call-start"]=>
  string(19) "2014/09/17 23:43:20"
  ["call-end"]=>
  string(19) "2014/09/17 23:43:34"
}

Open in new window

HTH, ~Ray
0
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 40329405
PS... You may want to go to the dashboard and put things back the way they were.  It looks like dashboard settings were not in play with this scenario!
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

Author Comment

by:rwinnick
ID: 40329418
Hi Ray -
It appears that you initiated an outbound call and then 'collected' the caller inputs -
What I am attempting is an "inbound" call which starts with a call to the nexmo number (rather than nexmo initiating the call) and then nexmo using the voice xml script at the 'forward call to voice xml url' to 'control' the call flow and ask for and collect inputs from the caller -
that's why you would need the url changed on the dashboard to where this controlling script would reside -
Evidently this is a less well documented and harder thing to do :-(
I really appreciate the time you have invested in this and if you want to 'quit' at this point I understand -
Richard
0
 

Author Comment

by:rwinnick
ID: 40329437
Hi Ray -
I stumbled over this on stack overflow and it may give us a clue on how to address our issue -

http://stackoverflow.com/questions/15621983/vxml-value-to-php-variable

It appears that you can 'send' the inputs of a vxml script to another 'form' outside or within the same vxml script by using <submit next=.....> as shown in the -1 answer for within the same script -

Not quite sure how to get them into a useable format for a query - if we get them this far :-)

Note: I need to stay in the same script because I need to validate the caller's input (hence the sql) and provide feedback if incorrect and if I send the info to another 'page' it appears that that new script is no longer be able to control call flow (I tested this two 'page' approach awhile back) -

Your thoughts -
Richard
0
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 40329480
OK, thanks. Those additional details are helpful.  I'll look at this again in the morning.
0
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 40330159
OK, let me see if I can describe the process we want to achieve.

1. I will use my phone to initiate a call to the Numbers->United States->1-218-xxx-xxxx number listed in the Nexmo Dashboard.
2. When the call is answered, a request will go to the Numbers->Voice Settings->Forward to VXML URL listed in the Nexmo Dashboard.
3. For starters we can just print the request data and see what Nexmo sends us.
4. With that, we can try using the VXML and hopefully build up the process step-wise.
0
 

Author Comment

by:rwinnick
ID: 40330173
Morning -
Yep that's the flow -
Sounds like a good approach -
The initial code I sent you takes it to the point where the caller's inputs are stored in the 'fields' -
But I think your approach of baby steps will help understand how it all ties together -
Let me know if there is any way I can help -
Really appreciate your persistence :-)
Richard
0
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 40330884
A bit of progress.  Apparently the callback script can get called multiple times for the same telephone call to the 1-218 number.  This seems to mean that the callback script must have multiple functionalities, and that the contents of the request argument names can be used to trigger different actions.
0
 

Author Comment

by:rwinnick
ID: 40330914
By callback script are you referring to the forward to voice xml entry?
Also I am not sure what the request argument names are -

Otherwise -
The way it will work is that a crew calls the 1-218 number and is asked to enter their crew code and a status code for the property they are at -
I want to use this information along with the phone number they are calling from (ANI) to update the information for the property in my database -
This information is then presented on our website -
So this number will get thousands of calls per day with updates from the crews out in the field -
0
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 40331002
OK, here is what I have now.  There is a forward to voice XML script.  This gets fired when I call the 1-218 number.  And there is a separate Status callback URL script.  You can see these in the dashboard.  The latter gets hit when the voice XML script is finished, and may get hit at other times.  I'm trying to discern the timing and order of asynchronous calls from Nexmo now.  Also looking for a way to tag these asynchronous calls in a way that allows us to track all of the data associated with a running of the voice XML script.  Right now I am using PHP mail to send myself a message every time the Nexmo server hits the callback script.  Hopefully this will let me see patterns in its behavior (that should have been documented in the online man pages for the service, but apparently are not documented!)

Documentation here looks helpful:
http://cafe.bevocal.com/docs/vxml/
0
 

Author Comment

by:rwinnick
ID: 40331231
voxeo also has a very complete set of docs
http://help.voxeo.com/go/help/xml.vxml.voicexml
In their documentation I just found what may be an interesting way to 'process' the inputs to validate them without leaving the script using the <data> tag
evidently you can use it to 'reach out' to another document and get a value without leaving the script itself -
you give it a url to go to and it can also carry along some values and returns the xml values found in the other doc -
I haven't been able to get it to work yet - surprise -
you can take a look at the data element's last example getting the weather -

This shouldn't be this hard :-)

Feel free to use my name and ask nexmo questions -

Thanks again for all your time -
We're way more than 500 points :-)
Richard
0
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 40331473
Interestingly I crashed the help.voxeo server earlier today when I visited without accepting cookies.  I'm learning a lot about voice XML, and now I understand why Twilio went with its own XML structure! :-)
0
 

Author Comment

by:rwinnick
ID: 40331556
A couple of years ago I was using twilio for SMS messaging and they weren't very reliable which is  how I found nexmo -
Richard
0
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 40331638
Yes, I seem to remember that the early days of Twilio SMS were a bit rocky.  Have not revisited that since, but I expect it is much more mature.
0
 

Author Comment

by:rwinnick
ID: 40333574
Hi Ray -
Just a quick note to let you know I will be on travel for the next 6 days or so -
I will have email but limited computer access -
Richard
0
 
LVL 108

Accepted Solution

by:
Ray Paseur earned 500 total points
ID: 40333619
OK, here is what I'm using for the XML now.
<?xml version="1.0"?>
<vxml version="2.1">
  <form>
    <field name="numA" type="digits">
      <prompt>Please enter first number, followed by the pound</prompt>
      <filled>
        <prompt>You entered: <value expr="numA" /> , , </prompt>
      </filled>
    </field>
    <field name="numB" type="digits">
      <prompt>Please enter second number, followed by the pound</prompt>
      <filled>
        <prompt>You entered: <value expr="numB" /> , , Thank you , , Goodbye</prompt>
        <submit next="http://iconoun.com/demo/temp_rwinnick_writefile.php" method="post" namelist="numA numB" />
      </filled>
    </field>
  </form>
</vxml>

Open in new window

This works as expected.  A POST is made to the next= attribute script and the script can find both numA and numB in the request.  When this script is called at the 1-218-... number, there are two calls made to the callback script.  The first call contains the "from" number of my cell phone and the "to" number of the 1-218-...  The second call contains the "from" number of the 1-218-...  These calls are within milliseconds of each other and both are made at the end of the call hangup.

The "writefile script identified in the next= attribute just mails the information to me, but since the information is present in the POST request, it can be used in a query to update a database.

What I have not been able to do with any certainty is determine a ligature between the calling phone number (my cell phone) and the numbers.  If we can somehow get the calling number into a VXML script variable, we should be able to send it to the writefile script and find it in the POST request.
0
 

Author Comment

by:rwinnick
ID: 40344845
Hi Ray -
Back from the road trip and with your code and some help from 'above' :-)
It appears that we've gotten it to work using the following code for the voice xml forward to doc -
error_reporting(E_ALL);
// USE THE REQUEST VARIABLE OR A DEFAULT VALUE
$cid = !empty($_REQUEST['nexmo_caller_id']) ? $_REQUEST['nexmo_caller_id'] : "9999";
$doc = <<<EOD
<vxml version="2.1">
  <form>
    <field name="numA" type="digits">
      <prompt>Please enter first number, followed by the pound</prompt>
      <filled>
        <prompt>You entered: <value expr="numA" /> , , </prompt>
      </filled>
    </field>
    <field name="numB" type="digits">
      <prompt>Please enter second number, followed by the pound</prompt>
      <filled>
        <prompt>You entered: <value expr="numB" /> , , and caller id is $cid , , Thank you , , Goodbye</prompt>
        <submit next="http://www.xxxxxxxxx.com/directory/inbound_db_update.php?caller_id=$cid" method="post" namelist="numA numB" /> 
	 </filled>
    </field>
  </form>
</vxml>
EOD;
// WRITE THE XML TO THE BROWSER
echo $doc;

Open in new window

Besides the name list we found that by adding the 'caller id' from the $_request to the URL for the 'receiving' doc on the xxxxx server that it sent both the caller id and the variables numA and B -
The code for the 'receiving' doc is as follows and successfully emailed all the variables -
$caller = !empty($_REQUEST['caller_id']) ? $_REQUEST['caller_id'] : "99999";
$crew = !empty($_REQUEST['numA']) ? $_REQUEST['numA'] : "11111";
$status = !empty($_REQUEST['numB']) ? $_REQUEST['numB'] : "2222";
$to="xxxxxxxxx";
$subject="test email";
$body = "hope we get this and caller is" . $caller ." and crew is " . $crew . "and status is " . $status;
mail($to,$subject,$body);

Open in new window

We also found that if we did not use the 'status callback url' we did not get multiple emails -

I have left your test code's addresses on the number you were working with and simply added another number for our testing -
so if you want to continue 'fooling' with this just let me know when to change things back -

I really really really appreciate your patience and tenacity on this issue and 500 points doesn't make a dent in the effort you put into this -

Is there any way I can add points or whatever to reflect your effort in helping me with this -

I will keep you posted on future progress but we have a strong foundation now and with luck the rest will be some 'simple' PHP code :-)

Thanks again and as always Experts Exchange ROCKS !!

Richard
0
 

Author Closing Comment

by:rwinnick
ID: 40352390
thanks for all the help
Richard
0
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 40352396
Thanks for the points -- it's a great question!
0

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
The viewer will learn how to dynamically set the form action using jQuery.
The viewer will learn how to count occurrences of each item in an array.

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now