SQL Leaderboard issue nodejs discord.js

Question

I have created a simple discord bot complete with message listeners ect which adds data into a MySQL DB on certain events.

My code for add +1 point to score on message into the channel (fully working):

//If message is sent add points to scores table

    con.query(`SELECT * FROM scores WHERE user="${message.author.id}" AND guild= "${message.guild.id}"`, (err, rows) => {
        if (err) throw err;

        let sql;
        if (rows.length < 1) {
            sql = `INSERT INTO scores (user, guild, points) VALUES ('${message.author.id}', '${message.guild.id}', '1')`, (err, rows) => {
                if (err) throw err;
            };

        } else {
            let score = rows[0].scores;
            sql = `UPDATE scores SET points = points +1 WHERE user="${message.author.id}" AND guild= "${message.guild.id}"`;
        };

        con.query(sql);

    });

Once the leader board command is entered into the channel; I'd like to take the data that's in the scores table, assort it into top 10 and print it in discord (by using message.channel.send or message.channel.send(embed).

Below code is what I have so far:

const Discord = require("discord.js");

module.exports.run = async (bot, message, args, con) => {
    
const top10 = `SELECT user, points, lstmsg FROM scores WHERE guild = '${message.guild.id}' ORDER BY cast(points as SIGNED) ASC LIMIT 10`;
//user, points, lstmsg from scores WHERE guild= '${message.guild.id}' ORDER BY points DESC LIMIT 10`;

con.query(top10, function(err, rows, fields) {
  if (err) throw err;

  //    const embed = new Discord.RichEmbed()
  //   .setTitle("Leaderboard")
  //   .setAuthor(bot.user.username,)
  //   .setDescription("Inactive..... Boooooo!")
  //   .addField(rows[1])
  //   .setColor(0x00AE86)


return message.channel.send(rows);

});


}
module.exports.help = {
    name: "top10",
    usage: "``prefix`` top10",
    description: "top 10 points",
}

But it sends the following in discord:

[object Object]
[object Object]
[object Object]
[object Object]
[object Object]
[object Object]
[object Object]
[object Object]
[object Object]
[object Object]

Any ideas? Thanks in advance

Using JSON.Stringify returns results like this:

[{"user":"123456789876587659","points":"2","lstmsg":"2019-03-04T22:40:40.000Z"},{"user":"378976806730203147","points":"80","lstmsg":"2019-03-05T10:10:18.000Z"},{"user":"123456789101112131","points":"93","lstmsg":"2019-03-04T22:40:29.000Z"},{"user":"378976806730203147","points":"168","lstmsg":"2019-03-05T10:10:18.000Z"},{"user":"456567676767867677","points":"289","lstmsg":"2019-03-04T22:41:01.000Z"}]

I would like the data to appear as:

User:
Points:
Last Message:

For each entry in the DB (Limited to 10).

I have also tried the following from resource: SQLite-Based Points system

Code:

const Discord = require("discord.js");

module.exports.run = async (bot, message, args, con) => {
  {
    var top10query = "SELECT * FROM scores WHERE guild = ? ORDER BY points DESC LIMIT 10"
    const top10 = con.query(top10query)

    // Now shake it and show it! (as a nice embed, too!)
    const embed = new Discord.RichEmbed()
      .setTitle("Leaderboard")
      .setAuthor(bot.user.username, bot.user.avatarURL)
      .setDescription("Our top 10 points leaders!")
      .setColor(0x00AE86);

    for (const data of top10) {
      embed.addField(bot.users.get(data.user).tag, `${data.points} points (last message ${data.lstmsg})`);
    }
    return message.channel.send({ embed });
  }





}
module.exports.help = {
  name: "inactive",
  usage: "``prefix`` inactive",
  description: "Bottom 30 inactive & last message date for current weeks",
}

but I get an error (Most likely because the example was for SQLlite and adapted for MySQL):

(node:2880) UnhandledPromiseRejectionWarning: TypeError: top10 is not iterable

score 0 · Answer 1 · answered Mar 05 '19 at 00:34

0

I'd suggest, if not already, that user, guild are a composite primary key (or unique key) of the scores table. This can be done in SQL with:

ALTER TABLE scores DROP PRIMARY KEY, ADD PRIMARY KEY (user, guide)

For your SQL statements INSERT ON DUPLICATE KEY UPDATE is a common query for this. It relies on the unique or primary key above.

INSERT INTO scores (user, guild, points) VALUES ('${message.author.id}', '${message.guild.id}', '1')
ON DUPLICATE KEY UPDATE points=points+1

Also see Preventing SQL injection in Node.js

answered Mar 05 '19 at 00:34

danblack

12,130
2
22
41

acknowledge its not the question you asked, however the code was a bit messy and both pieces has SQL injection that need fixing (no need to trust discord bot). – danblack Mar 05 '19 at 00:35
Thanks for the advice! Its just a basic bot to track inactive users in a guild really so don't really care about SQL injection for this particular project. I'll probably update it later to prevent this but not right now. – QueenInTheNorth Mar 05 '19 at 09:56

score 0 · Answer 2 · answered Mar 05 '19 at 07:47

0

Try using JSON.stringify();, the docs are here for this.

var o = {
  a: 2, 
  b: 3
}
console.log(JSON.stringify(o));  //"{"a":2,"b":3}"

answered Mar 05 '19 at 07:47

Pentium1080Ti

1,040
1
7
16

JSON.stringify is just what I needed!! Thanks! On the right track.. How would I split up the results to display: User: Points: Last message: for each entry? The results are displaying as: [{"user":"123456789876587659","points":"2","lstmsg":"2019-03-04T22:40:40.000Z"},{"user":"378976806730203147","points":"77","lstmsg":"2019-03-04T23:47:47.000Z"},{"user":"123456789101112131","points":"93","lstmsg":"2019-03-04T22:40:29.000Z"},{"user":"456567676767867677","points":"289","lstmsg":"2019-03-04T22:41:01.000Z"}] at the moment – QueenInTheNorth Mar 05 '19 at 10:07
As this answer worked for you, I suggest you click the little hollow check mark at the side to mark it as correct, this way it will make it easier for people to find an answer if they have the same question as you. If you use `rows[x].key` then it will get you that value, for example `console.log(rows[0].points); //2` would return 2, another example would be `rows[1].points` and this would return 77, and so on. – Pentium1080Ti Mar 05 '19 at 16:22

score 0 · Accepted Answer · answered Mar 15 '19 at 13:25

I used map to map out the results.

const Discord = require("discord.js");

module.exports.run = async (bot, message, args, con) => {

  const top10query = `SELECT user, points, lstmsg FROM scores WHERE guild = '${message.guild.id}' ORDER BY cast(points as SIGNED) DESC LIMIT 10`

  const query = querytxt => {
    return new Promise((resolve, reject) => {
      con.query(querytxt, (err, results, fields) => {
        if (err) reject(err);
        resolve([results, fields]);
      });
    });
  };
  const [results, fields] = await query(top10query);

  const map1 = results.map(results => ` ** User:** ${bot.users.get(results.user).username} \n **Messages:** ${results.points} \n **Last message:** ${results.lstmsg} \n`);
  message.channel.send(map1)
}
module.exports.help = {
  name: "top10",
  usage: "``prefix`` top10",
  description: "top 10 points",
}

SQL Leaderboard issue nodejs discord.js

3 Answers3