Question How to store multiple database results in a class and delete them beforehand to avoid duplicates?

Hello,

I would like to save data that I have fetched from the database (the query does not yet protect against SQL injection) in the 'Card' class so that I can access it later. The whole thing should later be visualized in a family tree-like structure.

First I get the data from the database if the username matches:

<?php



    mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);

    $con = mysqli_connect('localhost', 'root', 'root', 'unityaccess');

  

    //check connection

    if (mysqli_connect_errno())

    {

        echo "Error #1"; // error code #1 = Connection failed!

        http_response_code(400);

        exit();

    }



    $username = $_POST["name"];

    // $level = $_POST["level"];

    $sql = "SELECT * FROM playercards WHERE nickname ='" . $username . "';"; //"'"' AND level ='" . $level . "';";



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



    if (mysqli_num_rows($result) > 0) {

        while($row = $result->fetch_assoc()) {

            echo $row ["CardID"] . "+" . $row ["CardName"] . "+" . $row ["score"] . "+" . $row ["nos"] . "+" . $row ["tmp"] . "+" . $row ["OfficerID"] . "+" . $row ["ParentID"];

        }

    }

    else {

        echo "No cards found.";

        exit();

    }

    



?>

For each result that is available, the query fetches a series of data that is delimited with a '+'. He then saves this in the Card class:

Code (CSharp):

1. string[] DataStringArray = www1.downloadHandler.text.Split("+"); // Splits the data into an string array.
2.  
3. //All content needs to be deleted first to avoid duplicates.
4. //Fills the Card class with the data from the database.
5. Level61.CardID = int.Parse(level61DataStringArray[0]);
6. Level61.CardName = level61DataStringArray[1];
7. Level61.score = int.Parse(level61DataStringArray[2]);
8. Level61.nos = int.Parse(level61DataStringArray[3]);
9. Level61.tmp = int.Parse(level61DataStringArray[4]);
10. Level61.officerID = int.Parse(level61DataStringArray[5]);
11. Level61.parentID = int.Parse(level61DataStringArray[6]);

Code (CSharp):

1. public class Card : MonoBehaviour
2. {
3.  
4.     public static int CardID;
5.     public static string CardName;
6.     public static int score; // The score this card creates for the player.
7.     public static int level ;
8.     public static int nos = 0; // Actual number of soldiers;
9.     public static int tmp = 0; // Target number of soldiers;
10.     public static int officerID;
11.     public static int parentID;
12.  
13. }

As you can see from the code, it only saves this for the 'Level61' map. This is because I previously had a separate query for each map level, but that was way too much.
But it actually worked, so it didn't throw any errors or anything.

I would now like to change this so that for every database hit
automatically makes a new entry. So for every card
a new entry must be made in the card class.

How do I best implement this? Only the section with the string array would have to be changed I guess...

It would also be nice if you could tell me how to delete existing data. If the player requests all data again, e.g. because he restarted the app, the Card class should be empty so as not to create duplicates.

Kind regards
Tobias

 

You shouldn't mess with formatting your data manually. Literally every programming language has a json library, most of the time even built-in. So in PHP you would just use json_encode with the array of your objects / rows and you get a nicely formatted result.

Note on the Unity side Unity gives you the JsonUtiity. However it is limited as it only accepts an object as root object. So you can not have an array at the rop element.

So on the PHP side you would do

PHP:

$rows = $result->fetch_all(MYSQLI_ASSOC);
$data = ["cards"=>$rows];
echo json_encode($data);

This should generate a result like

Code (CSharp):

1. {
2.    "cards": [
3.       {
4.          "CardID": 42,
5.          "CardName": "Your name",
6.          "score": 123,
7.          "level": 5,
8.          "nos": 456,
9.          "tmp": 789,
10.          "OfficerID": 3,
11.          "ParentID": 7
12.       },
13.       {
14.          "CardID": 43,
15.          "CardName": "Your name",
16.          "score": 123,
17.          "level": 6,
18.          "nos": 456,
19.          "tmp": 798,
20.          "OfficerID": 3,
21.          "ParentID": 7
22.       }
23.    ]
24. }

On the Unity side you could create a serializable class that represents one of your "Card" (not a monobehaviour) so you can deserialize it like this:

Code (CSharp):

1.  
2.     [System.Serializable]
3.     public class Card
4.     {
5.         public static int CardID;
6.         public static string CardName;
7.         public static int score; // The score this card creates for the player.
8.         public static int level ;
9.         public static int nos = 0; // Actual number of soldiers;
10.         public static int tmp = 0; // Target number of soldiers;
11.         public static int officerID;
12.         public static int parentID;
13.     }
14.     [System.Serializable]
15.     public class Cards
16.     {
17.         public List<Card> cards = new List<Card>();
18.     }
19.  
20.     // [ ...]
21.     Cards data = JsonUtility.FromJson<Cards>( www1.downloadHandler.text );
22.  

This would automatically create one "Card" for each card in the cards array that you return.

ps: You already said your query isn't secure. I would strongly recommend switching to PDO (PHP Data Object) to access your database. Use prepared statements and named parameters to automatically sanetize the parameters.

PHP:

$pdo = new PDO( 'mysql:host=' . $yourHost .';dbname=' . $yourDBName.';charset=utf8', $yourDBUser, $yourDBPass);
$stmt = $pdo->prepare("SELECT * FROM playercards WHERE nickname = :username");
if (!$stmt->execute([":username" => $username])){
    // error
}
$result = $request->fetchAll(PDO::FETCH_ASSOC);
// [ ... ]


 

Wow, thank you very much for this detailed answer

I actually wanted to get the first module of the game running and then switch to pdo/prepared statements, but since you did so much preparatory work with your answer, I'll do that straight away.

 

One more question:

The coroutine returns the player's entire card set using the 'cards' list. If the player edits his cards (adds new ones, deletes existing ones or edits them), should I add/edit/remove corresponding entries from the list or just reload the whole list again?

If I reload all, I would have to delete the existing entries so that there are no duplicates, right?
Do I then just have to delete the contents or the 'cards' list itself, because the code creates a new list with the name?