Send SMS from MySQL on your Website

You can manage databases with SQL statements. You can send SQL statements through PHP. This makes you possible to manipulate database tables. For example, PHP can add table records of SMS messages to send, but it also can read records of incoming messages.

Download: MySQL_PHP_Example.zip

The PHP solution is intended for web developers with basic knowledge in PHP and SQL. You can download the PHP source code, so you can do this step-by-step tutorial containing basic instruction how to implement the solution.

Why is SMS Gateway with a database and PHP a useful combo

This is a useful PHP solution for

• adding SMS functions to your website.
• adding SMS functions to your corporate intranet.
• creating automated SMS notifications.
• increasing website security by adding SMS login.

Requirements

You will need to host an Ozeki SMS Gateway, a webserver and a MySQL server. You can host this function from the same computer or from two machines as you can see in the table below:

Ozeki SMS Gateway can be obtained by opening the download page: Download Ozeki SMS Gateway!

How does it work

Before you start using this PHP application, you should install Ozeki SMS Gateway and a MySQL Server on your computer. You can use Windows or Linux. It is important to choose a hardware or software solution for SMS messaging. As a hardware solution a mobile phone or GSM modem is advised and for a software solution you can use your IP SMS service provider over the internet.

The PHP example you can find in this tutorial is capable to read records or insert new table records. This is very useful when reading incoming messages or writing outgoing messages. If you would like to see how the solution works, please look at Figure 1.

You can see the 'Internet User' in the diagram, who will actually send and receive SMS messages through a browser. The internet user is connected to a PHP enabled web server, which is capable to manage databases on the SQL Server and these databases belong to Ozeki SMS Gateway. In the final step Ozeki SMS Gateway sends the message to a mobile phone through the GSM network.

The internet user can access the internet from anywhere. The only thing that matters is if they know the IP address or the URL of the computer running the PHP server and if they are authorized to log into the MySQL database, so they can insert the desired SMS message record which will be selected later on by Ozeki SMS Gateway to be sent out to the recipient's cell phone by using the GSM network.

This workflow also works in the opposite direction. Where Ozeki SMS Gateway receives the SMS message from the GSM network and saves it in the MySQL database, so the PHP server can read it and show it on the internet user's screen.

How to create a MySQL database

	

CREATE DATABASE ozekisms;

USE ozekisms;

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

CREATE TABLE ozekimessageout
(
id int(11) NOT NULL auto_increment,
sender varchar(30) default NULL,
receiver varchar(30) default NULL,
msg varchar(1024) default NULL,
senttime varchar(100) default NULL,
receivedtime varchar(100) default NULL,
status varchar(20) default NULL,
msgtype varchar(160) default NULL,
operator varchar(100),
PRIMARY KEY (id)
);

GRANT insert,update,select,delete 
on ozekisms.* to sqluser@localhost
IDENTIFIED BY 'abc123';

Figure 2 - Creating tables in the structure required by Ozeki SMS Gateway

How to create a Database User in Ozeki SMS Gateway

Step 1: Please open Ozeki 10 in a web browser
Step 2: Select SMS Gateway application from Ozeki 10's integrated desktop
Step 3: Create the Database User in SMS Gateway by following the instructions.

How to use the code

The PHP script

Here you can find out more details of the downloadable sample script.

The script must know the user credentials to your MySQL database. The main role of the script is to insert the new message in your outgoing message table. This table is called 'ozekimessageout' in the current example.

Ozeki SMS Gateway's Database User periodically checks the table for new records and if the status of the message is 'Send', it will try to send it. In case of success the Database User will change the message's status to 'Sent'.

This is the structure of the source code:

sqlsmshandling.php:

It contains message builder ASP elements. For example, labels, text boxes and the 'Send' button. The 'Send' button calls the 'connectToDatabase()', 'insertMessage(...)' and 'closeConnection()' functions from sqlsmshandling_functions.php. sqlsmshandling.php handles not just SMS messages, but exceptions as well. For example, empty fields.

sqlsmshandling_functions.php:

This PHP file contains all the internal functions used by the 3 files.

- connectToDatabase(): Connects to a database of the MySQL server.
- closeConnection(): Closes the connection, which was created in connectToDatabase().
- insertMessage(recipient, messageType, messageText): Runs INSERT statement on the database. This statement inserts the message record into the outgoing message table.
- showOutgoingMessagesInTable(): Selects message from the outgoing messages SQL table and builds a HTML table from them in your web browser.
- showIncomingMessagesInTable(): Selects message from the incoming messages SQL table and builds a HTML table from them in your web browser.

sqlsmshandling_inoutmessages.php:

It builds HTML tables. It uses connectToDatabase(), showOutgoingMessagesInTable(), showIncomingMessagesInTable() and closeConnection() function for re-generate tables. These functions are available in sqlsmshandling_functions.php.

The detailed description of the process in Figure 1 above:

Step 1: Create input form

sqlsmshandling.php creates a form (Figure 3) to request SMS data from the user. The form consists of Labels and Textboxes, plus a 'Send' button. The internet user fills the recipient and message fields and clicks 'Send'.

sqlsmshandling.php

...
<form action="" method="post">
<table border="0" align="centre">
    <tr>
        <td colspan="2" align="centre">
            <font style="font-weight: bold; font-size: 16px;">Compose message</font>
            <br /><br />
        </td>
    </tr>
    <tr>
        <td valign="top">Recipient: </td>
        <td>
            <textarea name="textAreaRecipient" cols="40" rows="2">...</textarea>
        </td>
    </tr>
    <tr>
        <td valign="top">Message text: </td>
        <td>
            <textarea name="textAreaMessage" cols="40" rows="10">...</textarea>
        </td>
    </tr>
    <tr>
        <td colspan="2" align="centre">
            <input type="submit" value="Send">
        </td>
    </tr>
    <tr><td colspan='2' align='centre'>
    ...
    </td></tr>
