PHP SQL database leaderboard

AshumBesher

Hello. I've been trying to figure out how to set up a simple online leaderboard using a database on 000webhost.com. I've searched through the GameSalad documentation, forums, and Google but instructions are outdated/incomplete. It's been a few days of pretty much non stop trial and error, so I thought it was about time I asked for help.

I have a table with 2 columns, 'Name' and 'Score' in both my GameSalad project and my SQL database. All I want is the ability to send a player's name and score as a new row to the database via the GameSalad project, and also the ability to retrieve the database of scores from the server to the project.

I'm very much stuck, I've tried editing example PHP files I found online but I just can't figure it out. If anybody has some exact code which I can literally copy and paste, that would be great.

muusi

Here try this for posting the score and name.

<?php

$servername = "localhost";

  $username = "your_username";

  $password = "your_password";

  $dbName = "your_db_name";

  $port = "3306";

  $link = @mysqli_connect($servername, $username, $password, $dbName, $port);

  // database connection strings. change these to your DB and Table names.

   

  $tableName = "Scores";

  if (mysqli_connect_errno()) {

printf("Connection failed in send.php %s\n", mysqli_connect_error());

exit();

}

// connect to the table

mysqli_select_db($link, $dbName)or die("cannot select DB");

// lets prepare some files to capture what is going on.

$incomingJson = 'json.txt';

$incomingSig = 'sig.txt';

$sqlErrorLog = "sqlErrors.txt";

// initialize the string with a blank value

$string = "";

// start SEND data

if ($_SERVER['REQUEST_METHOD'] === 'POST') {

//capture incoming data

error_reporting(1);

$sig = $_POST["sig"];

$jsondata = $_POST["params"];

// this line captures the sent data so you can figure out what you need to send back.

file_put_contents($incomingJson,$jsondata);

   file_put_contents($incomingSig,$sig);

// this line tells the application that the data send was successful.\

echo '{"Status":"Success"}';

// convert JSON to an array\

$array = json_decode($jsondata, TRUE);

//get the total number of objects in the array

$arrlength = count($array['Children']['1']['Properties']);

// set while loop index

$i = 0;

//loop through array node and get row values

while ($i < $arrlength ) {

// get row value

$value = $array['Children']['1']['Properties'][$i]['Value']."\n";

// convert delimited string to an array

$arrayPieces = explode("|", $value);

     

// get array values. This section would have to be modified to capture each value you are interested in.

$rowName = $arrayPieces[0]; // this variable will be blank if you don't name your rows. 

$playerName = $arrayPieces[1];

$playerScore =$arrayPieces[2];

$clean_name = preg_replace('/[^a-zA-Z0-9]/', '', $playerName);

$clean_score = preg_replace('/[^0-9]/', '', $playerScore);

   

       // construct SQL statement\

        $sql="INSERT INTO ".$tableName." SET name = '".$clean_name."', score = '".$clean_score."'";

// insert SQL statement\

$result=mysqli_query($link, $sql);

// catch any errors\

if($result){

// if successful do nothing for now.\

}

else {

// if failure, write to custom log\

$sqlError = "Error writing to database\n";

file_put_contents($sqlErrorLog, $sqlError, FILE_APPEND);

}

$i++;

} // end of WHILE

} // end of POST

// close the SQL connection

mysqli_close($link);

?>

AshumBesher

Hello. Thanks for your fast response.

That worked great! The names and scores post perfectly.

Do you happen to have the script to Get the table?

Thanks :)

AshumBesher

Basically now all I want is to be able to display the list of names and scores from the database (from highest to lowest) in my GameSalad project. Essentially copying the database table from the server to the table in my GS project.

If anyone can help with that script I'd appreciate it :)

muusi

Hi @AshumBesher, here's a php for that. You need to look up id of your table from your projects object.xml and replace it to the code below (id12345) and of course change the table name and value types and remove any extra columns and rename them to match yours.

<?php

