Android Tutorial Tutorial - Online Scoreboard

I noticed this being asked a few times so I thought I'd share the version I've created.

This implementation requires that you have a server with PHP and MySQL. I am by no means a PHP or MySQL expert so please correct me on anything that I say incorrectly.

First things first, we need a database. If you have hosting I'll assume you know how to get your database details.

Here's the SQL I used:

CREATE TABLE `scoreTable` (
  `id` int(11) NOT NULL auto_increment,
  `name` varchar(30) default NULL,
  `score` int(11) default NULL,
  `gameType` int(3) NOT NULL,
  `deviceID` varchar(30) NOT NULL,
  PRIMARY KEY  (`id`)
);

id - Required. This is pretty much essential in all MySQL databases. It gives each entry a unique ID.
name - Required. Self-explanitory.
score - Required. Self-explanitory.
gameType - Optional. You can use this to determine what gametype the score submitted is e.g easy, medium, hard. It's currently set up to use numbers only though so use 1,2,3 or change it to text if you're comfortable doing so.
deviceID - Optional. I added this so each device can only submit one score per game type. Also, you can look up the users score.

Although the last two are optional, for the purpose of this tutorial I will be using them. If you want to remove them you will have to change the PHP as well.

Here's the PHP file:

<?php

$myPassword = "";
$mysqlDatabaseName = "";
$mysqlUsername = "";
$mysqlPassword = "";

