OZEKI
NG SMS Gateway
"The World's most reliable SMS gateway software!"
info@ozekisms.com
Tel.: 00 36 52 532 731
Overview Quick start Download Manual How to buy FAQ Contact Us
OZEKI NG SMS Gateway - Product Guide

Instant brochure - MMS autoreply Contents | SearchSMS sport betting service

Home > Product Manual > Examples and Solutions > Alphabet letter game

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
  Appendix
  FAQ
  Feature list
  Commercial Information
  Search
 


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.

Alphabet letter game


Launch exciting and impressive games for people or your customers to engage them for longer relations! On this page you can find an excellent alphabet letter game that can be implemented very easily without any programmer expertise. All you need is database knowledge. Now read this article that provides you detailed information on how to configure your system to launch alphabet letter game.


Download: lettergame.zip

Video: SMS alphabet letter game (part 1/1, configure your system)

Introduction

This alphabet letter game is a simple but impressive solution to call the attention of people. It offers an interactive way for people to play and win various prizes. To get the prize they only need to use their mobile phones. You need to define a mobile phone number to which they can send SMS messages to play. In your SMS system you can also define which letters of the alphabet can be played. Then you only need to advertise your game and enjoy its advantages.

How to build your SMS system

To be able to launch alphabet letter game via SMS text messages you need to build your own SMS system. To do so download and install Ozeki NG SMS Gateway to your computer. This software product will ensure SMS functionality and operate your SMS system. After the installation you can start to receive SMS messages from mobiles to your PC and send response messages from PC to mobiles.

The SMS gateway forwards SMS messages to the mobile network in two possible ways. One of the possibilities is GSM modem connection, it means that one or more GSM modems are attached to the PC with a datacable and the gateway operates them. Or the SMS gateway connects directly to the SMS center of a mobile service provider over the Internet. It is called IP SMS connection.

Description of the game

If someone sends the word "info" to the predefined phone number he will receive a response message containing the description of the game. If someone sends another word for example "hello" to the same phone number he will also receive a response message that will inform him how he can play the game. People can play if they send a letter of the alphabet to your phone number. Each of the letters holds a prize. Each letter has an SMS value so after the predefined number of SMS messages arrived at one of the letters the player wins the prize. For example, every tenth player win a t-shirt with letter "A", etc. People can send a letter in five times and your SMS system will send different response messages at each time. See an example in the guide below how these response messages are sent to players.

System architecture

After you built your SMS system it will work as follows: Someone sends a letter, for example, "A" to you. Ozeki NG SMS Gateway processes this message and its Autoreply Database user will poll the respective response from the database. This response will automatically be sent to the player via SMS by Ozeki NG SMS Gateway. Figure 1 demonstrates this process.


Figure 1 - SMS alphabet letter game with Ozeki NG SMS Gateway

Configuration steps

First you need to download and extracted lettergame.zip file to your computer (Figure 2).


Figure 2 - Extract file

Start MySQL Command Line Client (Figure 3).


Figure 3 - Open MySQL Command Line Client

Login with your password (Figure 4).


Figure 4 - Enter your password

Open the first txt file: "Create table scripts" in lettergame.zip. Select the contents and copy it (Figure 5).


Figure 5 - Open Create table script txt
CREATE DATABASE `letter`;

USE `letter`;


CREATE TABLE `game` (
  `id` int(11) NOT NULL auto_increment,
  `prize_id` int(11) NOT NULL,
  `won` tinyint(1) NOT NULL default '0',
  `phone_number` varchar(15) NOT NULL,
  `date` datetime default NULL,
  PRIMARY KEY  (`id`)
) DEFAULT CHARSET=utf8;



CREATE TABLE `prize` (
  `id` int(20) NOT NULL auto_increment,
  `letter` varchar(10) NOT NULL,
  `prize` varchar(50) NOT NULL,
  `prize_description` varchar(100) NOT NULL,
  `sms_value` int(11) default NULL,
  `played_sms` int(11) default NULL,
  PRIMARY KEY  (`id`)
) DEFAULT CHARSET=utf8;



CREATE TABLE `raffle` (
  `id` int(10) NOT NULL auto_increment,
  `raffle_number` varchar(6) NOT NULL,
  `won` varchar(2) NOT NULL,
  `won_phone_number` varchar(10) NOT NULL,
  `date` datetime default NULL,
  PRIMARY KEY  (`id`)
) DEFAULT CHARSET=utf8;




