Getting multiple rows from php database

Unity Engine
1

Hey all, I’m stumped. Been trying to figure this one out but can’t get it to work, turning to the wisdom of the forums!

Been working with a database and php files, I can get grab 1 value pretty easily but I am trying to figure out how to get multiple values from a single row and can’t figure it out. Here’s what I’ve got!

$sql = "SELECT itemname, description, price FROM items WHERE itemid = '" . $itemID . "'";

$result = $conn->query($sql);

if($result->num_rows > 0)
{
    //Store item price
    //get the value in row "price"
    $itemPrice = $result->fetch_assoc()["price"];

    echo $itemPrice;
}

Here’s the important code in my php file, it gets a single item in the row (the “price” value)

public IEnumerator TestItem(int _itemid)
    {
        WWWForm form = new WWWForm();
        //itemID id the variable in the php file
        form.AddField("itemID", _itemid);
        using (UnityWebRequest www = UnityWebRequest.Post(testURL, form))
        {
            yield return www.SendWebRequest();
            if (www.result != UnityWebRequest.Result.Success)
            {
                Debug.Log(www.error);
            }
            else
            {
                Debug.Log(www.downloadHandler.text);
            }
        }
    }

and my code to get the info on the Unity end.

I’ve found a bunch of different ways to get multiple values, but none seem to be working. Any ideas? I feel like this is pretty simple but I’m just missing something small

2

So here’s the closest I have found:

$sql = "SELECT itemname, description, price FROM items WHERE itemid = '" . $itemID . "'";

$result = $conn->query($sql);

if($result->num_rows > 0)
{
    //output data of each row
    //create an array
    $rows = array();
    //fill that array
    while($row = $result->fetch_assoc())
    {
        $rows[] = $row;
    }
    //after filling the arrray
    //encode into json
    //pass the array
    echo json_encode($rows);
}

which prints this:
[{“itemname”:“Clay”,“description”:“Some soft clay.”,“price”:“2”}]

which is close

3

Isn’t this more of a pure SQL question?

You might have more luck on a forum for SQL.

4

Yes, this is more about php, which I’m actually currently have to work with as well ^^. In your first code example you don’t have any recognisable structure when you return the data. You just spit out the first elements price value. So yes, you usually want to use something like json to transfer the result back to Unity. Your code for returning multiple rows should work that way. However you would need a json library on the Unity side, or make it compatible with Unity’s JsonUtility which has some restrictions. @Kurt-Dekker yes, it’s perfectly fine to use JsonUtility for simple things, you just have to take its limits into account as you have to do with any API ^^.

Regardless of the Unityside, NEVER EVER use such php code in a public accessible API. When someone sends something like the following as “item id” to your php script, your database will be gone:
"; drop database; select "

Please, look up PDO and prepared statements. It allows to link / pass arguments to placeholders in the query and it automatically takes care of properly escaping the incoming data so no sql injections are possible. I would generally recommend to have a look at the PDO and PDOStatement documentation.

1 Like
5

Yeah I should probably look there, but I need help on both sides of the code haha. Figured I should see if anyone knows on here

6

For the recognizable structure that gets returned, is that not what the echo is doing? I’m still trying to learn how the php files function, but the only code I’m familiar with is c# so trying to understand it through that lense.

If it’s not a recognizable structure, what should I use instead? I am using simplejson on the unity side, but having trouble understanding what I am actually doing.

7

As Bunny points out, raw SQL like this is also pretty dangerous. I use Heroku and make my endpoints there. They have a hobby level you can set up and test with. You can write in Javascript, PhP or Python, and it all seems to work pretty flawlessly. I chose their Django wrapper. Then your Unity side just becomes REST API calls made with UnityWebRequest.

8

Overview of what you’re doing:
a) getting a row from mysql (see Bunny83’s advice on prepared statements) (mysql, php land)
b) encoding said row into json: [{“itemname”:“Clay”,“description”:“Some soft clay.”,“price”:“2”}] (phpland)
c) TODO: read json into unity. (c#)

I would advice to look for json parser (GitHub - applejag/Newtonsoft.Json-for-Unity: Newtonsoft.Json (Json.NET) 10.0.3, 11.0.2, 12.0.3, & 13.0.1 for Unity IL2CPP builds, available via Unity Package Manager), you would be able to generate a class from your json data and use that one (look for json2csharp).