I really dont know much about php and SQL, so it took me a great deal of time to figure this out.
To start with, I thought I could do it in the easy wat, so I bought two different packages on the “Asset store” that did not fulfil my purpose.. they were simply to complicated and not up to date, and after having used alot of time on these assets, I simply gave up on them – the assets I tried was: Youlead and PHP Highscore
Then there was the really old page about “Server Side Highscores” on the unity wiki – this was probably the biggest help – but again, a bit outdated, and I had to understand a few things before I had it working.
And then lastly, I watched some of these videos about how to work with SQL and PHP, this was also a great help, and made the last bits and pieces fall into place.
After having gone through all the trouble, I created the simplest version of a connection to my SQL server that I could – I wanted to do it in such a way, that I could use this for future games for myself. I hope to update this post with more information if I find some.
display.php
This should be placed on a web server, that have access to the SQL database, the display will get the values back from the database, this means that there should be some data on the server before this will work.
<?php
header("Access-Control-Allow-Credentials: true");
header('Access-Control-Allow-Origin: *');
header('Access-Control-Allow-Methods: POST, GET, OPTIONS');
header('Access-Control-Allow-Headers: Accept, X-Access-Token, X-Application-Name, X-Request-Sent-Time');
$servername = "sqldatabaseurl";
$server_username = "sqllogin";
$server_password = "sqlpassword";
$dbName = "DatabaseName";
//make connection
$conn = new mysqli($servername, $server_username, $server_password, $dbName);
if(!conn){
die("Connection failed: " . mysqli_connect_error());
}
$query = "SELECT * FROM `tablename` ORDER by `score` DESC LIMIT 10";
$result = mysqli_query($conn, $query);
if(mysqli_num_rows($result) > 0){
//show data for each row
while($row = mysqli_fetch_assoc($result)){
echo " Name: ".$row['name']. " - Score: ".$row['score']."\n";
}
}
?>
addscore.php
The addscore.php should also be put on the webserver, this is where the score is posted. Please note how the secret key is added. This is done so it will not be possible to post “bogus” data to the database, unless you have the secret key.
<?php
header("Access-Control-Allow-Credentials: true");
header('Access-Control-Allow-Origin: *');
header('Access-Control-Allow-Methods: POST, GET, OPTIONS');
header('Access-Control-Allow-Headers: Accept, X-Access-Token, X-Application-Name, X-Request-Sent-Time');
$servername = "sqldatabaseurl";
$server_username = "sqllogin";
$server_password = "sqlpassword";
$dbName = "DatabaseName";
// Strings must be escaped to prevent SQL injection attack.
$name = $_POST["namePost"];
$score = $_POST["scorePost"];
$hash = $_POST["hashPost"];
//make connection
$conn = new mysqli($servername, $server_username, $server_password, $dbName);
if(!conn){
die("Connection failed: " . mysqli_connect_error());
}
$secretKey="insertsecretcodehere";
$real_hash = md5($name . $score . $secretKey);
if($real_hash == $hash){
$sql = "INSERT INTO SuperBao_SP_Easy (name, score)
VALUES ('".$name."','".$score."')";
$result = mysqli_query($conn ,$sql);
}
//Check Connection
if(!$conn){
die("Connection Failed. ". mysqli_connect_error());
}
?>
SQL_Highscore.cs
This should be added in Unity, this is what will post values to the php files, that will then carry the data on to the database.
using UnityEngine;
using UnityEngine.UI;
using System.Collections;
public class SQL_HighScore : MonoBehaviour
{
private string secretKey = "thisshouldbethesameinthefiles";
public string addScoreURL = "http://yoururl.com/game/HighScore/addscore.php";
public string highscoreURL = "http://yoururl.com/game/HighScore/display.php";
public Text highscorelist;
//We start by just getting the HighScores, this should be removed, when you are done setting up.
void Start()
{
StartCoroutine(GetScores());
}
// This is for debugging purposes, you can run this when clicking
// on a button, to see that scores are added. Remove when done setting up.
public void PostRandomScore()
{
int randomscore = (int)Random.RandomRange(15.0f, 400.0f);
PostScores("tester", randomscore);
}
// This is for debugging purposes, you can run this when clicking on
// a button, to see the highscores that have been added. Remove when done setting up.
public void GetTheScores()
{
StartCoroutine(GetScores());
}
//This is where we post
public void PostScores(string name, int score)
{
string hash = Md5Sum(name + score + secretKey);
WWWForm form = new WWWForm();
form.AddField("namePost", name);
form.AddField("scorePost", score);
form.AddField("hashPost", hash);
WWW www = new WWW(addScoreURL, form);
}
//This co-rutine gets the score, and print it to a text UI element.
IEnumerator GetScores()
{
WWW wwwHighscores = new WWW(highscoreURL);
yield return wwwHighscores;
if (wwwHighscores.error != null)
{
print("There was an error getting the high score: " + wwwHighscores.error);
}
else
{
highscorelist.text = wwwHighscores.text;
}
}
// This is used to create a md5sum - so that we are sure that only legit scores are submitted.
// We use this when we post the scores.
// This should probably be placed in a seperate class. But isplaced here to make it simple to understand.
public string Md5Sum(string strToEncrypt)
{
System.Text.UTF8Encoding ue = new System.Text.UTF8Encoding();
byte[] bytes = ue.GetBytes(strToEncrypt);
// encrypt bytes
System.Security.Cryptography.MD5CryptoServiceProvider md5 = new System.Security.Cryptography.MD5CryptoServiceProvider();
byte[] hashBytes = md5.ComputeHash(bytes);
// Convert the encrypted bytes back to a string (base 16)
string hashString = "";
for (int i = 0; i < hashBytes.Length; i++)
{
hashString += System.Convert.ToString(hashBytes[i], 16).PadLeft(2, '0');
}
return hashString.PadLeft(32, '0');
}
}