We've Moved! Please visit our new and improved forum over at our new portal: https://portal.plumvoice.com/hc/en-us/community/topics

Passing Data to my SQL server

Questions and answers about IVR programming for Plum DEV

Moderators: admin, support

Post Reply
aghuber11
Posts: 14
Joined: Tue Jun 21, 2016 1:02 pm

Passing Data to my SQL server

Post by aghuber11 »

Hello,

I'm having trouble passing information into my SQL database using php. The script works fine with my web form, but doesnt seem to pass information to my database when i use it with plumvoice. I'm trying to configure the tutorial just to make sure it will interface with my database.

the table has two specific receivers

license_number VARCHAR (30)
and
request_type VARCHAR(30)

Here is my start.php

Code: Select all

<?php
  header("Content-type: text/xml");
  echo "<?xml version=\"1.0\"?>\n";
?>

<vxml version="2.0">
  <var name="license_number"/>
  <var name="request_type"/>
  <form>
    <field name="license" type="digits">
      <prompt>
        Please enter your license number
      </prompt>
      <filled>
        <assign name="license_number" expr="license"/>
      </filled>
    </field>
    <field name="request">
      <grammar type="application/srgs+xml" mode="dtmf" root="choice" maxdigits="1">
        <rule id="choice">
          <one-of>
            <item>1</item>
            <item>2</item>
          </one-of>
        </rule>
      </grammar>
      <prompt>
        To renew your license, press 1. To request a new license plate, press 2.
      </prompt>
      <filled>
        <assign name="request_type" expr="request"/>
        <submit next="process_request.php" method="post" namelist="
        license_number request_type" fetchtimeout="60s"/>
      </filled>
    </field>
  </form>
</vxml>
Here is my process_request.php page.

Code: Select all

<?php
  header("Content-type: text/xml");
  echo("<?xml version=\"1.0\"?>\n");

  // get the variables posted to this script
  $name = $_POST['license_number'];
  $license = $_POST['request_type'];
  // here we could process the data by entering the information into a database or calling a webservice
  $con = mysql_connect("provider","database","password");
  if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }
  mysql_select_db("db629980542", $con);
    $sql="INSERT INTO request (license_number, request_type)
    VALUES
    ('$license','$name')";
?>
<vxml version="2.0">
  <form>
    <block>
      <prompt>
        Thank you. We will process your request and get back to you shortly. Goodbye.
      </prompt>
      <exit/>
    </block>
  </form>
</vxml>
what am i doing wrong?

support
Posts: 3632
Joined: Mon Jun 02, 2003 3:47 pm
Location: Boston, MA
Contact:

Re: Passing Data to my SQL server

Post by support »

Hi,

You were so very close! You just forgot to execute your query. You just need to execute the query after you define your query (see after the "// ADDED THIS LINE" comment below):

Code: Select all

<?php
  header("Content-type: text/xml");
  echo("<?xml version=\"1.0\"?>\n");

  // get the variables posted to this script
  $name = $_POST['license_number'];
  $license = $_POST['request_type'];
  // here we could process the data by entering the information into a database or calling a webservice
  $con = mysql_connect("provider","database","password");
  if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }
  mysql_select_db("db629980542", $con);
    $sql="INSERT INTO request (license_number, request_type)
    VALUES
    ('$license','$name')";
  // ADDED THIS LINE
  mysql_query($sql);
?>
<vxml version="2.0">
  <form>
    <block>
      <prompt>
        Thank you. We will process your request and get back to you shortly. Goodbye.
      </prompt>
      <exit/>
    </block>
  </form>
</vxml>
We tested you code as-is with that additional line and did see the record show up in our database.

I know this is probably just your basis to get a working prototype and then you're planning to revise this a bit, but I just wanted to point out one thing about your script. In general, if any non-vxml output is generated, for instance:
die('Could not connect: ' . mysql_error());
would output that string and the mysql error string, the platform will throw a error.badfetch error, as the first thing it got from this script was text and not valid vxml. So you just want to be careful about suppressing output from your php scripts for the sake of your caller experience and ensuring only valid vxml ever makes it to the platform. You could do something like the following to ensure no matter what happens that your caller will always hear the prompt played after your php code execution is completed. This is a very common case to handle any php errors and dynamically generate what the caller hears:

Code: Select all

<?php
  header("Content-type: text/xml");
  echo("<?xml version=\"1.0\"?>\n");
  // keep a flag of the outcome
  $result = true;
  // get the variables posted to this script
  if (isset($_POST['license_number'], $_POST['request_type'])) {
    $name = $_POST['license_number'];
    $license = $_POST['request_type'];
    // here we could process the data by entering the information into a database or calling a webservice
    $con = mysql_connect("provider","database","password");
    if (!$con) {
      // either log or escape the error with a comment here
      $result = false;
    } else {
      if (!mysql_select_db("db629980542", $con)) {
        // either log or escape the error with a comment here
        $result = false;
      } else {
          $sql="INSERT INTO request (license_number, request_type)
          VALUES
          ('$license','$name')";
          if (!mysql_query($sql)) {
            // either log or escape the error with a comment here
            $result = false;
          }
      }
    }
  } else {
    $result = false;
  }
?>
<vxml version="2.0">
  <form>
    <block>
      <?php if ($result) { ?>
      <prompt>
        Thank you. We will process your request and get back to you shortly. Goodbye.
      </prompt>
      <?php } else { ?>
      <prompt>
        Sorry, there was an error processing your request. Please try again later.
      </prompt>
      <?php } ?>
      <exit/>
    </block>
  </form>
</vxml>

Regards,
Plum Support

aghuber11
Posts: 14
Joined: Tue Jun 21, 2016 1:02 pm

Re: Passing Data to my SQL server

Post by aghuber11 »

Wow i feel silly for missing something so simple. Thank you for reviewing this and replying so quickly. I really appreciate your help!

Works as intended now

Post Reply