PHP and MySQL / OZEKI SMS
/Send messages from a WWW pages/

PHP and MySQL make up what must be the best combination for data-driven Web sites on the planet. If you are using PHP and MySQL on your website and would like to integrate SMS messaging into your application, follow the steps below. The following steps work on Linux, Soliaris, MacOSX and Windows platforms.

sending messages using a mysql database
Figure 1 - Sending messages from an website using a MySQL database

In this example we assume that you have an Apache webserver with PHP support configured with a MySQL database server on a Linux machine. You would like send SMS messages with the Ozeki Sms Server, which is installed on another PC running Microsoft Windows.

In this case all the SMS sending and receiving tasks can be done by using MySQL SQL statements in your PHP script.

All you have to do is create the appropriate database tables, install a MyODBC driver on the Windows PC running the Ozeki SMS Engine and make some configuration. The MyODBC driver will enable the OZEKI SMS Server to insert the incoming SMS messages into the remote database and check this database periodically for outgoing messages.

Here is what you should do to have this worked:

On your UNIX (Linux) server:

Step 1 - Log in to your MySQL database server and attach to the database you are using.

[gyula@linux]$mysql -u john
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 60 to server version: 3.23.32

Type 'help;' or '\h' for help. Type '\c' to clear the buffer
mysql> use mybuddy
Reading table information for completion of 
table and column names
You can turn off this feature to get a 
quicker startup with -A

Database changed mysql>
	

Code 1 - Logging into the MySQL database

Step 2 - Create the database tables for sending and receiving SMS messages

mysql>CREATE TABLE ozekismsin (
id int(11) NOT NULL auto_increment,
sender varchar(30) default NULL,
receiver varchar(30) default NULL,
msg varchar(160) default NULL,
senttime varchar(100) default NULL,
receivedtime varchar(100) default NULL,
operator varchar(100) default NULL,
PRIMARY KEY (id)
);

Query OK, 0 rows affected (0.08 sec)

mysql>CREATE TABLE ozekismsout (
id int(11) NOT NULL auto_increment,
sender varchar(30) default NULL,
receiver varchar(30) default NULL,
msg varchar(160) default NULL,
senttime varchar(100) default NULL,
receivedtime varchar(100) default NULL,
reference varchar(10) default NULL,
status varchar(20) default NULL,
operator varchar(100) default NULL,
PRIMARY KEY (id)
);
	

Code 2 - Creating the ozekismsout and ozekismsin tables

After you have created the database table, don't forget to create a user with appropriate privileges. This user has to be able to log in to the database from the Windows PC and should have privileges for selecting and inserting records into the newly created tables.

On your Windows PC

Step 3 - Download the MyODBC database driver. You can download them from http://www.mysql.com/downloads/api-myodbc-2.50.html or from our server:

Step 4 - Install the downloaded MyODBC database driver by executing setup.exe. You need winzip to unpack the downloaded file

Step 5 - Configure your ODBC data source. Go to Control Panel and then click on Administrative Tools and then click on Data Source (ODBC)

odbc data source administrator user dsn tab
Figure 2 - Selecting the user DSN tab in the ODBC Source Administrator

Click on User DSN Tab (by default it is on that) abd click Add as shown in the figure above.

how to create a new data source in odbc data source administrator
Figure 3 - How to create a new datasource in the ODBC Source Administrator

Scroll down and select the MySQL ODBC Driver.

how to set the dns configurations in odbc data source administrator
Figure 4 - How to set the DNS configurations

In the MySQL ODBC driver configuration enter the details as seen in the figure (in the database name enter your database name) and after you fill in all the proper fields click on the Test Data Source to check if you have properly configured your Database settings.

Step 6 - Install the OZEKI SMS Server. You can read the installation instructions at the installation manual.

Step 7 - After the installation activate the Database plugin:

how to intsall plugins in the ozeki sms
Figure 5 - How to intsall plugins in the Ozeki SMS

Select Install plugins from the menu

how to intall the ozeki sms database plugin
Figure 6 - Intall Ozeki SMS database plugin

Select Database from the drop down and hit the Install button. After the plugin is activated you can configure it.

Step 8 - Configure the database connection in Ozeki SMS You can do this by clicking on the configure button at the plugin activation form

how to configure the ozeki sms database
Figure 7 - Ozeki SMS database configuration

On the Database Settings form, clik on the Build database connection button. This will bring up the connection selection dialog.

how to set connection properties in ozeki sms
Figure 8 - How to set connection properties in Ozeki SMS

On the connection tab, you can specify the ODBC datasource: mybuddy, and you can supply the login name and the password:

ozeki sms connection properties
Figure 9 - Ozeki SMS connection properties

After this is done, make sure you test the connection by clicking on the Test Connection button located in the lower right hand corner of the form. Press OK to finalize your settings.

On your UNIX (Linux) server:

Step 7 - Create a PHP script that will be able to send and receive SMS messages. This can be done by issuing SQL queries to the MySQL database. The OZEKI SMS server will check the ozekismsout database table periodically for outgoing SMS messages and place the incoming SMS messages into the ozekismsin database table.

Here are two example PHP scripts that will do the job:

//SENDING SMS MESSAGES
<?
# Connect to the database
mysql_connect('localhost','john','pass');

Function send_sms($phone,$message) {
$sql = "insert into ozekismsout (receiver,msg,status) values ('$phone','$msg','send')";
mysql('dbname',$sql);
};

send_sms("+362012234567","Hello World!");
?>
	

Code 3 - Sending SMS messages

//RECEIVING SMS MESSAGES
<?
# Connect to the database
mysql_connect('localhost','john','pass');

$sql = "select sender,msg from ozekismsin";
$res = mysql('dbname',$sql);
$count = mysql_num_rows($res);

for ($x=0;$x<$count;$x++) {
list ($num,$message) =mysql_fetch_row($res);
echo "$num - $message 
"; } ?>

Code 4 - Reciving SMS messages

When the appropriate scripts are created you are ready to send and receive sms messages from your webpages.

The great thing about this approach is that it is very convenient to use and it can survive network errors. If, for example, the network connection between the database server and the SMS server failes for a period of time, all the incoming and ougoing messages are saved.

When the connection resumes they are sent or inserted to the database. You can monitor ODBC events with the help of the event monitor window of the Ozeki Sms Server

Related pages: