This project is read-only.

How to build an autodialer with PHP using your MySQL database

Introduction

In the following article I will show you an example of creating a simple autodialer, using MySQL tables and PHP. The autodialer will call multiple phones simultaneously, telling them the text you have written in your code. I assume you are in possession of the neccessary PHP and SQL knowledge for this example.

Background

We are going to use three tools in the example. The first is the application we write, to record the calling plans. The second is the Database that contains the neccessary data for the calls and lastly the PBX, that will get the needed infromation from the database, start the calls and tell the previously set up message to the called person.

Preparing your Database Server

We are going to use Wampserver for the database. We will need three tables. The first table stores information about the incoming calls (callerID, scriptID, status of the calls and starttime), and the second, which stores almost the same information as the first, only about the outgoing calls. The third table stores the messages that should be read. Look at the ozmlin table, which you can create in the phpmyadmin panel of wampserver after creating a new database named „ozekipbx” and having run the following MySQL script in it:

Code Example 1 – Create ozmlin table
CREATE TABLE `ozmlin` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `callerid` varchar(40) NOT NULL,
  `scriptid` int(10) NOT NULL,
  `duration` int(10) DEFAULT '0',
  `status` varchar(40) DEFAULT NULL,
  `recordurl` varchar(150) DEFAULT NULL,
  `starttime` datetime DEFAULT NULL,
  PRIMARY KEY (`id`))
ENGINE=InnoDB AUTO_INCREMENT=0
DEFAULT CHARSET=utf8;

The ozmlin table contains:
  • Callerid: This is the number of the called phone.
  • Status: As its name says it tells the status of the call (Calling, Completed, Busy etc.)
  • Duration: the duration of the call in seconds
  • ScriptID: When the call is answered, that OzML script will be executed, which has its id set up
  • RecordUrl: This shows if you can listen in the conversation if the call is being recorded
  • StartTime: Shows the starting time of the call

The second table is generated with a similar structure:

Code Example 2 – Create ozmlout table
CREATE TABLE `ozmlout` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `dialednumber` varchar(40) NOT NULL,
  `status` varchar(40) DEFAULT NULL,
  `duration` int(10) DEFAULT NULL,
  `scriptid` int(10) DEFAULT NULL,
  `recordurl` varchar(150) DEFAULT NULL,
  `starttime` datetime DEFAULT NULL,
.. `ScheduledTime` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)) 
ENGINE=InnoDB AUTO_INCREMENT=0
DEFAULT CHARSET=utf8;

This table has one more column, which is:
  • ScheduledTime: We use this in the code, for setting up the date of the calls.
Finally let’s generate the last table, which will contain the OzML scripts. The content of this table will be executed after the call is answered. An ID belongs to all of the scripts, and we refer to these in the previously generated tables. We will store the concrete OzML scripts in this table, but in another column. These scripts will contain the text, that will be read for the caller. The table is created by running the following MySQL script:

Code Example 3 – Create ozmlscripts table
CREATE TABLE `ozmlscripts` (
  `scriptid` int(10) NOT NULL AUTO_INCREMENT,
  `ozml` varchar(10000) NOT NULL,
  PRIMARY KEY (`scriptid`))
ENGINE=InnoDB AUTO_INCREMENT=0
DEFAULT CHARSET=utf8;

Configuring the PBX

The next step after you have created the database is to configure the PBX. We are going to use the Ozeki Phone System XE as a PBX. You can download it from www.ozekiphone.com. Of course this is not the only PBX, but for this example we are going to use it, because it is easy to use and to set up the different components, moreover it is reliable. As next step create the SQL OzML Extension:

1. Download and install the Ozeki Phone System XE on your PC

2. Open the following address: http://localhost:7777 in your browser and log in with the username/password that you have set up meanwhile it was installed.

3. On the Home page of the PBX, click on the „Add” button inside the Extensions tab, which you can find near the top right corner.

4. Select SQL OzML on the next page and click on the „Install” button next to it.

5. Give the neccessary datas on the Database Connenction tab
  • Select „Odbc” for Data source
  • Copy the following text into the Connection string:
    • Driver={MySQL ODBC 5.2 ANSI Driver};Server=localhost;Database=ozekipbx;User=root;Password=;Option=4;
  • In the Outgoing calls tab:
    • Set the Max simultaneous calls to 50
    • Copy the following text into the Query template for outgoing call requests:
      • SELECT id, dialednumber, scriptid FROM ozmlout WHERE Status='call' AND scheduledtime < NOW()
6. Press OK

After you have finished configurating the OzML extension you will notice that it isn’t connected to the database, yet. It is beacuse you still need to download and install the MySQL ODBC, in which you can set up the connection between the database and the PBX. You can download the ODBC from this link: http://dev.mysql.com/downloads/connector/odbc/

The following link shows you a manual of installing and setting up the ODBC:
http://ozekiphone.com/how-to-install-myodbc-221.html

There are two differences in the manual and the actual setup we need:
  • 1. On Figure 8, you have to select MySQL ODBC 5.2 ANSI Driver
  • 2. On Figure 9, you don’t have to set up a password, because the database doesn’t have one.
Our next step is going to be to connect two Softphones to the PBX which will recieve the calls. We are going to use the X-Lite Softphone for one of the phones because it is simple, for the other you should use another Softphone.

Connect SoftPhone to PBX

1. Start with downloading, then installing the X-Lite softphone on your PC
(http://www.counterpath.com/x-lite.html)
2. After launching select the Softphone menu and in it the Account Settings.
3. Set the Account name, User ID, Password, Display name and Authorization name to 500 and the Domain to your own IP adress.
4. Press OK.

If you have done all the settings correctly, you will see the appearing Softphone 500 on the surface of Ozeki Phone System XE Extensions.
You should configure the other Softphone with number 501, based on the previous example.

Using the Code

You are ready to start working with your devices after finishing the setup of the database, PBX and softphones.
First create an HTML form, that will send the neccessary data into the database with:
  • The telephone numbers that will recieve a call
  • The exact time of the calls
  • The OzML script, that will be executed after answering the call
You can do this with the following Form/code:

Code Example 4 – Dial Form
<form action="http://localhost/autodialer.php" method="post">
	<label>dialednumber1:</label>  <input name="dialednumber1" type="text" value="500"/>
	<label>dialednumber2:</label>  <input name="dialednumber2" type="text" value="501"/><br />
	<label>status:</label> <input name="status" type="text" value="call"/><br />
	<label>script ID:</label> <input name="scriptid" type="number" value="1"/><br />
	<label>scheduleddate:</label> <input name="scheduleddate" type="date" value="2013-10-09"/><br />
	<label>scheduledtime:</label> <input name="scheduledtime" type="time" value="13:45"/><br />
	<input id="ResultDiv" type="hidden" value="resultDivOfInsert" />
	<input type="submit" />	
</form>

You can see that the autodialer.php file will be called in this Form. What this php file is going to do is to paste the given calling data to the database. This php file looks like this:

Code Example 5 – Insert calling datas into ozmlout table
<?php
if(isset($_POST['dialednumber1']) && isset($_POST['status']) && isset($_POST['scriptid']) && isset($_POST['scheduledtime']))
  {
   	$con = mysqli_connect("127.0.0.1","root","","ozekipbx");

	//check the state of the connection
	if (mysqli_connect_errno())
	{
		echo "Failed to connect to MySQL: " . mysqli_connect_error();
		exit();
	}

	//insert data
	$state = mysqli_query($con,"INSERT INTO ozmlout (id, dialednumber, status, duration, scriptid, recordurl, scheduledtime)
								VALUES (NULL, '".$_POST['dialednumber1']."', '".$_POST['status']."', NULL, ".$_POST['scriptid'].", NULL, '".$_POST['scheduleddate']." ".$_POST['scheduledtime']."')");
	$state = mysqli_query($con,"INSERT INTO ozmlout (id, dialednumber, status, duration, scriptid, recordurl, scheduledtime)
								VALUES (NULL, '".$_POST['dialednumber2']."', '".$_POST['status']."', NULL, ".$_POST['scriptid'].", NULL, '".$_POST['scheduleddate']." ".$_POST['scheduledtime']."')");
	mysqli_close($con);
	
	if ($state == 1)
		echo "Success";
	else
		echo "Unsuccess";
  }
else
	echo "Unsuccess";
?>

The php code tries to connect to the database with the default settings. You can change these settings to your own if they are different. After connecting successfully to the database, the php code will execute an insert on the ozmlout table, by running the the mysqli_query command.

As you can see in the code it gives a feedback of success after a successful insertion or a feedback of unsuccess if something went wrong. After you have created the html and php files, that you saw above, you have to copy them to the:
  • c:\wamp\www\ folder.
This way they will be on your webserver and you are going to be able to open them in a browser with this address: http://localhost/dial.html

The following code is an example for the OzML Script:

Code Example 6 – OzML Command for Speak a text to the called user
<Response>
	<UserInput Timeout="2" Repeat="true">
	<InitialCommands>
		<Speak>
			What you are hearing now is an automatic test message
		</Speak>
	</InitialCommands>
	</UserInput>
</Response>

This is an example of the simple „Speak” command. There are a lot of other commands, too, in the Ozeki Phone System XE.

Using the Program

Everything is set up and ready to be used, so the only thing left is to try out what we made.
Make sure that the following programs are running:
  • Wampserver: phpmyadmin
  • Ozeki Phone System XE
  • X-Lite Softphone
If they are running, the next step is to create an OzML script to be executed after answering a call. For this we have to open this URL: http://localhost/phpmyadmin

Log in, select the ozekipbx database, then select the ozmlscripts table and insert a new row. In this row we should add the scriptid and the OzML code we want to execute after asnwering the calls.

Type in the phone numbers you would like to recieve calls. In our case they are going to be the phone number 500 and 501. These numbers were configured in the X-Lite and another Softphone previously. Add the date and time of the call. When that time comes, the SQL OzML Extension starts the calls.

Type in the phone numbers you would like to recieve calls to. In our case they are going to be the phone numbers of 500 and 501. These two numbers were configured in the X-Lite and another Softphone previously. Add the date and time of the calls. When that time comes, the SQL OzML Extension starts the calls.

Lastly you have to give the Script ID that you have created before. When the phone is picked up, the script will be executed.
Submit the datas. If the insertion was successful you will see a message of „success”. Otherwise it will show a message of „unsuccess”. In this case please look on the autodialer.php file and check that the login data is correct or not.

After all of this the OzML script should be in the database, along with the phone numbers waiting to be called. If everything works perfectly you will recieve calls in the time you gave and hear the text you have written in the OzML script. If nothing happens,that means, something went wrong and you have to check the log of the SQL OzML Extension. It will help you to find out what the problem is. The easiest way to reach it is by clicking on the Open button, which is next to that SQL OzML Extension that you have created previously. You can find it on the Home Page. If it still hasn’t helped you enough choose the PBX Features/Logs menu in the above menu.

With this you are ready with your own Autodialer application. If you would like to, you can make any further developments on the application made here.

Summary

In this article you have learned, how to create a simple autodialer application with the help of MySQL database and OzML scripts. For this you had to learned how to configurate the phpMyAdmin, the MySQL ODBC, the Softphones and the Ozeki Phone System XE.

References

  • You are able to download the Wampserver for free, which contains the MySQL, and with the help of it you can run your written application, too, on your own PC: http://www.wampserver.com/en/
  • You are able to download the Ozeki Phone System XE, and you can find more help on: Ozekiphone.com

Last edited Oct 11, 2013 at 1:44 PM by simonrobert, version 7