Latest matches database setup

Discussion in 'Programming' started by Vengea, May 25, 2015.

  1. Vengea


    Hey! I got a pvp server and we are thinking of making a player's latest matches being saved into the database which would be used on our website. We are going to save all the 1vs1 matches into the database and I cant figure out how I wanna do this and how the database table would look like.

    First of all, check out the player profile site here:
    Im planning adding the Latest matches on the right side of the Hero Stats module.

    This is the General table:
    This is the Free-for-all table:

    For the 1vs1 matches table I would have to save Player 1's UUID and Player 2's UUID, who won and the score.
    But how would I be able to display it on the profile page?

    This is the select I currently use on the profile page:
    SELECT * FROM General, ffa WHERE General.UUID=ffa.UUID AND General.Name='$name'

    I use the general table to get the players username, which allows me to drop the mojangs API.
    But how would I be able to get Player 1's and Player 2's usernames and display it on the website?

    This is my suggestion on the Matches table:

    Since Im getting the player's UUID from the select, would the second select look like this?
    Select * from Matches WHERE Player1='$UUID' AND Player2='$UUID'

    $UUID would be the UUID I get from general table from the first select.

    Now comes my biggest challenge, how would I get the second player username?
    For example;
    In 1 match I am player1 but in another match im player2.

    Would I have to make 2 selects, where I take UUID from the Player1 row and UUID from the Player2 row and some way get their usernames from the General table?

    Im sorry if I am sounding very confusing, but I tried to explain this the best I could.
  2. In a way, a little confusing since you are jumping around.
    Let me dump what I understood.

    You are *not* needing plugin (.jar) development, are you? Just PHP/MySQL, correct?

    If so, in PHP/MySQL, all you are trying to do is find a way to format data in the most optimized way possible, and then pull it back out again cleanly and fast, correct?

    Sounds pretty easy with the usage of a few queries and caching. But I am not sure if you'd be able to find someone to do this for you, for free. Please let me know if I am mistaken.
  3. Vengea


    Yes, all I need is PHP/MYSQL help. Is that I have already saved a players username in the General table and when I am going to save the latest matches, the players has to be indentified by their UUIDs. How can I get their usernames using the general table, thats my difficulty.
  4. Well usernames are always changing. You could do a few things, such as
    SELECT Name FROM General WHERE UUID = '$uuid' LIMIT 1;

    Which would give you their username. Then compare it to their current username. If it is different, you'll need to update it.
    UPDATE General SET Name = '$name' WHERE UUID = '$uuid' LIMIT 1;

    I am still not sure I understand what you are getting at, exactly.
  5. Vengea


    Our developer has made it so, when a player logs into the server, their username is updated in the General table :)

    To indentify which player that the website should show is showed with his username, as you can see in the website link.
    I just need to get both players username from a match, at the same time indentify which match belong to that player which is getting his profile shown.
  6. You are wanting to query for two usernames at the same time? I am guessing the Player1 and Player2 slots from the Matches table? Just trying to understand. No offence at all, but it feels like your English is a little broken.

    What is the need to query for &player1=&player2= at the same time?

    EDIT: ohhhhh, I think I understand now.
    You want to get the Player Usernames from the UUIDs in the Matches table?

    Simply use the query:
    SELECT UUID, Name FROM General WHERE UUID = '$uuid1' OR UUID = '$uuid2' LIMIT 2;
    It would return an object with $row['Name'] = array ( name1, name2 ); If I am not mistaken. But this way will put them in a random order, so you won't know which one is from which without re-comparing them for their UUID.

    Or you could use Mojang API to convert UUIDs to Usernames, and avoid querying your own database all together.

    This would only get their usernames. If you are wanting to grab their whole profiles, You're going to have to make more than one Query.
    $player1 = SELECT * FROM General WHERE UUID = '$uuid1';
    $player2 = SELECT * FROM General WHERE UUID = '$uuid2';

    ect. Three queries, if you are grabbing the UUIDs from the table Matches.

    Ultimately you could look up the SQL "INNER JOIN" on google, join the databases General and Matches, then query that way. You might save a single query call using that method.

    Sorry if I am the one being confusing now, I'm still not completely sure I understand what you are aiming for. Currently, my understandings are that you wanted to display the results of 'Matches' by username.
  7. Vengea


    Thank you so much for the help. Now I got some methods to try out. I dont think my english is that broken, its just that I suck at explaining things, even in my main language :)
  8. You are welcome :) Yeah, explaining things can be quite difficult. :)