</table>
</form>
...

Figure 3 - Builds HTML form

If the internet user clicks 'Send' the following script runs:

...

<?php

    if (isset($_POST["textAreaRecipient"]) && $_POST["textAreaRecipient"] == "")
    {
        echo "Recipient field mustn't be empty!";
    }
    else if (isset($_POST["textAreaRecipient"]) && $_POST["textAreaRecipient"] != "")
    {
    try
    {
        connectToDatabase();
        if (insertMessage($_POST["textAreaRecipient"],"SMS:TEXT",$_POST["textAreaMessage"]))
        {
            echo "Insert was successful!";
        }
        closeConnection();
    }
    catch (Exception $exc)
    {
        echo "Error: " . $exc->getMessage();
    }
}
?>
...

Figure 4 - It logs in and inserts the message to the database table by calling insertMessage(...) function

Step 2: Processing data in the HTML textboxes

If both textboxes are filled, the data will be processed and inserted in the SQL database's outgoing message table. The insertMessage(…) function (Figure 5) can achieve this.

Keep in mind that the message record is going to be inserted in the MySQL database by the script which has previously logged in (Figure 4 above).

sqlsmshandling_functions.php

...
function insertMessage ($recipient, $messageType, $messageText)
{
    $query = "insert into ozekimessageout (receiver,msgtype,msg,status) ";
    $query .= "values ('" . $recipient . "', 
    	'" . $messageType . "', '" . $messageText . "', 'send');";
    $result = mysql_query($query);
    if (!$result)
    {
        echo (mysql_error() . "
");
        return false;
    }

    return true;
}
...

Figure 5 - insertMessage(...) function

Step 3: Creating outgoing and incoming message tables

If you press F5 or click refresh in your browser, the outgoing and incoming tables will be updated by reading information from the MySQL database. As an example you can see showOutgoingMessagesInTable() below (Figure 6).

sqlsmshandling_functions.php

function showOutgoingMessagesInTable()
{
    $query = "select id,sender,receiver,senttime,receivedtime,operator,status,msgtype,
    			msg from ozekimessageout;";
    $result = mysql_query($query);
    if (!$result)
    {
        echo (mysql_error() . "<br>");
        return false;
    }

    try
    {
        echo "<table border='1'>";
        echo "<tr><td>ID</td><td>Sender</td><td>Receiver</td>
        <td>Sent time</td><td>Received time</td><td>Operator</td>";
        echo "<td>Status</td><td>
        Message type</td><td>Message text</td></tr>";
        while ($row = mysql_fetch_assoc($result))
        {
            echo "<tr>";

            echo "<td>" . $row["id"] . "</td>";
            echo "<td>" . $row["sender"] . "</td>";
            echo "<td>" . $row["receiver"] . "</td>";
            echo "<td>" . $row["senttime"] . "</td>";
            echo "<td>" . $row["receivedtime"] . "</td>";
            echo "<td>" . $row["operator"] . "</td>";
            echo "<td>" . $row["status"] . "</td>";
            echo "<td>" . $row["msgtype"] . "</td>";
            echo "<td>" . $row["msg"] . "</td>";

            echo "</tr>";
        }
        echo "</table>";
        mysql_free_result($result);
    }
    catch (Exception $exc)
    {
        echo (mysql_error() . "<br>");
        return false;
    }

    return true;
}
...

Figure 6 - showOutgoingMessagesInTable() function: It generates a HTML table from outgoing messages

Frequently asked questions

Question: Can I send another type of message than 'SMS:TEXT' ?
Answer: Yes. For example, a Wap push message when calling the insertMessage(...) function (Figure 7).

insertMessage ($_POST["textAreaRecipient"], "SMS:WAPPUSH", $_POST["textAreaMessage"])

<si>
<indication href="http://target_address" action="signal-high">
text of description
</indication>
</si>

Figure 7 - SMS:TEXT changed to SMS:WAPPUSH

The 'action' parameter of the 'indication' tag can be one of the following:
signal-high, signal-medium, signal-low, signal-none, signal-delete.

Question: Can the PHP have enabled HTTP server run on a different computer from the MySQL server and Ozeki SMS Gateway?
Answer: Yes, it can. In PHP script please set the IP address and actual login credentials to your MySQL database.

Question: Can I show the recipient the phone number of the sender?
Answer: Yes, you can. Please create textbox for the sender's phone number on modify the insertMessage(...) function (Figure 8). Keep in mind that this only works if you have an IP SMS service provider connection.

function insertMessage($sender, $recipient, $messageType, $messageText)
{
...
$query = "insert into ozekimessageout (sender,receiver,msgtype,msg,status) ";
$query .= "values ('".$sender."','".$recipient."',".
"'".$messageType."','".$messageText."','send');";
$result = mysql_query($query);
...
}

Summary

This article is about a solution to manage SMS messaging with MySQL using the Ozeki SMS Gateway and how to set up this system. If you implement this solution by following the step-by-step tutorial, you will be able to control your messaging system with SQL statements through your PHP application. Besides the organized and simple SMS messaging this addition offers there is one more great advantage of it, which is the advanced website security you can reach by adding SMS login.

The Ozeki website is full of articles I recommend reading, take the chance, and visit these sites. Let's take first the guide about how to use your website to send SMS messages.

Download the Ozeki SMS Gateway now and let us help you to achieve your goals!