Sport betting service via SMS

Find an SMS sport betting example on this page that will demonstrate how to build your own SMS system to introduce SMS betting service for your customers. By downloading the project (available below this page) you can start the betting service via SMS to make your business more attractive and let more people know about it. To implement this solution effectively and properly please follow the configuration guide and watch the video tutorial, as well.

Download: ozeki-sms-bet-example.zip

Introduction

SMS sport betting service practically means that users can bet to the outcome of a sport event using their mobile phones. The greatest advantage of this solution is its simplicity since there is no need for paper forms but people can send bets via SMS text messages. In this way you can collect their phone numbers even for further mobile marketing purposes or you can achieve that more people will know your business through your entertaining solution.

How this system works

To be able to implement SMS sport betting service, first, you need to download and install Ozeki NG SMS Gateway. This software will provide SMS functionality for your IT environment. The SMS gateway connects to the mobile network in two ways. It can operates one or more GSM modem attached to the computer, called GSM modem connection. Or it can connect directly to the SMS center of a mobile service provider over the Internet: IP SMS connection.

After you configure the SMS gateway SMS betting service will work as follows: Users need to send SMS text messages to a predefined phone number. Ozeki NG SMS Gateway will process these messages with the help of its ASP user (Learn how to create an ASP user in Ozeki NG SMS Gateway: ASP user setup). This ASP user forwards messages to a MySQL database to be stored. Finally, the results will appear on the web site (Figure 1). Settings can also be made on the web site.

Please note that the example below is made for MySQL database but other databases can also be used with this solution. Ozeki NG SMS Gateway connects to the database via ODBC driver.

system architecture
Figure 1 - System architecture

Configuration guide

I have already downloaded and extracted ozeki-sms-bet-example.zip file to the desktop. Figure 1 demonstrates the content of this file: webpage directory, MySQL-table-structure.txt file, and sms-bet.aspx file.

content of the ozeki sms bet example zip file
Figure 2 - Content of ozeki-sms-bet-example.zip file

Copy the content of webpage to the directory of the web page (wwwroot). I created an sms-bet directory and copied webpage into this directory so the full path in this example is: C:\AppServ\www\sms-bet (Figure 2).

copy webpage directory
Figure 3 - Copy webpage directory

Open MySQL-table-structure.txt file (Figure 4).

open the mysql table structure text file
Figure 4 - Open MySQL-table-structure.txt file

MySQL-table-structure.txt file


CREATE DATABASE `bets`;

USE `bets`;

CREATE TABLE `bets` (
  `id` int(20) NOT NULL auto_increment,
  `sender` varchar(20) NOT NULL,
  `originalmsg` varchar(160) NOT NULL,
  `formatted_msg` varchar(160) NOT NULL,
  `match_number` int(5) NOT NULL,
  `team1` varchar(30) NOT NULL,
  `team2` varchar(20) NOT NULL,
  `time_of_bet` timestamp NOT NULL default CURRENT_TIMESTAMP,
  `msgid` varchar(30) NOT NULL,
  PRIMARY KEY  (`id`)
);


CREATE TABLE `matches` (
  `id` int(10) NOT NULL auto_increment,
  `match_number` varchar(10) NOT NULL,
  `match_begin` bigint(15) NOT NULL,
  `team1` varchar(30) NOT NULL,
  `team2` varchar(30) NOT NULL,
  `bet_begin` bigint(15) NOT NULL,
  `bet_end` bigint(15) NOT NULL,
  `final_result` varchar(40) NOT NULL,
  PRIMARY KEY  (`id`)
);

CREATE TABLE `settings` (
  `id` int(10) NOT NULL auto_increment,
  `name` varchar(10) NOT NULL,
  `value` tinyint(1) NOT NULL,
  PRIMARY KEY  (`id`)
);


INSERT INTO `settings` VALUES (1, 'restrict', 1); 

Now copy the content of MySQL-table-structure.txt and paste it to the SQL console. The content of the file will be executed (Figure 5).

execute the content of mysql table structure
Figure 5 - Execute the content of MySQL-table-structure.txt

Copy the content of sms-bet.aspx file (Figure 6).

copy the sms bet aspx fil
Figure 6 - Copy sms-bet.aspx

And paste sms-bet.aspx to its final place. In our example it is C:\bet\sms-bet.aspx (Figure 7).

paste sms bet aspx file
Figure 7 - Paste sms-bet.aspx

Install an ASP user in Ozeki NG SMS Gateway. On the Configuration pane of the user specify the path to the aspx file. In this example it is C:\bet\sms-bet.aspx (Figure 8).

path to sms bet aspx file
Figure 8 - Path to sms-bet.aspx file

Open sms-bet.aspx file to be able to customize it. You can specify the server, the database, the user, and its password (Figure 9). In our example these parameters are the follows:

Server: 127.0.0.1
Database: bets
User: root
Password: qwe123

customize aspx file
Figure 9 - Customize aspx file

sms-bet.aspx file

