How do I sort and display an array to create a leaderboards in unity C#

Question

I'm having a bit of a problem at the moment I have to work with a database with employees. these employes have different data these are strings and ints the example of what is in the array is: Naam:Pino | Team:2 | VerkoopA:12 | VerkoopB:12 | NPS:40 | conversie:10.

this is an example of only one employee there are more in the database and I already split them in the code. The thing I want to do is make a leaderboards out of this data however I do not know how to seperate the different items in the Array and if I have seperated this how can I sort the array to see who hase the most saleA and who has the most saleB so I can make this a leaderboard with a filter.

Also is there an easy way to have a visual leaderboards that can display a certain amount of employees? Because the way I displayed it now it shows nothing.

I'm using Unity and my own server with Xampp

With kind regards,

Dh

Script:

using UnityEngine;
using UnityEngine.UI;
using System.Collections;

public class Leaderboard : MonoBehaviour
{

    // Array of all the employees
    public string[] leaderboard;

    Text txt;

    IEnumerator Start()
    {


        // Getting the leaderboard data from mySQL.
        WWW leaderboardsData = new WWW("http://localhost/leaderboards.php");
        yield return leaderboardsData;

        // Split every employee in the Array so they are all apart from each other. |
        // an example Message Naam:Pino | Team:2 | VerkoopA:12 | VerkoopB:12 | NPS:40 | Conversie:10 
        string leaderboardsDataString = leaderboardsData.text;
        leaderboard = leaderboardsDataString.Split(';');

        // for every employee in the array print every employee's data.
        foreach (string employee in leaderboard)
        {
            string employeeApart = employee;
            DisplayLeaderboards(employeeApart);
        }

    }

    // Display the leaderboards 1 by 1.
    void DisplayLeaderboards(string employeeApart)
    {
        print(employeeApart);

        txt = gameObject.GetComponent<Text>();
        txt.text = employeeApart;
    }



    string GetDataValue(string data, string index)
    {
        string value = data.Substring(data.IndexOf(index) + index.Length);
        if (value.Contains("|")) value = value.Remove(value.IndexOf("|"));
        return value;
    }

}

PHP:

<?php
$servername = "localhost";
$username =  "root";
$password = "";
$dbName = "test3";

//Make Connection
$conn = new mysqli($servername, $username, $password, $dbName);
//Check Connection
if(!$conn){
    die("Connection Failed. ". mysqli_connect_error());
}

$sql = "SELECT ID, Team, VerkoopA, VerkoopB, NPS, Conversie FROM Leaderboards";
$result = mysqli_query($conn ,$sql);


if(mysqli_num_rows($result) > 0){
    //show data for each row
    while($row = mysqli_fetch_assoc($result)){
        echo "ID:".$row['ID'] . "|Team:".$row['Team']. "|VerkoopA:".$row['VerkoopA']. "|VerkoopB:".$row['VerkoopB'] . "|NPS:".$row['NPS']. "|Conversie:".$row['Conversie'] . ";";
    }
}

?>

EDIT:

After programmers help, I decided to use json

I'm having a bit of a problem at the moment I have to work with a database with employees. these employes have different data the output of my PHP file is:

[{"ID":"1","Team":"2","VerkoopA":"35","VerkoopB":"12","NPS":"25","Conversie":"18"},{"ID":"2","Team":"1","VerkoopA":"55","VerkoopB":"2","NPS":"12","Conversie":"40"},{"ID":"3","Team":"2","VerkoopA":"12","VerkoopB":"12","NPS":"40","Conversie":"10"}]

The thing I want to do is make a leaderboards out of this data however I do not know how to seperate the different items in the Array By using the JsonHelper I get an error: ArgumentException: JSON must represent an object

Script:

using UnityEngine;
using UnityEngine.UI;
using System.Collections;

public class Leaderboard: MonoBehaviour
{
    // Array of all the employees

    [System.Serializable]
    public class LeaderBoard
    {
        public string ID;
        public string Team;
        public string VerkoopA;
        public string VerkoopB;
        public string NPS;
        public string Conversie;
    }



    Text txt;

