Sinder Keyforge Card Ranking – PHP, SQL and Unity
For a while now I wanted to make a card ranking system in which users are offered two cards and then choose which they like better.
However my web development knowledge is very limited, and I felt overwhelmed trying to learn PHP backend and HTML frontend at the same time. I don’t even remember how I stumbled onto it, but I found this video series about PHP backend with MySQL and Unity front end. I already know Unity, so this was right up my alley.
I followed the tutorial which uses MAMP as a server instance, and shows how to do basic login, and then it was time to start on my own stuff.
Cards Cards Cards
First thing I knew I needed was a database of KeyForge cards. I asked the nice people on the Archon Arcana Discord, and they gave me a Json file with all the cards.
And when I say all the cards, I mean all of them. SkyJedi has scrapped the master vault for every variation of every card, a total of over 9000 card objects.
MAMP does not have an import for Json, and also I didn’t want all 9000+ entries in my table.
So after a lot of googling I settled on writing my own PHP code that would load what I needed from the Json file into my SQL database.
The Json file has a lot more data like is_maverick and is_anomaly which I decided not to include in my table.
Then I had to find a way to get all that data from the Json file into the table, after a lot of googling and tinkering, I came up with this:
<?php
$con = mysqli_connect('localhost', 'root', 'root', 'keyforgedb');
if (mysqli_connect_errno())
{
echo "1 - connection failed";
exit();
}
$string = file_get_contents("AllCards.json");
$json_a = json_decode($string, true);
$row_count = 0;
foreach ($json_a as $block => $block_data) {
$house = "";
$card_title = "";
$front_image = "";
$card_type = "";
$rarity = "";
$expansion = "";
$id = "";
$is_maverick = "";
$is_enhanced = "";
foreach ($block_data as $key => $value) {
if ($key == 'house') {
$house = mysqli_real_escape_string($con, $value);
}
if ($key == 'card_title') {
$card_title = mysqli_real_escape_string($con, $value);
}
if ($key == 'front_image') {
$front_image = mysqli_real_escape_string($con, $value);
}
if ($key == 'card_type') {
$card_type = mysqli_real_escape_string($con, $value);
}
if ($key == 'rarity') {
$rarity = mysqli_real_escape_string($con, $value);
}
if ($key == 'expansion') {
$expansion = mysqli_real_escape_string($con, $value);
}
if ($key == 'id') {
$id = $value;
}
if ($key == 'is_maverick') {
$is_maverick = $value;
}
if ($key == 'is_enhanced') {
$is_enhanced = $value;
}
}
if (!$is_maverick && !$is_enhanced) {
$row_count++;
echo $card_title . " " . $house . "<br>";
$inseruserquery = "INSERT INTO cards (house, card_title, front_image, card_type, rarity, expansion, id) VALUES ('" . $house . "', '" . $card_title . "', '" . $front_image . "', '" . $card_type . "', '" . $rarity . "', '" . $expansion . "', '" . $id. "');";
if(!mysqli_query($con, $inseruserquery)) {
echo("Error description: " . mysqli_error($con) . "<br>");
echo $row_count;
}
}
}
echo $row_count++ . "<br>";
?>
This put all the cards I needed into the database, with a few extra entries like top and bottom halves of gigantics and anomalies in all 7 houses.
Getting the card to display in Unity
First thing I needed to do is get cards from the database, through PHP and into Unity. I wrote a PHP that would get a House and an Expansion and return the cards in Json.
IEnumerator GetCards()
{
WWWForm form = new WWWForm();
form.AddField("expansion", expansionField.text);
form.AddField("house", houseField.text);
UnityWebRequest request = UnityWebRequest.Post("http://localhost/keyforgedb/get_cards.php", form);
yield return request.SendWebRequest();
CardsData.LoadFromJson(request.downloadHandler.text);
SceneManager.LoadScene(1);
}
The above is some test code to get the cards from the PHP, which looks like this:
<?php
$con = mysqli_connect('localhost', 'root', 'root', 'keyforgedb');
if (mysqli_connect_errno())
{
echo "1 - connection failed";
exit();
}
$expansion= $_POST["expansion"];
$house = $_POST["house"];
$getcardsquery = "SELECT id, card_title, front_image, rarity FROM cards WHERE expansion=" . $expansion . " AND house='" . $house . "';";
$getcards = mysqli_query($con, $getcardsquery) or die("2 - name check query failed");
$data = array();
// $data['cards'] = "";
while($row = mysqli_fetch_assoc($getcards)) {
$data[] = $row;
}
$root = array('cardsArray' => $data );
header('Content-Type: application/json');
echo json_encode($root);
?>
The tricky part was in order to use Unity’s Json deserializer, I had to have a name for the root element, so I added it.
Next thing on my plate was displaying a card image, so I came up with this:
IEnumerator DownloadImage(string imageUrl, string cacheName, Image toImage)
{
UnityWebRequest request = UnityWebRequestTexture.GetTexture(imageUrl);
yield return request.SendWebRequest();
if (request.isNetworkError || request.isHttpError)
Debug.Log(request.error);
else
{
if (request.downloadHandler.data != null)
{
AssignTextureToImage(((DownloadHandlerTexture)request.downloadHandler).texture, toImage);
CacheData(request.downloadHandler.data, cacheName);
}
}
}
private void AssignTextureToImage(Texture2D tex, Image toImage)
{
Sprite sprite = Sprite.Create(tex, new Rect(0, 0, tex.width, tex.height), new Vector2(tex.width / 2, tex.height / 2));
toImage.overrideSprite = sprite;
}
private void CacheData(byte[] data, string cacheName)
{
System.IO.File.WriteAllBytes(Application.persistentDataPath + cacheName, data);
Debug.Log("Writing Success");
}
You can see I also cached the image locally, so I won’t have to download them every time. All that was left was to load the cached image if it exists:
public void LoadImageTo(string imageUrl, Image toImage)
{
string cacheName = imageUrl.Substring(imageUrl.LastIndexOf("/"));
string localFilePath = Application.persistentDataPath + cacheName;
if (File.Exists(localFilePath))
{
Debug.Log("Loading cached image");
byte[] fileData = File.ReadAllBytes(localFilePath);
Texture2D tex = new Texture2D(2, 2);
tex.LoadImage(fileData);
AssignTextureToImage(tex, toImage);
}
else
{
Debug.Log("Downloading image");
StartCoroutine(DownloadImage(imageUrl, cacheName, toImage));
}
}
I hope you follow along as I develop this app, which is 90% for fun. Though, if it’s popular and useful, I fully intend to make it public.
