Solved MySQL - Check if one day has passed

Discussion in 'Spigot Plugin Development' started by mlgcraftnetwork, May 30, 2016.

  1. I'm creating a plugin and there will be a dailyreward system in it, I have a mysql table with 3 rows:
    - player (player UUID)
    - use (true/false)
    - date (date the player last opened the dailyreward)

    I'm trying to update the use row to true if 1 day has passed and I tried a lot of ways but I can't get it to work, could someone with more MySQL experience help me? Thanks a lot!

    Current MySQL code:
    Code (Text):
    UDPATE `dailyreward` SET `use`='true' WHERE DATE_SUB(`date`, NOW())<=0 AND `player`='"+p.getUniqueId()+"';
    Code (Text):
    [15:34:32] [Server thread/ERROR]: Could not pass event PlayerInteractEvent to PLGlobal v1.0
        at$1.execute( ~[spigot.jar:git-Spigot-4af49dc-c5e9a16]
        at org.bukkit.plugin.RegisteredListener.callEvent( ~[spigot.jar:git-Spigot-4af49dc-c5e9a16]
        at org.bukkit.plugin.SimplePluginManager.fireEvent( [spigot.jar:git-Spigot-4af49dc-c5e9a16]
        at org.bukkit.plugin.SimplePluginManager.callEvent( [spigot.jar:git-Spigot-4af49dc-c5e9a16]
        at org.bukkit.craftbukkit.v1_9_R2.event.CraftEventFactory.callPlayerInteractEvent( [spigot.jar:git-Spigot-4af49dc-c5e9a16]
        at net.minecraft.server.v1_9_R2.PlayerInteractManager.a( [spigot.jar:git-Spigot-4af49dc-c5e9a16]
        at net.minecraft.server.v1_9_R2.PlayerConnection.a( [spigot.jar:git-Spigot-4af49dc-c5e9a16]
        at net.minecraft.server.v1_9_R2.PacketPlayInUseItem.a(SourceFile:55) [spigot.jar:git-Spigot-4af49dc-c5e9a16]
        at net.minecraft.server.v1_9_R2.PacketPlayInUseItem.a(SourceFile:11) [spigot.jar:git-Spigot-4af49dc-c5e9a16]
        at net.minecraft.server.v1_9_R2.PlayerConnectionUtils$ [spigot.jar:git-Spigot-4af49dc-c5e9a16]
        at java.util.concurrent.Executors$ Source) [?:1.8.0_91]
        at Source) [?:1.8.0_91]
        at net.minecraft.server.v1_9_R2.SystemUtils.a(SourceFile:45) [spigot.jar:git-Spigot-4af49dc-c5e9a16]
        at net.minecraft.server.v1_9_R2.MinecraftServer.D( [spigot.jar:git-Spigot-4af49dc-c5e9a16]
        at net.minecraft.server.v1_9_R2.DedicatedServer.D( [spigot.jar:git-Spigot-4af49dc-c5e9a16]
        at net.minecraft.server.v1_9_R2.MinecraftServer.C( [spigot.jar:git-Spigot-4af49dc-c5e9a16]
        at [spigot.jar:git-Spigot-4af49dc-c5e9a16]
        at Source) [?:1.8.0_91]
    Caused by: 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 'UDPATE `dailyreward` SET `use`='true' WHERE DATE_SUB(`date`, NOW())<=0 AND `play' at line 1
        at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) ~[?:1.8.0_91]
        at sun.reflect.NativeConstructorAccessorImpl.newInstance(Unknown Source) ~[?:1.8.0_91]
        at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Source) ~[?:1.8.0_91]
        at java.lang.reflect.Constructor.newInstance(Unknown Source) ~[?:1.8.0_91]
        at com.mysql.jdbc.Util.handleNewInstance( ~[spigot.jar:git-Spigot-4af49dc-c5e9a16]
        at com.mysql.jdbc.Util.getInstance( ~[spigot.jar:git-Spigot-4af49dc-c5e9a16]
        at com.mysql.jdbc.SQLError.createSQLException( ~[spigot.jar:git-Spigot-4af49dc-c5e9a16]
        at com.mysql.jdbc.MysqlIO.checkErrorPacket( ~[spigot.jar:git-Spigot-4af49dc-c5e9a16]
        at com.mysql.jdbc.MysqlIO.checkErrorPacket( ~[spigot.jar:git-Spigot-4af49dc-c5e9a16]
        at com.mysql.jdbc.MysqlIO.sendCommand( ~[spigot.jar:git-Spigot-4af49dc-c5e9a16]
        at com.mysql.jdbc.MysqlIO.sqlQueryDirect( ~[spigot.jar:git-Spigot-4af49dc-c5e9a16]
        at com.mysql.jdbc.ConnectionImpl.execSQL( ~[spigot.jar:git-Spigot-4af49dc-c5e9a16]
        at com.mysql.jdbc.StatementImpl.executeUpdateInternal( ~[spigot.jar:git-Spigot-4af49dc-c5e9a16]
        at com.mysql.jdbc.StatementImpl.executeLargeUpdate( ~[spigot.jar:git-Spigot-4af49dc-c5e9a16]
        at com.mysql.jdbc.StatementImpl.executeUpdate( ~[spigot.jar:git-Spigot-4af49dc-c5e9a16]
        at mcgglobal.DailyReward.updateDr( ~[?:?]
        at mcgglobal.DailyReward.openDr( ~[?:?]
        at mcgglobal.Listeners.onPlayerInteract( ~[?:?]
        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[?:1.8.0_91]
        at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source) ~[?:1.8.0_91]
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source) ~[?:1.8.0_91]
        at java.lang.reflect.Method.invoke(Unknown Source) ~[?:1.8.0_91]
        at$1.execute( ~[spigot.jar:git-Spigot-4af49dc-c5e9a16]
        ... 17 more
    NOTE: I use "datetime" as datatype for the date row.
    • Agree Agree x 1
  3. I'm SO DUMB, Thank you so much XD
  4. 1. Use PreparedStatements instead of string concatenation as it reduces chances of sql injection and looks better.
    2. As megamichiel said, you have a typo in UPDATE.
    • Agree Agree x 1
  5. Code (SQL):
    UPDATE `dailyreward` SET `use`='true' WHERE DATE_SUB(NOW(), INTERVAL 1 DAY) <= `date` AND `player`=?;
    DATE_SUB means "date subtraction", you're subtracting the current time from the stored time and checking if it's less than 0, that means anything older than the future applies (aka, it's pointless).

    Additionally, you should use Prepared Statements and not just insert things like UUID directly into the query string.

    Also, use BOOLEAN or 0/1 values, not "true" or "false" strings.
    • Agree Agree x 1
  6. First off thanks for the useful tips, I'm still new to mysql so I'm not the best at it. Your solution fixed my problem but it sets the value to true no matter what. The date is updated to the current date but every time I open the reward gui I can claim it. Did I make any mistakes?
    Code (Text):
    Main.c.createStatement().executeUpdate("UPDATE `dailyreward` SET `use`='true' WHERE DATE_SUB(NOW(), INTERVAL 1 DAY) <= `date` AND `player`='"+p.getUniqueId()+"';");
    Learning MySQL is going pretty well and I agree that I should use prepared statements so I will take a look at that, but I would really like to get this code to work, do you have any idea how to make this work? Thanks so much!

    (Btw how do prepared statements work and why are they better?)
  7. PreparedStatements essentially make a spot in your sql statement a "Variable", it's a placeholder for whatever you insert.

    If my UUID was: "';DROP ALL TABLES;--", then your statement would be:

    Code (Java):
    UPDATE `dailyreward` SET `use`='true' WHERE DATE_SUB(NOW(), INTERVAL 1 DAY) <= `date` AND `player`='';DROP ALL TABLES;--';
    Which would wipe your database. By using a preparedstatement, it would literally insert "';DROP ALL TABLES;--" into the table directly.

    As for your statement, you set it to true, but do you ever check if it's true/false in your menu? You should probably cache these kinds of things anyhow, sql isn't meant for live data, it's meant to store data you no longer immediately need.
  8. Well I created a cache system for most of my mysql interaction but not for this one, I could add that but I need to check if the player can claim the dailyreward anyways so I though it would work if I made it update every time the player opens the gui. Yes, I check if its true/false everytime the player opens the gui, here is my checking code:
    Code (Text):
    But for some reason everytime I run the mysql date check code it sets the players `use` value to true, even if I opened it 1 minute ago.
  9. Because that's an UPDATE statement, not a SELECT. Which means you're always calling something to set it to "true".
    • Winner Winner x 1
  10. Aha, so I should first check if the player can use it again using select and then set the value to true if he can. Will try that, thanks!
  11. I think I got it to work, it looks like to code worked all the time but I had to change "<=" to ">=" xD. Thanks!
  12. Well all that does is update anything older than 1 day, instead of newer than. So you're probably just excluding your expected results from being modified at all, not solving the problem. Your first idea:

    Was more correct. At least for a hypothetical design (In reality, you'd store a map/boolean etc of that information, and store it in sql when they quit).