<%@ Page Language="C#"%>
<%@ Import Namespace="System" %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.Common" %>
<%@ Import Namespace="System.Data.SqlClient" %>
<%@ Import Namespace="System.Data.Odbc" %>

<%

        // Copying the message parameters

    string sender = Request.QueryString["sender"];
    string messagedata = Request.QueryString["messagedata"];
    string messageid = Request.QueryString["messageid"];

        // Converting the message datra to lowercase

        string data = messagedata.ToLower();


        // The country names containing more parts will be considered as one word

        data = Regex.Replace(data, "united(.|..|...)states", "unitedstates");
    data = Regex.Replace(data, "south(.|..|...)africa", "southafrica");
    data = Regex.Replace(data, "korea(.|..|...)republic", "korearepublic");
    data = Regex.Replace(data, "korea(.|..|...)dpr", "koreadpr");
    data = Regex.Replace(data, "new(.|..|...)zealand", "newzealand");

        // Checking the match for the pre-defined message format
    bool match = Regex.IsMatch(data, "([0-9]{1,2})([^a-zA-Z0-9]{1,3})([a-zA-Z]*)
    ([^a-zA-Z0-9]{1,3})([a-zA-Z]*)([^a-zA-Z0-9]{1,3})([0-9]{1,3})([^a-zA-Z0-9]{1,3})([0-9]{1,3})");

        // If it not matches, then the message will be treated as an invalid message.
   if (!match)
   {
       bool connectedToDb;
       string connectionString = "Driver={MySQL ODBC 5.1 Driver};Server=127.0.0.1;
       Database=bets;User=root;Password=qwe123;Option=4;";
       OdbcConnection oc = new OdbcConnection(connectionString);
       oc.Open();
       string insertSQL = "insert into bets(`sender`,`originalmsg`,`formatted_msg`,
       `match_number`,`team1`,`team2`,`time_of_bet`,`msgid`) values ('" + sender + "','" +
       messagedata + "','invalid','0','invalid','invalid',now(),'" + messageid + "');";
       OdbcCommand command = new OdbcCommand(insertSQL, oc);
       command.ExecuteNonQuery();
       oc.Close();
   }
   else
   {

   // Replacing the special characters into semicolon.
       data = data.Replace('-', ';');
       data = data.Replace('.', ';');
       data = data.Replace(',', ';');
       data = data.Replace('_', ';');
       data = data.Replace('<', ';');
       data = data.Replace('>', ';');
       data = data.Replace('#', ';');
       data = data.Replace('&', ';');
       data = data.Replace('@', ';');
       data = data.Replace('{', ';');
       data = data.Replace('}', ';');
       data = data.Replace(':', ';');
       data = data.Replace('*', ';');
       data = data.Replace('\'', ';');
       data = data.Replace('\\', ';');
       data = data.Replace('"', ';');
       data = data.Replace('+', ';');
       data = data.Replace('!', ';');
       data = data.Replace('%', ';');
       data = data.Replace('/', ';');
       data = data.Replace('=', ';');
       data = data.Replace('|', ';');
       data = data.Replace('[', ';');
       data = data.Replace(']', ';');
       data = data.Replace('(', ';');
       data = data.Replace(')', ';');
       data = data.Replace('�', ';');

        // Splitting the message by semicolons, and removing the incidental duplicate semicolons
       string[] Splitted = data.Split(';', ' ');
       string FormattedBet = "";
       for (int i = 0; i <= Splitted.Length - 1; i++)
       {
           if (Splitted[i] != "")
           {
               FormattedBet = FormattedBet + (Splitted[i] + ";");
           }

       }

       // Bet variable will contain the processed message, in an array
       string[] Bet = FormattedBet.Split(';');

       bool connectedToDb;

       try
       {
           string connectionString = "Driver={MySQL ODBC 5.1 Driver};Server=127.0.0.1;
           Database=bets;User=root;Password=qwe123;Option=4;";
           OdbcConnection oc = new OdbcConnection(connectionString);
           oc.Open();
           connectedToDb = true;
           try
           {

            // Ensuring the possibility for the team names could be swithable.

               string team1;
               string team2;
               if ((Bet[1].Substring(0, 3).CompareTo(Bet[2].Substring(0, 3))) < 0)
               {
                   team1 = Bet[1].Trim() + ";" + Bet[3].Trim();
                   team2 = Bet[2].Trim() + ";" + Bet[4].Trim();
               }
               else
               {
                   team1 = Bet[2].Trim() + ";" + Bet[4].Trim();
                   team2 = Bet[1].Trim() + ";" + Bet[3].Trim();
               }

            // Assembling the SQL query, and inserting the bet.
               string insertSQL = "insert into bets(`sender`,`originalmsg`,`formatted_msg`,
               `match_number`,`team1`,`team2`,`time_of_bet`,`msgid`) values ('" +
               sender + "','" + messagedata + "','" + FormattedBet + "','" + Bet[0].Trim()
               + "','" + team1 + "','" + team2 + "',now(),'"
               + messageid + "');";
               OdbcCommand command = new OdbcCommand(insertSQL, oc);
               command.ExecuteNonQuery();
               oc.Close();
           }
           catch (Exception z)
           {
               string errCode = z.Message;
               Response.Write(errCode);
           }

       }
       catch (Exception e)
       {
           string respmsg = "Cannot connect to database." + e.Message;
           connectedToDb = false;
           Response.Write(respmsg);
       }
   }
%>

Open query.php file (can be found in MySQL-table-structure.txt file). You need to specify the server, the user and its password for the MySQL function. In our example these parameters are the follows: localhost, root, and qwe123 in order.

query php file
Figure 10 - Query.php file

query.php file

<?php
#################################################
###             Function name: Query          ###
### This helper function queries the database ###
###           and returns the results.        ###
### parameters: database name, sql statement  ###
###             returns: the result.          ###
#################################################

function Query($dbname,$sql){
        $link = mysql_connect('localhost', 'root', 'qwe123') or die
        ('Could not connect to MySQL database. Reason: ' . mysql_error());
        mysql_select_db($dbname) or die ('Cannot select database');
        $result = mysql_query($sql);
        mysql_close();
return $result;
}
?>

Test the system

I have sent a test message that has been processed by ASP user (Figure 11). This was a test message and not a bet.

test message processed by asp user
Figure 11 - Test message processed by ASP user

To see the results, click on "view results" on the control pane on the web site (Figure 12).

view results
Figure 12 - View results

Since my first test message was not a bet, it is considered by the system as an invalid message (Figure 13).

invalid message
Figure 13 - Invalid message

Create a new game for betting

First you can enable or disable restrictions to the system. If you enable it, it means that the system will accept only one bet from one user during one game period. If you disable this option then users can send several bets and these bets will be accepted by the system (if they meet the requirements) (Figure 14).

enable / disable restrictions
Figure 14 - Enable/Disable restrictions

In Create game for the next match section I create a test game:

Ordinal number of the match: it is necessary to specify an identification number for the game (in this example it is "2"). Users need to include this number in their SMS bets, without this parameter the bit will be "invalid"

Team1: Specify the name of the first team (France)
Team2: Specify the name of the other team (Uruguay)
Match begin time: Specify the exact date of the match in Year/Month/Day Hour:Minute:Second format (2010/06/11 20:30:00)

Game period: It is the period in which users can send bets and the system will accept them if they meet the requirements. You need to specify a from - to period. If you do not specify Hour/Minute/Second then the system will count the period from Hour 00/00/00. So in this example this period is 2010/06/06 - 2010/06/10, it means that bets are accepted from 2010/06/06 00/00/00 to 2010/06/10 00/00/00

To add the new created game click on Create new game (Figure 15).

create a new game
Figure 15 - Create new game

Note that the SMS sport betting service only accepts teamnames included in the $TeamNames array in the newgame.php file.

Now I send a test bet. In the SMS text message the follows need to be included: Ordinal number of the match, Team1, Team2, Result of Team1, Result of Team2.
Please note that the system is flexible since it is able to handle cases if users invert the names of teams (it selects only the names and the order does not matter), however, the names needs to be spelled correctly. Furthermore, the system picks unnecessary punctuation marks up to 3 characters between the values in the message. It means that users can write any punctuation mark in their messages, the system will ignore them up to 3 characters.

If all of the requirements are fulfilled, the system accepts the bet. If something is missing, or the user misspells the country names the message will be inserted as an invalid message.
In case of accepted bets, the bet will be inserted into the table of the respective game which the bet arrived at. The following information is provided (Figure 16):

ID: this helps identify the bet in the database
Sender: the phone number of the sender
Original message: shows how the message was received
Match number: identifies the game
Formatted message: it is the accepted message. It shows the form in which the message has been processed
Team1: (France) the name of the first team
Team2: (Uruguay) the name of the other team
Status of bet: it shows whether the bet is valid or invalid
msgID: this is the message ID that helps identify the message in Ozeki NG SMS Gateway
Time of the match: the starting time of the match
Interval of the bidding: it shows the game period in which bets are accepted
Time of the bet: it is the time when the bet is received in the system
Status according to time: it shows if the message arrives in time, before the betting period or after the betting period
Match result: the final results of the match
Won/Lost: it shows that the respective user wins or loses with his bet

test bet
Figure 16 - Test bet

Now I enter a test result for the match in Enter the result of the match section. I specify the result (france-uruguay 3-2) and click on Insert result (Figure 17).

insert result
Figure 17 - Insert result

Since the test bet meets all the requirements (even the result of the game is agreed) Figure 18 demonstrates that it won the game.

won the game
Figure 18 - Won the game

Finally, it is possible to select columns and by clicking on Export columns to Excel button, the selected columns will be exported into an Excel file (Figure 19).

export columns to excel
Figure 19 - Export columns to Excel

On Figure 20 you can see the exported columns in Excel.

the exported columns
Figure 20 - Exported columns in Excel

If you haven't downloaded Ozeki NG SMS Gateway, download the software or get a free trial right now: Download Ozeki NG SMS Gateway

If you have any question, please do not hesitate to contact us at: info@ozekisms.com

More information