SQL UPDATE event not working correctly with discord.js via message event

I’ve been trying over the past days to get an SQL UPDATE event to work correctly with discord.js using the mysql npm package. All the login details and table, column names are correct. This is meant to update the integer every time a message is sent in the Discord text channel.

I have tried multiple types of the UPDATE even none of which have worked out.

See these logging screenshots: – https://faxes.zone/img/y4fx1.png (Before / after the event was triggered) – https://faxes.zone/img/8610v.png (discord.js console when a message is sent)

bot.on("message", message => {
    if ( message.channel.type === 'dm' ) return;
    if (message.author.bot) return;
    if (message.content.startsWith(".")) return;

    connection.query(`SELECT * FROM logs WHERE id = '${message.author.id}'`, function (err, resultLogs) {
        if(err) throw err;
        let sql;

        if(resultLogs[0]) {
            console.log(resultLogs[0].msgcount)
            let newMessageCount = resultLogs[0].msgcount++;
            sql = `UPDATE logs SET msgcount = ${newMessageCount} WHERE id=${message.author.id}`
            connection.query(sql, console.log)
        }
    });  
});

The expected output should be the integer increasing by one every time a message is sent.

Answer

You can increment the msgcount with a MySql operator. So you don’t need to make a select query. And also you need to escape the value in the sql statement to prevent injections.

Btw, I refactored your code.

bot.on('message', message => {
    if ( message.channel.type === 'dm' || message.author.bot || message.content.startsWith(".") ) return;

    connection.query('UPDATE logs SET msgcount = msgcount + 1 WHERE id = ?', [message.author.id], (error,  result) => {
      if (error) throw error;
      
      console.log(result);
    })
}