Android Tutorial Tutorial - Online Scoreboard

admac231 · Jan 16, 2012

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:

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_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:

B4X:

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:

B4X:

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:

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 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:

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

pluton · Mar 9, 2012

PharCyDeD said:
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

Click to expand...

Php changes:

B4X:

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

Altered B4A Code

...cut

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

Ok
As I see you have wrong set up your database name

In your details Databasename is: db404553659
And in your PHP
$mysqlDatabaseName = "db404553659.db.1and1.com";

PharCyDeD · Mar 9, 2012

By database name it means the host. The host name is required to make a connection. Thanks for taking a peek though.

pluton · Mar 9, 2012

PharCyDeD said:
By database name it means the host. The host name is required to make a connection. Thanks for taking a peek though.

Did you try to put as host: localhost ?

PharCyDeD · Mar 9, 2012

With my particular host localhost does not work. To be honest...I can't recommend against a hosting company any more than I can 1and1 hosting. They offer pretty good deals, but unless you are just some average joe WordPress blogger it is just not enough freedom.

timo · Mar 13, 2012

As I can see it, in your php, database name includes the host name, or something is not clear. You normally should separate them.
Try maybe this

B4X:

<?php
$mysqlHost = "db404553659.db.1and1.com";
$mysqlDatabaseName = "db404553659";
$mysqlUsername = "dbo404553659";
$mysqlPassword = "Basic4Android";

$conn_DB=mysql_connect($mysqlHost,$mysqlUsername ,$mysqlPassword ) or die(mysql_error());
mysql_select_db($mysqlDatabaseName) or die(mysql_error());

$query="SELECT * FROM table....etc.

PharCyDeD · Mar 13, 2012

Yeah, the name is recycled as it is generated by my hosting when creating a MySQL. They are always db"numbershere". Even the username is the same aside from adding dbo to the front of the numbers. There is no way to change it either. Such a pain. :BangHead:

timo · Mar 13, 2012

My provider act the same way, but with the above code it works.

PharCyDeD · Mar 13, 2012

I added:

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

to my php file but still get this (the same problem as before):

timo · Mar 14, 2012

Can you access it with MySql Query Browser from your PC?

timo · Mar 15, 2012

I asked you that because I saw "dbo404553659@%' and the char % is normally blocked if the provider denied access from everywhere. If you can only access from the hosting server, the provider automatically add "...@something" and you can't change it in the db. If I'm right with this, you maybe have to verify the permissions granted to that db user. Adding a score need writing permission.
Even if you have the "...@something" situation, the only thing you cannot do is to send via get/post from phone the dbServer/dbname/user and password, you have to put them directly in the php file. Imei and other data can generally be sent with no problem.

PharCyDeD · Mar 17, 2012

I'll check into this tomorrow. Thanks for your continued help with this!

squaremation · Apr 26, 2012

I am probably dense but, how do I pass the the Variables that hold my scores, user name, ect. to 'name', 'score' ect. in the db?

tamadon · Jul 12, 2012

How I get it to work

I was previously having problem connecting to the database as I kept getting the unknown MYSQL server message.

I modified the connection variables a little but so I am sharing what I did in case you are having the same issue as I was. I am using Hostgator hosting btw.

B4X:

<?php

$myPassword = "securitypass";
$mysqlDatabaseName = "datname";
$mysqlUsername = "usrname";
$mysqlPassword = "mymysqlpass";
$mysqlHost = "localhost";

if($_GET['secret']!=$myPassword){
die("You shouldn't be here...");
}
mysql_connect($mysqlHost, $mysqlUsername, $mysqlPassword) or die(mysql_error());
mysql_select_db($mysqlDatabaseName) 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";
}
}
?>

Also remember that the Type variable should be gameType or whatever that matches the column in your database

roarnold · Dec 27, 2012

I utilize MySql and PHP however in the app I specifically authenticate through a port. Depending on what ports are available may or may not allow the access from the app.

Example:

request.InitializeGet("http://cowboy.ath.cx:88/cad/site2/mobile.php" & "?id=" & id & "&typeofcall=" & typeofcall & "&location=" & loc & "&narrative=" & narrative & "&edit=Register")

In this case I am just pulling information for a list. Note if the 88 is not there it will not allow the connection between the DB-PHP-App.

R

Smee · Dec 30, 2012

PharCyDeD said:
I'll check into this tomorrow. Thanks for your continued help with this!

did you ever sort this problem out

Smee · Jan 13, 2013

i do not know what i am missing here but with the following line i get a compilation error

