Birthday greeting SMS service with MySQL

1. INTRODUCTION

This documentation provides information on how to setup Ozeki NG - SMS Gateway if you wish to send birthday greeting SMS messages with the help of MySQL database server.

Our demand is to send birthday greeting SMS messages to your employees, colleagues, customers, friends, etc. By setting up this system you do not have to keep all birthday dates in mind, as the system will automatically keep track of these and will remind you in time. A birthday greeting can either be an endearment to your friends and colleagues or a marketing tweak to your customers.

In order to set up the system, read carefully and follow the instructions provided on this page. You can find the structure of the documentation listed below:

0. Video tutorials
1. Introduction
2. Requirements
3. Process
4. Configuration steps
5. Conclusion

Please find the video tutorials of this configuration process on the top of the page.

2. REQUIREMENTS

The prerequisites for launching a birthday greeting service are the following:

1. Ozeki NG - SMS Gateway software
2. MySQL database server
3. Mobile network connection

It is assumed that a working SMS service provider is already configured in Ozeki NG - SMS Gateway and a database user is already setup. The database user is connected to the MySQL database.

On the diagram below you can see the system architecture of the birthday greeting SMS service (Figure 2).

system architecture
Figure 2 - System architecture

3. PROCESS

The following steps describe how the birthday greeting SMS service system works:

1. Ozeki NG applies a query to the database table regarding the people who have their birthday the current day and have not yet received a birthday SMS message
2. The database submits this information to Ozeki NG - SMS Gateway
3. The software sends the birthday message to the SMSC
4. The people having their birthday receive the birthday SMS message to their mobile phones

4. CONFIGURATION STEPS

To setup a Birthday reminder service we use MySQL database server with MyODBC Database driver.

Step 1.) Configuring MySQL<

To setup a birthday reminder service a database table is needed that will hold birthday information. Here is the table definition: 

CREATE TABLE birthday (
        id int auto_increment primary key,
        customername varchar (100),
        customerphone varchar(100),
        birthdate date,
        lastnotified date,
        status varchar(30)
);

After the table has been defined, create it in the database server, using MySQL Command Line Client. For this, open MySQL Command Line Client and insert the table definition.

Now put some test data into this database table. 

INSERT INTO birthday (customername, customerphone, birthdate) VALUES ('Elisabeth', '+36301111111', '2009-08-04');
INSERT INTO birthday (customername, customerphone, birthdate) VALUES ('George', '+36202222222', '2009-09-04');

Copy this test data and paste it into the MySQL Command Line Client table.

Step 2.) Configuring Ozeki - SMS Gateway

Configure Ozeki NG - SMS Gateway to send birthday reminders based on the SQL commands in the configuration form of the database user.

The first command is used to pick up messages: 

SELECT id, '', customerphone, 'Happy Birthday' FROM birthday
WHERE month(current_date)=month(birthdate) AND day(current_date)=day(birthdate)
AND ( NOT lastnotified=current_date) OR lastnotified is null;

Copy this command and insert it into the 'Polling' field of the 'SQL for sending' tab within the Ozeki NG - SMS Gateway 'birthday (sql)' configuration form.

The second command is used to update the "lastnotified" information in the birthday table.

UPDATE birthday SET status='sending',
lastnotified=current_date WHERE id='$id';

Copy this update into the 'Sending' field on the same form. This will make sure that the birthday reminder is sent out once only.

Finally, the other SQL commands should also be modified to work with the birthday table.

To test the service, send a birthday message by setting the date of the computer to the birthday of one of the persons in the example (e.g. George). After setting the date to 2009-09-04, you can see in the database user's 'Events' window that the message was found in the database, and it was accepted for delivery. If you look into the database table, you will see that the "lastnotified" field was updated.

For this, open the MySQL Command Line Client, and apply a query to the birthday table. As you can see, the 'lastnotified' and 'status' fields has changed.

Customization

To customize the Happy Birthday greeting, the SQL statement is needed to be modified. 

SELECT id,  '', customerphone, CONCAT(  'Hello ', customername,  '! The date of your birthday is ', birthdate,  '. Congratulations!' )
FROM birthday WHERE MONTH( current_date ) = MONTH( birthdate ) AND DAY( current_date ) = DAY( birthdate )
AND (NOT lastnotified = current_date) OR lastnotified IS NULL;

The 4ht term becomes the outgoing sms. Both customer name and birth date are queried from the database.

For example: if you insert this SQL statement into the MySQL command prompt, you will get this output: 

+----+--+---------------+---------------------------------------------------------------------------------------------------------+
| id |  | customerphone | CONCAT(  'Hello ', customername,  '! The date of your birthday is ', birthdate,  '. Congratulations!' ) |
+----+--+---------------+---------------------------------------------------------------------------------------------------------+
|  1 |  | +36301111111  | Hello Elisabeth! The date of your birthday is 2009-08-04. Congratulations!                              |
|  2 |  | +36202222222  | Hello George! The date of your birthday is 2009-09-04. Congratulations!                                 |
+----+--+---------------+---------------------------------------------------------------------------------------------------------+

5. CONCLUSION

By performing the steps provided in this documentation your goal has been achieved, you have set up a working birthday greeting SMS service in Ozeki NG - SMS Gateway software with the help of MySQL database server. Now the system is ready to be used.

More information