[SOLVED] Update row if 1 day has passed using MySQL

Discussion in 'Programming' 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()+"';
    Error:
    Code (Text):
    [15:34:32] [Server thread/ERROR]: Could not pass event PlayerInteractEvent to PLGlobal v1.0
    org.bukkit.event.EventException
        at org.bukkit.plugin.java.JavaPluginLoader$1.execute(JavaPluginLoader.java:310) ~[spigot.jar:git-Spigot-4af49dc-c5e9a16]
        at org.bukkit.plugin.RegisteredListener.callEvent(RegisteredListener.java:62) ~[spigot.jar:git-Spigot-4af49dc-c5e9a16]
        at org.bukkit.plugin.SimplePluginManager.fireEvent(SimplePluginManager.java:502) [spigot.jar:git-Spigot-4af49dc-c5e9a16]
        at org.bukkit.plugin.SimplePluginManager.callEvent(SimplePluginManager.java:487) [spigot.jar:git-Spigot-4af49dc-c5e9a16]
        at org.bukkit.craftbukkit.v1_9_R2.event.CraftEventFactory.callPlayerInteractEvent(CraftEventFactory.java:231) [spigot.jar:git-Spigot-4af49dc-c5e9a16]
        at net.minecraft.server.v1_9_R2.PlayerInteractManager.a(PlayerInteractManager.java:483) [spigot.jar:git-Spigot-4af49dc-c5e9a16]
        at net.minecraft.server.v1_9_R2.PlayerConnection.a(PlayerConnection.java:887) [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$1.run(SourceFile:13) [spigot.jar:git-Spigot-4af49dc-c5e9a16]
        at java.util.concurrent.Executors$RunnableAdapter.call(Unknown Source) [?:1.8.0_91]
        at java.util.concurrent.FutureTask.run(Unknown 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(MinecraftServer.java:726) [spigot.jar:git-Spigot-4af49dc-c5e9a16]
        at net.minecraft.server.v1_9_R2.DedicatedServer.D(DedicatedServer.java:399) [spigot.jar:git-Spigot-4af49dc-c5e9a16]
        at net.minecraft.server.v1_9_R2.MinecraftServer.C(MinecraftServer.java:665) [spigot.jar:git-Spigot-4af49dc-c5e9a16]
        at net.minecraft.server.v1_9_R2.MinecraftServer.run(MinecraftServer.java:564) [spigot.jar:git-Spigot-4af49dc-c5e9a16]
        at java.lang.Thread.run(Unknown 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(Util.java:404) ~[spigot.jar:git-Spigot-4af49dc-c5e9a16]
        at com.mysql.jdbc.Util.getInstance(Util.java:387) ~[spigot.jar:git-Spigot-4af49dc-c5e9a16]
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:941) ~[spigot.jar:git-Spigot-4af49dc-c5e9a16]
        at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3870) ~[spigot.jar:git-Spigot-4af49dc-c5e9a16]
        at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3806) ~[spigot.jar:git-Spigot-4af49dc-c5e9a16]
        at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2470) ~[spigot.jar:git-Spigot-4af49dc-c5e9a16]
        at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2617) ~[spigot.jar:git-Spigot-4af49dc-c5e9a16]
        at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2546) ~[spigot.jar:git-Spigot-4af49dc-c5e9a16]
        at com.mysql.jdbc.StatementImpl.executeUpdateInternal(StatementImpl.java:1541) ~[spigot.jar:git-Spigot-4af49dc-c5e9a16]
        at com.mysql.jdbc.StatementImpl.executeLargeUpdate(StatementImpl.java:2605) ~[spigot.jar:git-Spigot-4af49dc-c5e9a16]
        at com.mysql.jdbc.StatementImpl.executeUpdate(StatementImpl.java:1469) ~[spigot.jar:git-Spigot-4af49dc-c5e9a16]
        at mcgglobal.DailyReward.updateDr(DailyReward.java:55) ~[?:?]
        at mcgglobal.DailyReward.openDr(DailyReward.java:236) ~[?:?]
        at mcgglobal.Listeners.onPlayerInteract(Listeners.java:188) ~[?:?]
        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 org.bukkit.plugin.java.JavaPluginLoader$1.execute(JavaPluginLoader.java:306) ~[spigot.jar:git-Spigot-4af49dc-c5e9a16]
        ... 17 more
    NOTE: I use "datetime" as datatype for the date row.
     
  2. You can compare the dates in you java plugin. If the day has passed (you can compare the day number) run the mysql query on your mysql server.
    Compare: In your java plugin
    Query: In your mysql server​
    Don't run the query with the comparator, just compare in your java plugin and then update the row.
     
  3. Thanks for your help, but I already got this to work. I totally forgot about this thread. But thanks anyways!