OZEKI OZEKI NG SMS Gateway
The World's most reliable SMS gateway software!

Ozeki NG SMS Gateway

< Instant brochure from MMS autoreply | SMS sport betting service >

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:

sms-letter-game.zip

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.

sms alphabet letter game with ozeki ng sms gateway — 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).

extract the file — Figure 2 - Extract file

Start MySQL Command Line Client (Figure 3).

open mysql command line client — Figure 3 - Open MySQL Command Line Client

enter your password — Figure 4 - Enter your password

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

open the create table script txt file — 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
"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).

paste the create table script — Figure 6 - Paste Create table script

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

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

provide sample data for the table — Figure 8 - Paste Sample data for the table

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

open the create function txt file — 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).

copy create function — 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).

create an autoreply user in ozeki ng sms gateway — Figure 11 - Create an Autoreply user in Ozeki NG SMS Gateway

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

provide a name for the new autoreply user — 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).

specify the path to the lettergame txt file — 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;

specify the connection strings — Figure 14 - Specify the Connection string

Test

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

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