B4X:

        req.InitializeGet("http://website.com/scores.php?secret=YOUR_PASSWORD&id=add&name=USERNAME&score=123456 & " & Type=1 & "deviceID=" &  user_DEVICEID)

Compiling code. Error
Error compiling program.
Error description: Object reference not set to an instance of an object.
Occurred on line: 124
req.InitializeGet("http://website.com/scores.php?secret=YOUR_PASSWORD&id=add&name=USERNAME&score=123456 & " & Type=1 & "deviceID=" & user_DEVICEID)
Word: =

:BangHead:

Smee · Jan 13, 2013

Ok I found that error. the type needed to be enclosed in "". now the program works except when the routine fetches the high scores i get an error from this code

B4X:

Sub httpC_ResponseSuccess (Response As HttpResponse, TaskId As Int)
    lstScore.Clear
    Dim result As String
 result = Response.GetString("UTF8")

result = Response.GetString("UTF8")

LastException android.os.NetworkOnMainThreadException

This is the full log
PackageAdded: package:scores.test

** Activity (main) Create, isFirst = true **
** Activity (main) Resume **
Connected to B4A-Bridge
Installing file.

main_httpc_responsesuccess (B4A line: 88)
result = Response.GetString("UTF8")
android.os.NetworkOnMainThreadException
at android.os.StrictMode$AndroidBlockGuardPolicy.onNetwork(StrictMode.java:1118)
at libcore.io.BlockGuardOs.recvfrom(BlockGuardOs.java:163)
at libcore.io.IoBridge.recvfrom(IoBridge.java:513)
at java.net.PlainSocketImpl.read(PlainSocketImpl.java:488)
at java.net.PlainSocketImpl.access$000(PlainSocketImpl.java:46)
at java.net.PlainSocketImpl$PlainSocketInputStream.read(PlainSocketImpl.java:240)
at org.apache.http.impl.io.AbstractSessionInputBuffer.fillBuffer(AbstractSessionInputBuffer.java:103)
at org.apache.http.impl.io.AbstractSessionInputBuffer.read(AbstractSessionInputBuffer.java:120)
at org.apache.http.impl.io.ChunkedInputStream.getChunkSize(ChunkedInputStream.java:211)
at org.apache.http.impl.io.ChunkedInputStream.nextChunk(ChunkedInputStream.java:183)
at org.apache.http.impl.io.ChunkedInputStream.read(ChunkedInputStream.java:155)
at org.apache.http.impl.io.ChunkedInputStream.read(ChunkedInputStream.java:175)
at org.apache.http.impl.io.ChunkedInputStream.exhaustInputStream(ChunkedInputStream.java:289)
at org.apache.http.impl.io.ChunkedInputStream.close(ChunkedInputStream.java:262)
at org.apache.http.conn.BasicManagedEntity.streamClosed(BasicManagedEntity.java:179)
at org.apache.http.conn.EofSensorInputStream.checkClose(EofSensorInputStream.java:266)
at org.apache.http.conn.EofSensorInputStream.close(EofSensorInputStream.java:213)
at java.io.InputStreamReader.close(InputStreamReader.java:145)
at org.apache.http.util.EntityUtils.toString(EntityUtils.java:139)
at anywheresoftware.b4a.http.HttpClientWrapper$HttpResponeWrapper.GetString(HttpClientWrapper.java:469)
at scores.test.main._httpc_responsesuccess(main.java:533)
at java.lang.reflect.Method.invokeNative(Native Method)
at java.lang.reflect.Method.invoke(Method.java:511)
at anywheresoftware.b4a.BA.raiseEvent2(BA.java:165)

Smee · Jan 13, 2013

This just does not seem to be working for me

i remarked the offending line of code out so that i could get the program to run and then i executed the statement which adds a record to the database.
Unfortunately that does not work either even though the code seems to execute ok

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

Erel · Jan 13, 2013

Response.GetString is a deprecated method and will not work with Android 4+.

The simplest solution is to use HttpUtils2 instead.

sanjibnanda · Mar 17, 2013

error in sql on excecution

hi,
the code works fine, but on clicking the 'Me' button, i get the error as attached in the image

the query run from the browser, shows following error

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1

what can be the issue?

regards,
sanjib

Android Tutorial Tutorial - Online Scoreboard

Active Member

Active Member

Active Member

Active Member

Active Member

Active Member

Active Member

Active Member

Active Member

Active Member

Active Member

Active Member

Active Member

Active Member

Well-Known Member

Well-Known Member

Well-Known Member

Well-Known Member

B4X founder

Active Member

Similar Threads

Privacy & Transparency

Privacy & Transparency