CREATE TABLE `won_prize` (
  `id` int(10) NOT NULL auto_increment,
  `prize_id` int(10) NOT NULL,
  `date` date NOT NULL,
  `won` tinyint(1) NOT NULL,
  PRIMARY KEY  (`id`)
) DEFAULT CHARSET=utf8;

This script will create the letter database and four tables in this database: Game, Prize, Raffle, Won_prize tables.

Fields of table game:

"prize_id": is the id of Prize table
"won": "Game" table puts together the phone number of the sender and the prize. It stores data about who sent SMS messages to which prize. It can be True or False value. This will be set to "1" if this is the last won prize.
"phone_number": it is the phone number of the sender
"date": defines when he played for the prize.

Fields of table prize:

Prize table stores data about which letter is assigned to which prize, how many text messages are needed to win the prize, and it also shows how many messages have already been sent to the given prize.
"id": identifies the prize
"letter": the letter assigned to the prize
"prize": this is the prize
"prize_description": describes the prize
"SMS_value": shows how many messages need to be sent to win the prize
"played SMS": the numbers of messages that have been played for the prize.

Fields of table raffle:

This table stores the number of the raffle and it also shows which phone number won the raffle and when it was won.
"id": identifies the number of the raffle
"raffle_number": number of the raffle
"won": Has the raffle been won or not
"won_phone_number": the phone number that won the raffle
"date": when was the raffle won

Fields of table won_prize:

It shows if the given prize has been won or not.
"prize_id": it connects the id of the "Prize" table with the id of "won_prize" table
"date": it is the date when the prize has been won
"won": it can be True or False value and shows if the prize has been won or not.

Paste "Create table scripts" in MySQL Command Line Client (Figure 6).


Figure 6 - Paste Create table script

Open "Sample data for the table" txt file and copy it (Figure 7).


Figure 7 - Open Sample data for the table txt

##table:prize

INSERT INTO `prize` VALUES (1, 'a', 'pillow', 'In this game you are about to play for a pillow.', 20, 162);
INSERT INTO `prize` VALUES (2, 'b', 'watch', 'If you play with this letter, you will play for a watch', 10, 1);
INSERT INTO `prize` VALUES (3, 'c', 'LCD television', 'With this letter you will play for lcd television', 20, 2);
INSERT INTO `prize` VALUES (4, 'd', 'T-shirt', 'You can win a T-shirt with this letter. ', 2, 54);
INSERT INTO `prize` VALUES (5, 'e', 'cake', 'this chocolate cake is waiting for you to win.', 5, 1);
INSERT INTO `prize` VALUES (6, 'info', '', 'This is an alphabet letter game. You need to send a letter to play this game.', NULL, 1);

##table: raffle

INSERT INTO `raffle`(`raffle_number`, `won`, `won_phone_number`) VALUES ('778899', '0', '');
INSERT INTO `raffle`(`raffle_number`, `won`, `won_phone_number`) VALUES ('665544', '0', '');
INSERT INTO `raffle`(`raffle_number`, `won`, `won_phone_number`) VALUES ('332211', '0', '');
INSERT INTO `raffle`(`raffle_number`, `won`, `won_phone_number`) VALUES ('123456', '0', '');
INSERT INTO `raffle`(`raffle_number`, `won`, `won_phone_number`) VALUES ('132345', '0', '');

Paste "Sample data for the table" in MySQL Command Line Client (Figure 8).


Figure 8 - Paste Sample data for the table

Open "Create functions" txt and copy it (Figure 9).


Figure 9 - Open Create functions txt
DELIMITER //;

CREATE FUNCTION win_raffle( s char(20)) RETURNS VARCHAR(150)
BEGIN
RETURN (SELECT CONCAT((SELECT 'Fifth message: Congratulations! Your number for
the monthly raffle is: '),(SELECT `raffle_number` FROM `raffle` WHERE `won` = '0'
ORDER BY `id`  LIMIT 1)));
UPDATE `raffle` SET `won`='1', `won_phone_number` = s, `date`=now() WHERE `won`=0
ORDER BY `id` LIMIT 1;
END;//;


CREATE FUNCTION set_won(let char(20)) RETURNS VARCHAR(150)
BEGIN
INSERT INTO `won_prize` (`won`, `date`, `prize_id`) VALUES (1, now(),
(SELECT `id` FROM `prize` WHERE `letter` = let));
RETURN (SELECT CONCAT(' Congratulations! You have just won the game! Your prize
is: ', (SELECT `prize` FROM `prize`WHERE `letter` = let)));
END;//;


DELIMITER ; 



Function "win_raffle": it selects the number of the raffle and set to the fact that it once has been won

Function "set_won": it returns if the prize has been won

Paste "Create functions" txt in MySQL Command Line Client (Figure 10).


Figure 10 - Copy Create functions

Start Ozeki NG SMS Gateway and login with your username and password. Create an "Autoreply database" user. To do so click on "Add users and applications" and select "Autoreply database" user in the list and click on "Install" (Figure 11).


Figure 11 - Create an Autoreply user in Ozeki NG SMS Gateway

Provide a name for the user e.g. "lettergame" (Figure 12).


Figure 12 - Provide a name for the user

Click on "Database Setup". In "Autoreply script" tab enter the path to the lettergame.txt file (Figure 13).


Figure 13 - Specify the path to lettergame.txt

#####################################################################
######                SMS ALPHABET LETTERGAME                ########
#####################################################################


####################################################################
#              IN THIS GAME EVERY LETTER IN THE APLHABET           #
#     IS HOLDING A PRICE. YOU CAN PLAY 5 TIMES FOR EACH PRICE.     #
####################################################################


####################################################################
#            IF THE PLAYER SENDS IN THE WORD INFO,                 #
#    THIS WILL SEND BACK A SHORT DESCRIPTION ABOUT THE GAME.       #
####################################################################
k^INFO
SELECT '$sender',price_description FROM price WHERE letter = 'info';


####################################################################
#         IF THE PLAYER SENDS IN A LETTER, THIS STATEMENT          #
#   WILL DETERMINATE THE NUMBER OF THE MESSAGE, AND SENDS BACK     #
#                  THE RIGHT RESPONSE MESSAGE.                     #
####################################################################
k^[a-z]$
INSERT INTO game (price_id, phone_number) VALUES ((SELECT id FROM `price`
WHERE letter='$messagedata'),'$sender');
UPDATE `price` SET `played_sms`=`played_sms`+1 WHERE `letter`='$messagedata';
Select '$sender', CONCAT((
SELECT (case count(id)%5
when '1' then (
     SELECT price_description from price where letter='$messagedata')

when '2' then (
     SELECT 'Second message: If you play now,
            you will get a chance to play the grand prize')

when '3' then (
     SELECT 'Third message: Your chance is
            growing to win your price with every SMS')

when '4' then (
     SELECT 'Fourth message: play one more time,
            and you will get a ticket for the monthly raffle')

when '0' then (
SELECT (win_raffle('$sender')) FROM `game` WHERE `phone_number`='$sender' LIMIT 1) END)
     FROM game WHERE `phone_number`='$sender' AND
      price_id=(SELECT id FROM `price` WHERE letter='$messagedata')),(
           Select (CASE played_sms%sms_value WHEN '0' then (
             SELECT(set_won('$messagedata')))
               ELSE (SELECT ' ') END) FROM`price` WHERE `letter`='$messagedata'));


####################################################################
#         IF THE PLAYER SENDS IN ELSE, THIS WILL SENDS             #
#                  BACK A SHORT DESCRIPTION.                       #
####################################################################

n.*
SELECT '$sender','Hello, the following message was received: $messagedata. You
 need to send in a letter to play this game'

In "Connection information" tab you need to provide the connection string (Figure 14).

Driver={MySQL ODBC 5.1 Driver};Server=127.0.0.1;Database=letter;User=root;Password=qwe123;Option=4; 


Figure 14 - Specify the Connection string

Test

Now I send a test message to the system containing the word "info" (Figure 15).


Figure 15 - Test SMS with "info"

Ozeki NG SMS Gateway sends out a response message with the description of the game. You can see the response message in the "Sent" folder of the user (Figure 16).


Figure 16 - Response message in "Sent" folder of the user

Now I sent a message with the word "hello". The system sends out a response message that informs how to play the game (Figure 17).


Figure 17 - Response message in "Sent" folder of the user

I send a test message with letter "A" to play five times (Figure 18).


Figure 18 - Test message with letter "A"

Figure 19 shows the first response message.


Figure 19 - First response message in "Sent" folder of the user

Figure 20 shows the second response message.


Figure 20 - Second response message in "Sent" folder of the user

Figure 21 shows the third response message.


Figure 21 - Third response message in "Sent" folder of the user

Figure 22 shows the forth response message.


Figure 22 - Forth response message in "Sent" folder of the user

Figure 23 shows the fifth response message.


Figure 23 - Fifth response message in "Sent" folder of the user

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





Next page: SMS sport betting service
Copyright © 2000 - 2014 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