Updating all of the table order by a value and numbering it | MySQL

Discussion in 'Programming' started by YunusGG, Jun 27, 2018.

  1. Hey, I've got a table on MySQL server and I want to update it order by "Kill" value and numbering it like 1,2,3,4,5...
    I mean
    Before update:
    Before.png
    After update:
    After.png
    Help pls. Thanks.
     
  2. You shouldn't need to change anything except for the SELECT statement that you use to fetch the data. Use the ORDER BY keyword along with your SELECT.
     
  3. I did this "SELECT * FROM Players ORDER BY Kills DESC"
    But I want to get player's ranking according to player's kills. How can I do this?
     
  4. Well if you executed that query all you have to do is iterate over the result set and increase by 1 each time.
    I advise that you store players uuid rather than their name because names can change where as their UUID cannot.
    use "Player.getUniqueId()"
    Code (Java):
    int ranking = 1;
    Map<UUID, Integer> rankings = new HashMap<>();
    while (ResultSet.next()) {
       rankings.put(UUID.fromString(ResultSet.getString("UUID")),  ranking);
        index++;
    }
     
  5. I get this error:
    Code (Text):
    [15:03:40] [Server thread/WARN]: [FFA] Task #18 for FFA v1.0 generated an exception
    java.lang.IllegalArgumentException: UUID string too large
        at java.base/java.util.UUID.fromString(Unknown Source) ~[?:?]
        at mainpackage.Main$2.run(Main.java:104) ~[?:?]
        at org.bukkit.craftbukkit.v1_8_R3.scheduler.CraftTask.run(CraftTask.java:71) ~[FFA.jar:git-Spigot-db6de12-18fbb24]
        at org.bukkit.craftbukkit.v1_8_R3.scheduler.CraftScheduler.mainThreadHeartbeat(CraftScheduler.java:350) [FFA.jar:git-Spigot-db6de12-18fbb24]
        at net.minecraft.server.v1_8_R3.MinecraftServer.B(MinecraftServer.java:723) [FFA.jar:git-Spigot-db6de12-18fbb24]
        at net.minecraft.server.v1_8_R3.DedicatedServer.B(DedicatedServer.java:374) [FFA.jar:git-Spigot-db6de12-18fbb24]
        at net.minecraft.server.v1_8_R3.MinecraftServer.A(MinecraftServer.java:654) [FFA.jar:git-Spigot-db6de12-18fbb24]
        at net.minecraft.server.v1_8_R3.MinecraftServer.run(MinecraftServer.java:557) [FFA.jar:git-Spigot-db6de12-18fbb24]
        at java.base/java.lang.Thread.run(Unknown Source) [?:?]
     
     
  6. Inkzzz

    Resource Staff

    The string you're passing to UUID.fromString() is longer than the character length it accepts. I believe it's 36 characters. When you create the table, make sure you ensure field UUID is a varchar or nvarchar of 36 size.
     
  7. Yes I can backup that the max UUID size is infact 36, make sure that you have stored the UUID in the table with the correct heading first.
    If you try to use my code with the table you showed before it wont work because there is no coloumn for "UUID"
    If you want more info about the Exception, provide us with the code that caused it and we can give more info
     
  8. Here my full code:
    Code (Java):
        public void derle() {
            new BukkitRunnable() {
                @Override
                public void run() {
                    try {
                        Statement st = getConnection().createStatement();
                        ResultSet rs = st.executeQuery("SELECT * FROM " + table + " ORDER BY Olme DESC");
                        int i=1;
                        Map<UUID, Integer> ranking = new HashMap<>();
                        while (rs.next()) {
                            ranking.put(UUID.fromString(rs.getString("UUID")),  i);
                            i++;
                        }
                        rs.close();
                    } catch (SQLException e) {
                        e.printStackTrace();
                    }
                }
            }.runTaskTimer(this, 20, 120);
        }
    I run this runnable in onEnable method.
    And my table:
    Ekran Alıntısı.PNG
    I will save all players to this table and I just want to update "Siralama" column according to all player's "Olme" amount every 6 secs.
    "Siralama" of
    "Oyuncu" -> "YasYasYas" will be 1
    "Oyuncu" -> "YusufBICAK" will be 2
    "Oyuncu" -> "YunusEmreGT" will be 3.
    I mean.
     
  9. I'm going to ask, do you access this mysql table from anywhere else other than in the single plugin?
    for example maybe another plugin on another server in the bungee cord network, or maybe accessed by a website so players can view data?
    If not then you really don't need this in a mysql data, You should use a flat file database and use an SQLite connection to it. This is because locally stored flatfile databases are less likely to be successfully attacked by hackers than a mySQL database
    Also you should try to avoid storing any data in the database unless you definitely need it because it will affect the performance.
    If you only want to access the players ranking (Oyuncu) from the database then you can do so with the code you have just provided and you dont need to actually store their ranking (Oyuncu) in the database, this will save storage and will improve performance
    also you don't need to use "*" You should only use the columns you will need, for example: "SELECT UUID, Olme FROM table ORDER BY Olme DESC". Then it will only return the columns that you will need.
    Hope this helps.
     
  10. Yea, I'm working on a HUB project and I'll use this database after. I couldn't find solution of my problem. I'll work on it. Thanks for helpful responses.
     
  11. Strahan

    Benefactor

    What? No. MySQL is no more of a security risk than any other part of the server infrastructure, assuming it was setup by someone other than the village idiot. Performance is also far superior, especially when the data grows. I also have yet to find a flat file with indexing and ACID support, lol
     
    • Agree Agree x 2
  12. Well for databases in general it's best not to store unnecessary data but actually now thinking about it more I see why MySQL would be better.
    Edit: I think someone convinced me that SQLite was better before and it stuck in my head.
    Edit2: Someone told me that it's slower to use MySQL because you need to use a network query.
     
  13. I want to know who is that genius person.