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 &amp;&amp; !$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.

You may also like...