Ozeki NG SMS Gateway logo OZEKI NG SMS Gateway
"The World's most reliable SMS gateway software!"
info@ozekisms.com
Tel.: +36 1 371 0150
SMS Gateway to send SMS via GSM modem or IP SMS connection Overview SMS Gateway to send SMS via GSM modem or IP SMS connection SMS Gateway to send SMS via GSM modem or IP SMS connection Quick start SMS Gateway to send SMS via GSM modem or IP SMS connection SMS Gateway to send SMS via GSM modem or IP SMS connection Download SMS Gateway to send SMS via GSM modem or IP SMS connection SMS Gateway to send SMS via GSM modem or IP SMS connection Manual SMS Gateway to send SMS via GSM modem or IP SMS connection SMS Gateway to send SMS via GSM modem or IP SMS connection How to buy SMS Gateway to send SMS via GSM modem or IP SMS connection SMS Gateway to send SMS via GSM modem or IP SMS connection FAQ SMS Gateway to send SMS via GSM modem or IP SMS connection SMS Gateway to send SMS via GSM modem or IP SMS connection Contact Us SMS Gateway to send SMS via GSM modem or IP SMS connection
OZEKI NG SMS Gateway - Product Guide

Birthday greeting - SQL Express Contents | SearchBirthday greeting - Oracle

Home > Product Manual > Examples and Solutions > Birthday greeting - MySQL

SMS Gateway SMS Gateway Home

  Product Manual
  Introduction
  SMS technology
  Installation Guide
  User Guide
  Developers Guide
  Examples and Solutions
  Telephone networks
  SMS Pin game
  E-mail to SMS alert
  2 way SMS to Email gateway
  SMS Order System
  Least Cost Routing
  SMS Load Balancing
  Asterisk PBX SMS
  SQL SMS Gateway
  SMS Service Provider
  Service Provider
  SMS PIN code query
  SMS Counter game
  SMS newsgroup
  Distributed SMS
  SMS Menu
  Google maps
  Forwarding
  Birthday greeting - SQL Express
  Birthday greeting - MySQL
  Birthday greeting - Oracle
  Instant brochure - MMS autoreply
  Alphabet letter game
  SMS sport betting service
  E-mail about outgoing SMS messages
  SMS Information Menu
  Email to SMS feature
  Reminder example
  Bulk SMS Client
  Bulk SMS to a given phone number range
  Filtering phone numbers
  Ozeki Phone Sytem PBX SMS
  Autoreply voucher example
  Appendix
  FAQ
  Feature list
  Commercial Information
  Search
  Cookie policy
 


Automated phone calls?
Ozeki Phone System XE VoIP PBX software is an advanced PBX built for automated voice calls and 2 way SMS messaging. It has outstanding APIs for software developers. It can be used for:

Phone calls from SQL
Phone calls from HTTP
Voice and SMS applications

Callcenter developers
If you are working on telephone solutions, please check out the Ozeki VoIP SIP SDK.
It can be used to create VoIP client and server software.

Contact Us!
If you wish to get further information, do not hesitate to contact us!

E-mail: info@ozekisms.com

If you have a technical question, please submit a support request on-line.

Birthday greeting SMS service with MySQL

Video tutorial: Setup a birthday greeting service with MySQL (part 1/3, Create database layout)
Video tutorial: Setup a birthday greeting service with MySQL (part 2/3, Setup Ozeki SMS gateway)
Video tutorial: Setup a birthday greeting service with MySQL (part 3/3, Test the configuration)

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).



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.

Dig deeper!
People who read this also read...





Next page: Birthday greeting - Oracle
Copyright © 2000 - 2017 Ozeki Informatics Ltd.
All rights reserved

Software PBX for Windows | VoIP SDK   |   Legal information   |   Privacy policy   |   Terms of use
Please, address your inquiries to info@ozekisms.com