$servername = "localhost";

  $username = "your_username";

  $password = "your_password";

  $dbName = "your_db_name";

  $port = "3306";

  $tableName = "leaderboars";

  $link = @mysqli_connect($servername, $username, $password, $dbName, $port);

  // database connection strings. change these to your DB and Table names.\

  $db = new mysqli($servername, $username, $password, $dbName, $port);

   

  if (mysqli_connect_errno()) {

printf("Connection failed in get.php %s\n", mysqli_connect_error());

exit();

}

// connect to the table\

//mysqli_select_db($link, $dbName)or die("cannot select DB");

// lets prepare some files to capture what is going on.\

  

$incomingJson = 'json.txt';

//$fullArray = 'fullArray.txt'; // needed if you enable the debugging section below\

$sqlErrorLog = "sqlErrors.txt";

// initialize the string with a blank value\

$string = "";

// start GET data\

if ($_SERVER['REQUEST_METHOD'] === 'GET') {

// initialize the JSON body variable\

$jsonBody="";

// get table contents\

$query = sprintf("SELECT * FROM `%s`", $tableName);

$res = $db->query($query);

if ($res === false) {

$err = sprintf("Invalid query: %s\nWhole query: %s\n", $db->error, $query);

throw new Exception($err);

} 

     

// construct an array to hold the data we pull from mySQL\

while($row = $res->fetch_array(MYSQLI_BOTH))

{

$rows[] = $row;

}

//$rows = $res->fetch_array(MYSQLI_BOTH);

// get the number of rows in the array. We need this in the JSON return\

$arrlength = count($rows);

//loop through array node and get row values\

for($i = 0; $i < $arrlength; $i++) {

  $playerID = $rows[$i]["playerID"];

$playerName =$rows[$i]["playerName"];

$playerStats = $rows[$i]["playerStats"];

// table row numbers. our index starts at 0, so we want to increment it by 1 to get valid row numbers.\

$tableRow = $i+1;

// construct the JSON return from our data\

$jsonString = '{"Name":"'.$tableRow .'","Value":"|'.$playerID.'|'.$playerName.'|'.$playerStats.'|"},';

// append the JSON return with the new data\

$jsonBody=$jsonBody.$jsonString;

//}

// increase index and loop again if not at end of array.\

}

// construct the JSON response\

// this is the header of the JSON return. It will have to be adjusted to match whatever your app is expecting. We have to define this here to get the row count above.\

$jsonHeadher='{"Properties":[],"Name":"","Children":[{"Properties":[{"Name":"rowCount","Value":'.$arrlength.'},{"Name":"columnCount","Value":3},{"Name":"0-1-name","Value":"playerID"},{"Name":"0-1-type","Value":1},{"Name":"0-2-name","Value":"playerName"},{"Name":"0-2-type","Value":1},{"Name":"0-3-name","Value":"playerStats"},{"Name":"0-4-type","Value":2},{"Name":"0-4-name","Value":"auto_i"},{"Name":"0-3-type","Value":2}],"Name":"id12345_headers","Children":[]},{"Properties":[';

// this is the footer of the JSON return. Again it will have to be adjusted to match whatever your app is expecting.\

$jsonFooter='],"Name":"id12345","Children":[]}]}';

// removes an extra comma that the loop above leaves behind\

$jsonBody=rtrim($jsonBody, ",");

// constructing the full JSON return\

$returnedJson=$jsonHeadher.$jsonBody.$jsonFooter;

// write the JSON data so the app can read it.\

echo $returnedJson;

} // end of get\

// close the SQL connection\

mysqli_close($link);

?>

AshumBesher

You're a genius! I'm getting a successful callback, when using that code, thanks a lot!

One more thing if it's not too much trouble, is it possible for the table rows being imported to my GameSalad Project to be sorted from highest score to lowest?

I really appreciate your help :)

muusi

https://forums.gamesalad.com/discussion/comment/614877#Comment_614877

Just use DESC in your SQL statement