Get highest INT in column (SQL)

Discussion in 'Plugin Development' started by DarkRangerMC, Jan 25, 2015.

Thread Status:
Not open for further replies.
  1. I got a system where every player gets an ID by joining the server.
    So first player ID = 1
    second = 2
    third = 3
    fourth ....

    So I want my plugin to check the highest ID in the "ID" column, and then give the next player ID + 1.
     
  2. Konato_K likes this.
  3. Offline

    SuperOriginal

    Have an Int equal to 0, loop through all ids if the id is greater than the int, set the int equal to that id. After the loop the int will be the highest id

    Edit: Oh yes, forgot the SQL increment system
     
  4. Not very experienced with SQL, explain "MySQL's increment" please.
     
  5. So how would I implement it?

    Code:
    openConnection();
    PreparedStatement statement = connection.prepareStatement("INSERT INTO test values(?,?,?,?,?);");
    statement.setString(1, args[0]);
    statement.setString(2, player.getName());
    statement.setString(3, test);
    statement.setInt(4, newID);
    Edit:
    I want newID to be the highest ID + 1
     
    Last edited: Jan 25, 2015
  6. Offline

    hexaan

    Code:
    -- Make sure you have the ID set to primary key of your table.
    ALTER TABLE test MODIFY COLUMN `ID column of your table` INT NOT NULL AUTO_INCREMENT
    
    Now every time you insert a new person in your test table you do not have to specify the ID. It gets generated automatically.
     
  7. Thanks, but how do I implement it in there ^^
     
  8. Offline

    hexaan

  9. Code:
    statement.executeUpdate("CREATE TABLE IF NOT EXISTS `corereport` (`Player` varchar(17),`Reporter` varchar(17),`Reason` text,`ID` int,`Status` text)"); 
     
  10. Offline

    hexaan

    Code:
    openConnection();
    PreparedStatement statement = connection.prepareStatement("insert into corereport(`Player`,`Reporter`,`Reason`,`Status`) values(?,?,?,?);");
    statement.setString(1, "The player name");
    statement.setString(2, "The reporter name");
    statement.setString(3, "The reason");
    statement.setString(4, "The status");
    This would work if the ID is auto_incremented. If you only use the values instead of saying which Columns get those values it does not know that you want to generate the auto_incremented id.

    I would also edit the table to know that its auto_increment and it has a primary key.
    Code:
    statement.executeUpdate("CREATE TABLE IF NOT EXISTS `corereport` (`Player` varchar(17),`Reporter` varchar(17),`Reason` text,`ID` int not null auto_increment,`Status` text, primary key(`ID`))");
     
  11. @hexaan works for me :)
    How would I send an ingame-message with that ID?
    Since I implemented your code, this is not working anymore:

    Code:
    openConnection();
    PreparedStatement ps = connection.prepareStatement("UPDATE corereport SET Status = ? WHERE ID = ?;");
    ps.setString(1, "Reviewed");
    ps.setString(2, args[2]);
    ps.executeUpdate();
    ps.close();
    args[2] = ID

    I've fixed most of it.
    Still don't know, how to send a message in chat with the new ID.

    EDIT by Moderator: merged posts, please use the edit button instead of double posting.
     
    Last edited by a moderator: Jun 13, 2016
  12. Offline

    mythbusterma

    @DarkRangerMC

    ...you can "select id," you do know that right?
     
  13. I also want to get the HIGHEST ID.
     
    Last edited: Jan 26, 2015
  14. Offline

    guitargun

    @DarkRangerMC
    its something like select id from (yourtable) order by desc limit 1;
    this will give you the highest one. just get a result set and return the last result you will get

    edit:
    I have a site link with most used sql statements. I only don't know if I can post it online since it was from a older school I attended
     
  15. Code:
                                            openConnection();
                                            try{
                                                PreparedStatement sql = connection.prepareStatement("SELECT ID FROM corereport ORDER BY DESC LIMIT 1;");
                                                ResultSet rs = sql.executeQuery();
                                                String id;                          
                                                if(rs.next()) {
                                                    id = rs.getString("ID");
                                                    player1.sendMessage(id);
                                                }
                                            }catch (Exception e) {
                                                e.printStackTrace();
                                            } finally {
                                                closeConnection();
                                            }
    This is what I got right now

    ERROR:
    Code:
    [26-01 19:56:51 ] [Server] WARN com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DESC LIMIT 1' at line 1
     
  16. Offline

    guitargun

    @DarkRangerMC I think it should work not sure if your rs.getString should be rs.getInt(); since ID is an int

    EDIT:
    ORDER BY ID DESC that has to work if above doens't work
     
  17. Code:
    [26-01 20:09:41 ] [Server] WARN com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ORDER BY ID DESC 1' at line 1
     
  18. Offline

    guitargun

    @DarkRangerMC I am not sure if you still have the limit with it. you can always check in mysql workbench (and I think phpmyadmin) with your statements. what I also have in one of my statement is this:
    Code:
    String sql = "SELECT * FROM `Users` WHERE `username` LIKE ? ORDER BY `id` DESC LIMIT 1;";
    this uses the ` which I found some what needed for not getting errors (I don't know why)

    yours should be then
    Code:
    String sql = "SELECT `ID` FROM `Users` ORDER BY `ID` DESC LIMIT 1;";
     
  19. Offline

    mythbusterma

    guitargun likes this.
  20. Example?
     
  21. Offline

    mythbusterma

  22. Offline

    mythbusterma

    @DarkRangerMC

    You need to learn SQL if you expect to be able to write queries in it. You don't know SQL if you can't read the manual and write a query from it, ESPECIALLY one as stupid simple as SELECT MAX()

    http://sqlzoo.net/wiki/Main_Page
     
  23. I was just asking for an example... not for advice.
     
  24. Offline

    mythbusterma

    @DarkRangerMC

    Do you know how IMMENSELY frustrating it is when you're sitting here begging for an example when literally the first example on Google is an example, and it's from the MANUAL?


    [​IMG]

    A screenshot, with some helpful arrows because it being the only thing on the page, it's a little hard to find on the page, especially since it's only the first Google result.
     
    TGRHavoc and SuperOriginal like this.
  25. Do you know how frustrating it is when I just want an example how I would implement that in MY code. Because don't understand sh** from that site.
     
  26. Offline

    mythbusterma

    @DarkRangerMC

    Then say you want to be "spoon-feed" because you don't understand SQL. Because that is what you are asking for. You can't even take a very simple example and apply it to your own code because of how little you understand the medium you are working with.

    That is not what these forums are for, and nobody should spoon-feed you. Read the tutorials at the link I posted above until you can understand what you're doing.
     
  27. Offline

    guitargun

    @mythbusterma I did not know about this select max or I just forgot about it thx for it.
     
  28. This forum is not ment for this discussion, just help me or don't reply :S...
     
    Last edited: Jan 26, 2015
Thread Status:
Not open for further replies.

Share This Page