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

 

[trueboss323]

Pardon if this is a dumb question but where do i go to host my PHP / MySQL database ? Would Dropbox work fine?

 

[DonManfred]

but where do i go to host my PHP / MySQL database ?

On your account at a webspace-provider.

Would Dropbox work fine?

Definetively NO. You can use Dropbox to store files and to upload to or download from. But you can not "run" PHP-Scripts or even host a MySQL-Database.

 

[trueboss323]

On your account at a webspace-provider.

Definetively NO. You can use Dropbox to store files and to upload to or download from. But you can not "run" PHP-Scripts or even host a MySQL-Database.

Are there any good free hosting providers you can recommend? Preferably something reliable

 

[johndb]

Are there any good free hosting providers you can recommend? Preferably something reliable

I am currently using this provider for my virtual servers (not free but reasonably priced): https://islandhosting.com/