if($_GET['secret']!=$myPassword){
die("You shouldn't be here...");
}
mysql_connect($mysqlDatabaseName, $mysqlUsername, $mysqlPassword) or die(mysql_error());
mysql_select_db("scores") or die(mysql_error());
if(isset($_GET['id'])){
   $id = $_GET['id'];
   $id = mysql_real_escape_string($id);
}
if($id == "add"){
   $name = $_GET['name'];
   $score = $_GET['score'];
   $type = $_GET['type'];
   $deviceID = $_GET['deviceID'];
   $name = mysql_real_escape_string($name);
   $score = mysql_real_escape_string($score);
   $type = mysql_real_escape_string($type);
   $deviceID = mysql_real_escape_string($deviceID);
   $checkExists = mysql_query("SELECT COUNT(id),score FROM scoreTable WHERE gameType = '$type' AND deviceID=$deviceID"); 
   $checkExists = mysql_fetch_array($checkExists) or die(mysql_error());
   if($checkExists['COUNT(id)']>0){
      if($score > $checkExists['score']){
      mysql_query("UPDATE scoreTable SET score='$score', name='$name' WHERE gameType = '$type' AND deviceID=$deviceID") 
      or die(mysql_error());  
      }
   }else{
   mysql_query("INSERT INTO scoreTable 
   (name, score, gameType, deviceID) VALUES('$name', '$score', '$type', '$deviceID' ) ") 
   or die(mysql_error());  
   }
}
if($id == "view"){
   $start = $_GET['start'];
   $end = $_GET['end'];
   $type = $_GET['type'];
      $start = mysql_real_escape_string($start);
      $end = mysql_real_escape_string($end);
      $type = mysql_real_escape_string($type);
   $result = mysql_query("SELECT * FROM scoreTable WHERE gameType = '$type' ORDER BY score DESC LIMIT $start,$end") or die(mysql_error());  
   while($row = mysql_fetch_array($result)){
   echo $row['name'];
   echo "\r\n".$row['score']."\r\n";
}
}
if($id == "showUser"){
   $deviceID = $_GET['deviceID'];
   $type = $_GET['type'];
   $limit = $_GET['limit'];
   $deviceID = mysql_real_escape_string($deviceID);
   $type = mysql_real_escape_string($type);
   $limit = mysql_real_escape_string($limit);
   $userName = mysql_query("SELECT name,score FROM scoreTable WHERE deviceID=$deviceID AND gameType = '$type' LIMIT 1") or die(mysql_error());
   $userName = mysql_fetch_array($userName);
   $position = mysql_query("SELECT COUNT(id) FROM scoreTable WHERE score>=".$userName['score']." AND gameType = '$type' AND deviceID!='".$deviceID."'"); 
   $position = mysql_fetch_array($position) or die(mysql_error());
   echo $position['COUNT(id)']."\r\n";
   echo $userName['name']."\r\n".$userName['score']."\r\n";
   $result = mysql_query("SELECT * FROM scoreTable WHERE score<=".$userName['score']." AND gameType = '$type' AND deviceID!=".$deviceID." ORDER BY score DESC LIMIT ".$limit) or die(mysql_error());  
   while($row = mysql_fetch_array($result)){
   echo $row['name'];
   echo "\r\n".$row['score']."\r\n";
}
}
?>

Go ahead and save that as scores.php. I won't be going over the PHP in detail as this isn't a forum for PHP. I'll answer questions and give help if you like though.

There's 4 things you have to change in the PHP file. Right at the top.

$myPassword - A password, just to add a little security.
$mysqlHostName - You MySQL hostname
$mysqlDatabaseName - Your database name
$mysqlUsername - Your database username
$mysqlPassword - Your database password

This PHP file gives us 3 functions:

add
Usage:

scores.php?secret=YOUR_PASSWORD&id=add&name=USER&score=USER_SCORE&type=GAME_TYPE&deviceID=USER_DEVICEID

This adds a score to the database. Pretty simple stuff. If the deviceID submitted has been used before and for the same gametype the entry in the database it matches will be updated, rather than a new entry being added.

view
Usage:

scores.php?secret=YOUR_PASSWORD&id=view&start=START_NUMBER&end=END_NUMBER&type=GAME_TYPE

Shows the entries ordered by descending score. "Start" and "end" allow you to select how many entries to show. So start=5&end=14 will show positions 5 - 14.

showUser
Usage:

scores.php?secret=YOUR_PASSWORD&id=showUser&deviceID=USER_DEVICEID&type=GAME_TYPE&limit=TRAILING_SCORES

Gets the score of the name and score of the current deviceID and show "limit" number of scores after i.e limit=15 will give the the current deviceID score and the 15 scores after.
Also, the first line of the returned file will have the position of the current deviceID score (reason for that later...)

Righto, so now the actual program code:

Sub Process_Globals
   Dim httpC As HttpClient
End Sub

Sub Globals
   Dim spnGameType As Spinner
   spnGameType.Initialize("spnGameType")
   
   Dim cmdTop As Button
   cmdTop.Initialize("cmdTop")

   Dim cmdViewMe As Button
   cmdViewMe.Initialize("cmdViewMe")

   Dim lstScore As ListView
   lstScore.Initialize("lstScore")
   
   Dim difficulty As String
   Dim httpReq As HttpRequest
   Dim reader As TextReader
   
   Dim phoneInfo As PhoneID
   Dim deviceID As String
   deviceID = phoneInfo.GetDeviceId
End Sub

Sub Activity_Create(FirstTime As Boolean)
   If FirstTime Then
        httpC.Initialize("httpC")
    End If
   
   cmdTop.Text = "Top 100"
   Activity.AddView(cmdTop,50%x+5dip,5dip,25%x-5dip,50dip)
   
   cmdViewMe.Text = "Me"
   Activity.AddView(cmdViewMe,75%x+5dip,5dip,25%x-5dip,50dip)
   
   spnGameType.Prompt = "Select scoreboard"
   spnGameType.Add("Easy")
   spnGameType.Add("Medium")
   spnGameType.Add("Hard")
   Activity.AddView(spnGameType,5dip,5dip,50%x-10dip,50dip)

   lstScore.ScrollingBackgroundColor = Colors.Transparent
   Activity.AddView(lstScore,0,55dip,100%x,100%y-65dip)
   spnGameType_ItemClick(spnGameType.SelectedIndex,spnGameType.SelectedItem)
End Sub

Sub Activity_Resume

End Sub

Sub Activity_Pause (UserClosed As Boolean)
   Activity.Finish
End Sub

Sub cmdViewMe_Click
   Dim req As HttpRequest
   req.InitializeGet("http://mywebsite.com/scores.php?secret=YOUR_PASSWORD&id=showUser&deviceID="&deviceID&"&type="&difficulty)
    httpC.Execute(req, 2)
   ProgressDialogShow("Fetching your high score...")
End Sub

Sub cmdTop_Click
   spnGameType_ItemClick(spnGameType.SelectedIndex,spnGameType.SelectedItem)
End Sub

Sub spnGameType_ItemClick (Position As Int, Value As Object)
   Select Case Value
      Case "Easy"
         difficulty = 1
      Case "Medium"
         difficulty = 2
      Case "Hard"
         difficulty = 3
   End Select
   Dim req As HttpRequest
    req.InitializeGet("http://mywebsite.com/scores.php?secret=YOUR_PASSWORD&id=view&start=0&end=100&type="&difficulty)
    httpC.Execute(req, 1)
   ProgressDialogShow("Fetching high scores...")
End Sub

Sub httpC_ResponseSuccess (Response As HttpResponse, TaskId As Int)
   lstScore.Clear
   Dim result As String
   result = Response.GetString("UTF8")
   File.WriteString(File.DirInternalCache, "scores.sco",result)
   reader.Initialize(File.OpenInput(File.DirInternalCache, "scores.sco"))
   Dim line As String
   Dim i As Int
   i = 0
   line = reader.ReadLine
   If TaskId = 2 Then
      If IsNumber(line) = False Then
         ToastMessageShow("You have no saved score",False)
         spnGameType_ItemClick(spnGameType.SelectedIndex,spnGameType.SelectedItem)
         Return
      End If
      i = line
      line = reader.ReadLine
   End If
   Do While line <> Null
      i = i+1
      lstScore.AddTwoLines(i&". "&line,reader.ReadLine)
      line = reader.ReadLine
   Loop
   reader.Close
   ProgressDialogHide
   lstScore.SetSelection(0)
End Sub

Sub httpC_ResponseError (Response As HttpResponse, Reason As String, StatusCode As Int, TaskId As Int)
   Msgbox("Error connecting to score server"&CRLF&CRLF&Reason,"Error ("&StatusCode&")")
   ProgressDialogHide
End Sub

That's lifted straight out of my program, with a few changes of course. I know personally I learn better from seeing actual use rather than loads of subroutines dotted around.

Here's the add subroutine:

Sub cmdSubmitScore_Click
      ProgressDialogShow("Submitting score...")
      Dim req As HttpRequest
      req.InitializeGet("http://mywebsite.com/scores.php?secret=YOUR_PASSWORD&id=add&name=USERNAME&score=123456&"&type=1&deviceID=USER_DEVICEID)
      httpC.Execute(req, 1)
End Sub

Sub httpC_ResponseSuccess (Response As HttpResponse, TaskId As Int)
   ProgressDialogHide
   ToastMessageShow("Score submitted",False)
End Sub

 

[capisx]

Thank you very much admac231 for sharing your code :sign0142:, it really helps a newbie like me. :sign0098:

 

[PharCyDeD]

Thanks for the tutorial. I am trying to integrate this into my app and having a few difficulties. What Libs are being used? I know HTTP and Phone are used, but what else? 'Response' in:

Sub httpC_ResponseSuccess (Response As HttpResponse, TaskId As Int)

is highlighted red so I know something is missing. Also, are all the cmd's buttons?

 

[Penko]

I am asking just from theoritic point of view. I see there is no threading so we assume results can be obtained within 5 seconds, right?

Maybe if more players start playing, it can turn into a problem.

 

[Erel]

I see there is no threading so we assume results can be obtained within 5 seconds, right?

No. There are threads however they are managed by the Http library. You need to handle the events which are raised in the main thread.

 

[PharCyDeD]

I set it up a bit and then realized my hosting does not allow remote connections to the MySQL database. :sign0080: .

Are there any free alternatives that you would recommend?

 

[Erel]

I don't think that this is a remote connection. You are calling a PHP script which accesses the local MySQL database.

 

[PharCyDeD]

Hmm, I get this when running the program:

1. Access denied for user 'myusernamehere'@'%' to database 'scores'

 

[admac231]

Why would a host offer PHP and MySQL and not allow connections between the two...?

This works no different than a website running off PHP and MySQL. That is a strange problem indeed. Have you had issue with this before?

 

[PharCyDeD]

I have used MySQL for numerous scripts on my hosting without any issues. However, nothing that connected to the server via a program. I think my hosting is blocking this kind of connection because they are considering this kind of action to be 'remote'. I have no other way to explain the error. I will continue to poke around though to see what I can do.

 

[Erel]

Technically there is no difference between a request sent from a browser and a request sent from an application.
Remote connections are different. In this mode a connection is done directly to the MySQL server. This is not the case here.

You should check the response you get from the script.

 

[PharCyDeD]

How can I check the response I get from the script? I have never had to troubleshoot php before. I know you covered this not being a remote connection, but my hosting says this:

Your MySQL databases are stored behind a firewall to protect your data. You can only access each one exclusively through your server. Direct access to your MySQL databases using a home PC (external ODBC connection) cannot be established.

Could it be that a firewall is somehow preventing this action?

 

[Erel]

You should check the error response received in hc_ResponseError (Response.GetString).

 

[PharCyDeD]

Is this correctly setup? (not worried about posting details as I can just create a new database with new details)

Details:

Database name : db404553659
Host name : db404553659.db.1and1.com
User name : dbo404553659

Php changes:

$myPassword = "BASIC4ANDROID";
$mysqlDatabaseName = "db404553659.db.1and1.com";
$mysqlUsername = "dbo404553659";
$mysqlPassword = "Basic4Android";

Altered B4A Code:

Sub Process_Globals
    Dim httpC As HttpClient
End Sub

Sub Globals
    Dim spnGameType As Spinner
    spnGameType.Initialize("spnGameType")
    
    Dim cmdTop As Button
    cmdTop.Initialize("cmdTop")

    Dim cmdViewMe As Button
    cmdViewMe.Initialize("cmdViewMe")

    Dim lstScore As ListView
    lstScore.Initialize("lstScore")
    
    Dim difficulty As String
    Dim httpReq As HttpRequest
    Dim reader As TextReader
    
    Dim phoneInfo As PhoneId
    Dim deviceID As String
    deviceID = phoneInfo.GetDeviceId
End Sub

Sub Activity_Create(FirstTime As Boolean)
    If FirstTime Then
        httpC.Initialize("httpC")
    End If
    
    cmdTop.Text = "Top 100"
    Activity.AddView(cmdTop,50%x+5dip,5dip,25%x-5dip,50dip)
    
    cmdViewMe.Text = "Me"
    Activity.AddView(cmdViewMe,75%x+5dip,5dip,25%x-5dip,50dip)
    
    spnGameType.Prompt = "Select scoreboard"
    spnGameType.Add("Easy")
    spnGameType.Add("Medium")
    spnGameType.Add("Hard")
    Activity.AddView(spnGameType,5dip,5dip,50%x-10dip,50dip)

    lstScore.ScrollingBackgroundColor = Colors.Transparent
    Activity.AddView(lstScore,0,55dip,100%x,100%y-65dip)
    spnGameType_ItemClick(spnGameType.SelectedIndex,spnGameType.SelectedItem)
End Sub

Sub Activity_Resume

End Sub

Sub Activity_Pause (UserClosed As Boolean)
    Activity.Finish
End Sub

Sub cmdViewMe_Click
    Dim req As HttpRequest
    req.InitializeGet("http://instantlywatchmovies.com/scores.php?secret=BASIC4ANDROID&id=showUser&deviceID="&deviceID&"&type="&difficulty)
    httpC.Execute(req, 2)
    ProgressDialogShow("Fetching your high score...")
End Sub

Sub cmdTop_Click
    spnGameType_ItemClick(spnGameType.SelectedIndex,spnGameType.SelectedItem)
End Sub

Sub spnGameType_ItemClick (Position As Int, Value As Object)
    Select Case Value
        Case "Easy"
            difficulty = 1
        Case "Medium"
            difficulty = 2
        Case "Hard"
            difficulty = 3
    End Select
    Dim req As HttpRequest
    req.InitializeGet("http://instantlywatchmovies.com/scores.php?secret=BASIC4ANDROID&id=view&start=0&end=100&type="&difficulty)
    httpC.Execute(req, 1)
    ProgressDialogShow("Fetching high scores...")
End Sub

Sub httpC_ResponseSuccess (Response As HttpResponse, TaskId As Int)
    lstScore.Clear
    Dim result As String
    result = Response.GetString("UTF8")
    File.WriteString(File.DirInternalCache, "scores.sco",result)
    reader.Initialize(File.OpenInput(File.DirInternalCache, "scores.sco"))
    Dim line As String
    Dim i As Int
    i = 0
    line = reader.ReadLine
    If TaskId = 2 Then
        If IsNumber(line) = False Then
            ToastMessageShow("You have no saved score",False)
            spnGameType_ItemClick(spnGameType.SelectedIndex,spnGameType.SelectedItem)
            Return
        End If
        i = line
        line = reader.ReadLine
    End If
    Do While line <> Null
        i = i+1
        lstScore.AddTwoLines(i&". "&line,reader.ReadLine)
        line = reader.ReadLine
    Loop
    reader.Close
    ProgressDialogHide
    lstScore.SetSelection(0)
End Sub

Sub httpC_ResponseError (Response As HttpResponse, Reason As String, StatusCode As Int, TaskId As Int)
    Msgbox("Error connecting to score server"&CRLF&CRLF&Reason,"Error ("&StatusCode&")")
    ProgressDialogHide
End Sub

The program just tells me this when I run it:

1. Access denied for user 'dbo404553659'@'%' to database 'scores'


**Edit: I get no other error from ResponseError in the program

 

[admac231]

I thought I knew what had gone wrong but I really don't know. It all looks pretty good to me.

 

[PharCyDeD]

Yeah, I can really only chalk it up to my hosting because everything else is correctly done. I am not even altering the code at all aside from the needed changes like website, database info, etc. Guess I will have to go with local high scores for now and figure this out later. What hosting do you use?

 

[admac231]

I use https://www.nearlyfreespeech.net/. It's pay as you go so you only pay for the storage, bandwidth and features you use. For example, last month I paid about 3$ in total for around 4Gb transfers, 4mb files, 2 MySQL databases and DNS. I can't recommend them enough.

Saying that, they're only really good if you don't expect a lot of traffic.

 

[PharCyDeD]

Thanks for the recommendation. I have a server for my webpage needs, but using them for a highscore board should be fairly cheap I assume? I doubt it would be going crazy on bandwidth to update scoreboards. Plus, if my scoreboard is going that good I guess I could afford the more expensive costs per month anyway.

 

[admac231]

Probably cost you about 3 cents a day

 

[GulfCoast]

In your php script you're connecting to the remote database. See the connection in your script:

$myPassword = "";
$mysqlDatabaseName = "";
$mysqlUsername = "";
$mysqlPassword = "";
...

Some web hosts don't allow remote connections like this, others do. If you have access to a control panel and your host allows it you can put a (*) wildcard to allow remote connections or you can specify which domains you want to allow remote connections from.