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:
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:
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:
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:
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:
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:
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:
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:
B4X:
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:
B4X:
<?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:
B4X:
scores.php?secret=YOUR_PASSWORD&id=add&name=USER&score=USER_SCORE&type=GAME_TYPE&deviceID=USER_DEVICEIDview
Usage:
B4X:
scores.php?secret=YOUR_PASSWORD&id=view&start=START_NUMBER&end=END_NUMBER&type=GAME_TYPEshowUser
Usage:
B4X:
scores.php?secret=YOUR_PASSWORD&id=showUser&deviceID=USER_DEVICEID&type=GAME_TYPE&limit=TRAILING_SCORESAlso, 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:
B4X:
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 SubThat'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:
B4X:
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
Last edited:
