Using mySQL in order to create a daily command?

Question

still getting into the shift of things with this. So i'm currently using a system to create a discord.js command which can be ran by a player and then will go onto a 24 hour cooldown, however the issue with the system below is I have no real way to store when there cooldown expires. Currently it is using an array of IDs however a user could simply time there command with the refresh of the cache and then claim it twice in quick succession.

So my question is

Is there a way to handle a per user cooldown via mySQL, or should another method be looked into. Any answer is appreciated!

const { SlashCommandBuilder } = require("@discordjs/builders");
const connection = require("../db");

let claimedCache = []

const clearCache = () => {
    claimedCache = []
    setTimeout(clearCache, 1000 * 60 * 60 * 24)
}
clearCache()

module.exports = {
    data: new SlashCommandBuilder()
    .setName('daily')
    .setDescription('Claim your daily points'),

    
    async execute(interaction) {
        if (claimedCache.includes(interaction.user.id)) {
            interaction.reply({
                content: "You have already claimed your daily reward!",
                ephemeral: true
            })
            return
        }
        connection.query(`SELECT * FROM characters WHERE char_owner = '${interaction.user.id}'`, function(err, rows) {
            if (err || !rows.length) {
                interaction.reply({
                    content: "You do not have a character!",
                    epehemeral: true
                })
                return
            }
            const currentPoints = rows[0].char_points
            
            if (interaction.member.roles.cache.has('912680123113832448')) {
                connection.query(`UPDATE characters SET char_points = '${currentPoints + 4}' WHERE char_owner = '${interaction.user.id}'`)
                interaction.reply({
                    content: "You have claimed your daily reward!",
                    ephemeral: true
                })
                claimedCache.push(interaction.user.id)
                return
            } else if (interaction.member.roles.cache.has('912680385685635092')) {
                connection.query(`UPDATE characters SET char_points = '${currentPoints + 8}' WHERE char_owner = '${interaction.user.id}'`)
                interaction.reply({
                    content: "You have claimed your daily reward!",
                    ephemeral: true
                })
                claimedCache.push(interaction.user.id)
                return
            } else if (interaction.member.roles.cache.has('912680439095889971')) {
                connection.query(`UPDATE characters SET char_points = '${currentPoints + 12}' WHERE char_owner = '${interaction.user.id}'`)
                interaction.reply({
                    content: "You have claimed your daily reward!",
                    ephemeral: true
                })
                claimedCache.push(interaction.user.id)
                return
            } else if (interaction.member.roles.cache.has('912680476790120448')) {
                connection.query(`UPDATE characters SET char_points = '${currentPoints + 16}' WHERE char_owner = '${interaction.user.id}'`)
                interaction.reply({
                    content: "You have claimed your daily reward!",
                    ephemeral: true
                })
                claimedCache.push(interaction.user.id)
                return
            } else if (interaction.member.roles.cache.has('912680542091243570')) {
                connection.query(`UPDATE characters SET char_points = '${currentPoints + 16}' WHERE char_owner = '${interaction.user.id}'`)
                interaction.reply({
                    content: "You have claimed your daily reward!",
                    ephemeral: true
                })
                claimedCache.push(interaction.user.id)
                return
            }

            //connection.query(`UPDATE characters SET char_points = '${currentPoints + 4}' WHERE char_owner = '${interaction.user.id}'`)
        })
    }
}```

user_14757658 · Answer

Yes there is a way but this below is set up as once per day starting at midnight - tweak if you'd like

Create a table with 2 columns

CREATE TABLE trackers (discordID varchar(60), claimed varchar(5));

Then have your command update that table when the command is used:

connection.query(`SELECT * FROM trackers WHERE discordID = '${interaction.user.id}'`, (err, rows) => {
    if (err) throw err

    const claimStatus = rows[0].claimed

    if (claimStatus === 'Yes') {
// insert response for already claimed
    return
    } else {
        if (!rows.length) {
// not in table yet cause its the first time they have used the command
            connection.query(`INSERT INTO trackers (discordID, claimed) VALUES ('${interaction.user.id}', 'Yes')`)
// insert response for claiming
            return
        } else {
            connection.query(`UPDATE trackers SET claimed = 'Yes' WHERE discordID = '${interaction.user.id}'`)
// insert response for claiming
            return
        }
    }
})

Then somewhere in your main bot script put this to run everyday at midnight (will need to add node-cron to the top of the file)

const cron = require('node-cron')

Then schedule it like this

cron.schedule(0 0 * * *, function () {
// resets daily
connection.query(`UPDATE trackers SET claimed = 'No'`)
})

Using mySQL in order to create a daily command?

1 Answers