    IEnumerator Start()
    {
        // Getting the leaderboard data from mySQL.
        WWW leaderboardsData = new WWW("http://localhost/leaderboards.php");
        yield return leaderboardsData;

        string leaderboardsDataString = leaderboardsData.text;

        print(leaderboardsDataString);
        LeaderBoard[] leaderboard;
        leaderboard = JsonHelper.FromJson<LeaderBoard>(leaderboardsDataString);

        // for every employee in the array print every employee's data.
        foreach (LeaderBoard employee in leaderboard)
        {
            DisplayLeaderboards(employee);
        }

    }


    // Display the leaderboards 1 by 1.
    void DisplayLeaderboards(LeaderBoard employeeApart)
    {
        Debug.Log("ID: " + employeeApart.ID);
        Debug.Log("Team: " + employeeApart.Team);
        Debug.Log("VerkoopA: " + employeeApart.VerkoopA);
        Debug.Log("VerkoopB: " + employeeApart.VerkoopB);
        Debug.Log("NPS: " + employeeApart.NPS);
        Debug.Log("Conversie: " + employeeApart.Conversie);
    }
}

PHP:

<?php
$servername = "localhost";
$username =  "root";
$password = "";
$dbName = "test3";

//Make Connection
$conn = new mysqli($servername, $username, $password, $dbName);
//Check Connection
if(!$conn){
    die("Connection Failed. ". mysqli_connect_error());
}


$sql = "select * from Leaderboards";
$result = mysqli_query($conn, $sql) or die("Error in Selecting " . mysqli_error($conn));

//create an array
$emparray = array();
while($row =mysqli_fetch_assoc($result))
{
    $emparray[] = $row;
}

echo json_encode($emparray);    
?>

Answer 1

Since you have access to the server and the server script(php), you should do this with json or xml instead of separating the data with |.

Send the data from database as json. On Unity side, receive the data with WWW class then convert the json back to class with JsonUtility.

Unity's JsonUtility does not support array so you will need a wrapper against it. You can get the JsonHelper class which allows json array here.

This is what I described above should look like:

[System.Serializable]
public class LeaderBoard
{
    public string ID;
    public string Team;
    public string VerkoopA;
    public string VerkoopB;
    public string NPS;
    public string Conversie;
}

Text txt;

IEnumerator _Start()
{


    // Getting the leaderboard data from mySQL.
    WWW leaderboardsData = new WWW("http://localhost/leaderboards.php");
    yield return leaderboardsData;


    string leaderboardsDataString = leaderboardsData.text;
    LeaderBoard[] leaderboard = JsonHelper.FromJson<LeaderBoard>(leaderboardsDataString);

    // for every employee in the array print every employee's data.
    foreach (LeaderBoard employee in leaderboard)
    {
        DisplayLeaderboards(employee);
    }

}

// Display the leaderboards 1 by 1.
void DisplayLeaderboards(LeaderBoard employeeApart)
{
    Debug.Log("ID: " + employeeApart.ID);
    Debug.Log("Team: " + employeeApart.Team);
    Debug.Log("VerkoopA: " + employeeApart.VerkoopA);
    Debug.Log("VerkoopB: " + employeeApart.VerkoopB);
    Debug.Log("NPS: " + employeeApart.NPS);
    Debug.Log("Conversie: " + employeeApart.Conversie);
}

On the php side, you have to use the json_encode function to encode your database information into json array before sending it to Unity. Not a php guy but you can learn more about that here and here.

Sorting the result:

You can sort by ID after receiving the data from the server with

leaderboard = leaderboard.OrderBy(c => c.ID).ToArray();

or by NPS

leaderboard = leaderboard.OrderBy(c => c.NPS).ToArray();

This must be done before the foreach loop.

EDIT:

Your json generated by php cannot be ready directly in Unity. Not good at php and can't fix the json data on php side. Although, I can fix it on Unity side.

Add {"Items": in front of the received string then add } at the end of it.

I made a simple function for this:

string fixJson(string value)
{
    value = "{\"Items\":" + value + "}";
    return value;
}

Include that function to your script, then simply replace

string leaderboardsDataString = leaderboardsData.text;

with

string leaderboardsDataString = fixJson(leaderboardsData.text);

Everything should now work.