Discord Bot Maker - MySQL Leveling System or Discord.js - MySQL Leveling System

Question

So let me layout the background:

Discord Bot Maker: I have a leveling system that adds xp based on posts and stores that to a JSON file and works without a problem. Once a member reaches a certain xp level, the bot assigns them a new role in discord. If ever I have to restart the bot, all members who have xp would start over back at level 1 with 0 xp.

Discord.js I have an xp bot that adds xp based on posts and stores the information in an online MySQL database. This bot does not auto assign roles when they reach a certain xp level but the information is saved if I ever have need to restart the bot.

I need help (point me in the right direction) getting either DBM bot to store the data in MySQL (I've searched for hours and can't find a tutorial or anything). Here is the DBM Raw data (minus ID's and other information that isn't relevant)

Command level

{
  "name": "level",
  "permissions": "NONE",
  "restriction": "1",
  "actions": [
    {
      "member": "1",
      "varName": "",
      "dataName": "exp",
      "defaultVal": "0",
      "storage": "1",
      "varName2": "memberexp",
      "name": "Store Member Data"
    },
    {
      "member": "1",
      "varName": "",
      "dataName": "level",
      "defaultVal": "1",
      "storage": "1",
      "varName2": "myLevel",
      "name": "Store Member Data"
    },
    {
      "channel": "0",
      "varName": "",
      "message": "```You are level ${tempVars(\"myLevel\")} with ${tempVars(\"memberexp\")}!```",
      "storage": "0",
      "varName2": "",
      "name": "Send Message"
    }
  ],
  "_id": "Tucww",
  "comType": "0"
}

Command LevelSystem

{
  "name": "LevelingSystem",
  "temp": "msg",
  "event-type": "2",
  "actions": [
    {
      "info": "0",
      "find": "channelid goes here",
      "storage": "2",
      "varName": "channel name goes here",
      "name": "Find Channel"
    },
    {
      "storage": "2",
      "varName": "myLevel",
      "comparison": "1",
      "value": "7",
      "iftrue": "2",
      "iftrueVal": "7",
      "iffalse": "0",
      "iffalseVal": "",
      "name": "Check Variable"
    },
    {
      "storage": "2",
      "varName": "myLevel",
      "comparison": "1",
      "value": "13",
      "iftrue": "2",
      "iftrueVal": "10",
      "iffalse": "0",
      "iffalseVal": "",
      "name": "Check Variable"
    },
    {
      "storage": "2",
      "varName": "myLevel",
      "comparison": "1",
      "value": "25",
      "iftrue": "2",
      "iftrueVal": "13",
      "iffalse": "0",
      "iffalseVal": "",
      "name": "Check Variable"
    },
    {
      "storage": "2",
      "varName": "myLevel",
      "comparison": "1",
      "value": "50",
      "iftrue": "2",
      "iftrueVal": "16",
      "iffalse": "0",
      "iffalseVal": "",
      "name": "Check Variable"
    },
    {
      "storage": "2",
      "varName": "myLevel",
      "comparison": "1",
      "value": "100",
      "iftrue": "2",
      "iftrueVal": "19",
      "iffalse": "1",
      "iffalseVal": "",
      "name": "Check Variable"
    },
    {
      "info": "0",
      "find": "roleid number goes here",
      "storage": "2",
      "varName": "role name goes here",
      "name": "Find Role"
    },
    {
      "member": "3",
      "varName2": "member",
      "role": "4",
      "varName": "role name goes here",
      "reason": "",
      "name": "Add Member Role"
    },
    {
      "name": "End Action Sequence"
    },
    {
      "info": "0",
      "find": "roleid number goes here",
      "storage": "2",
      "varName": "role name goes here",
      "name": "Find Role"
    },
    {
      "member": "3",
      "varName2": "member",
      "role": "4",
      "varName": "role name goes here",
      "reason": "",
      "name": "Add Member Role"
    },
    {
      "name": "End Action Sequence"
    },
    {
      "info": "0",
      "find": "roleid number goes here",
      "storage": "2",
      "varName": "role name goes here",
      "name": "Find Role"
    },
    {
      "member": "3",
      "varName2": "member",
      "role": "4",
      "varName": "role name goes here",
      "reason": "",
      "name": "Add Member Role"
    },
    {
      "name": "End Action Sequence"
    },
    {
      "info": "0",
      "find": "roleid number goes here",
      "storage": "2",
      "varName": "role name goes here",
      "name": "Find Role"
    },
    {
      "member": "3",
      "varName2": "member",
      "role": "4",
      "varName": "role name goes here",
      "reason": "",
      "name": "Add Member Role"
    },
    {
      "name": "End Action Sequence"
    },
    {
      "info": "0",
      "find": "roleid number goes here",
      "storage": "2",
      "varName": "role name goes here",
      "name": "Find Role"
    },
    {
      "member": "3",
      "varName2": "member",
      "role": "4",
      "varName": "role name goes here",
      "reason": "",
      "name": "Add Member Role"
    },
    {
      "name": "End Action Sequence"
    }
  ],
  "_id": "gUxAU",
  "restriction": "1",
  "comType": "3",
  "permissions": "NONE"
}

Event LevelSystem

{
  "name": "LevelingSystem",
  "temp": "msg",
  "event-type": "2",
  "actions": [
    {
      "message": "1",
      "varName": "msg",
      "info": "3",
      "storage": "2",
      "varName2": "member",
      "name": "Store Message Info"
    },
    {
      "member": "3",
      "varName": "member",
      "dataName": "exp",
      "changeType": "1",
      "value": "1",
      "name": "Control Member Data"
    },
    {
      "member": "3",
      "varName": "member",
      "dataName": "level",
      "defaultVal": "1",
      "storage": "2",
      "varName2": "myLevel",
      "name": "Store Member Data"
    },
    {
      "member": "3",
      "varName": "member",
      "dataName": "exp",
      "comparison": "4",
      "value": "{serverVars(\"myLevel\")*48}",
      "iftrue": "0",
      "iftrueVal": "",
      "iffalse": "1",
      "iffalseVal": "",
      "name": "Check Member Data"
    },
    {
      "member": "3",
      "varName": "member",
      "dataName": "level",
      "changeType": "1",
      "value": "1",
      "name": "Control Member Data"
    }
  ],
  "_id": "Mtoid"
}

or

I need to figure out how to get the discord.js bot to add roles to members when their xp stored in MySQL reaches a certain level. Here is the discord.js bot code (again with login information removed):

./bot.js

const Discord = require('discord.js');
const bot = new Discord.Client();
const mysql = require("mysql");
const prefix = '.';
const fs = require('fs');

bot.commands = new Discord.Collection();


fs.readdir("./cmds/", (err, files) => {
  if(err) console.error(err);

  let jsfiles = files.filter(f => f.split(".").pop() === "js");
  if(jsfiles.length <= 0) {
    console.log("No commands to load!");
    return;
  }

  console.log(`Loading ${jsfiles.length} commands!`);

  jsfiles.forEach((f, i) => {
    let props = require(`./cmds/${f}`);
    console.log(`${i + 1}: ${f} loaded!`);
    bot.commands.set(props.help.name, props);
  });
});

bot.login(process.env.BOT_TOKEN);

bot.once('ready', () => {
    console.log('Bot is online!')
});

var con =mysql.createConnection({
    host: "host name goes here",
    user: "username goes here",
    password: "password goes here",
    database: "database name goes here"
});

con.connect(err => {
  if(err) throw err;
  console.log("Connected to database.");
  
});

function generateXp() {
  let min = 1
  let max = 1
//the above numbers are both one's because I only want each message to count as 1xp
  return Math.floor(Math.random() * (max - min + 1)) + min;
};

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

  con.query(`SELECT * FROM xp WHERE id = '${message.author.id}'`, (err, rows) => {
    if (err) throw err;
    
    let sql;

    if(rows.length < 1) {
      sql = `INSERT INTO xp (id, xp) VALUES ('${message.author.id}', ${generateXp()})`
    } else {
        let xp = rows[0].xp;
        sql = `UPDATE xp SET xp = ${xp +generateXp()} WHERE id = '${message.author.id}'`;
    }

    con.query(sql);

  });

  if(/(?:https?:\/)?discord(?:app.com\/invite|.gg)/gi.test(message.content)) {
    message.delete();
    return;
  }

  let messageArray = message.content.split(/\s+/g);
  let command = messageArray[0];
  let args = messageArray.slice(1);

  if(!command.startsWith(prefix)) return;

  let cmd = bot.commands.get(command.slice(prefix.length));
  if(cmd) cmd.run(bot, message, args, con);

});

./cmds/xp.js

module.exports.run = async (bot, message, args, con) => {
    let target = message.mentions.users.first() || message.author;

    con.query(`SELECT * FROM xp WHERE id = '${target.id}'`, (err, rows) => {
        if(err) throw err;

        if(!rows[0]) return message.channel.send("This member has no XP on record.")
        let xp = rows[0].xp;
        message.channel.send(xp);
    });
};

module.exports.help = {
    name: "xp"
}

Answer 1

I don't have much experience with Discord Bot Maker, but I can offer a potential solution for discord.js. Its documentation can take a bit of time to get used to but is quite well done and useful for finding what you need.

In your main message event handler, after you run the SQL query that updates their experience points (con.query(sql)), you should check if their points have exceeded the threshold for that role.

// inside the callback for your SELECT query
if (err) throw err;

let sql;
let originalXp;
let updatedXp;

if (rows.length < 1) {
  originalXp = 0;
  updatedXp = generateXp();
  sql = `INSERT INTO xp (id, xp) VALUES ('${message.author.id}', ${updatedXp})`;
} else {
  originalXp = rows[0].xp;
  updatedXp = originalXp + generateXp();
  sql = `UPDATE xp SET xp = ${updatedXp} WHERE id = '${message.author.id}'`;
}

con.query(sql);

// if you want different roles at certain thresholds, replace this with the snippet later in the answer
if (originalXp < SOME_XP_THRESHOLD && updatedXp >= SOME_XP_THRESHOLD) {
  // this example gets a role based on name
  // you could also get the role you want using some other criteria
  const role = message.member.guild.roles.cache.find(role => role.name === ROLE_TO_ADD);
  message.member.roles.add(role);
}

If you want to include multiple roles for hitting various thresholds, you could set them up in an array:

const roles = [
  {
    name: 'foo',
    threshold: 5,
  },
  {
    name: 'bar',
    threshold: 10,
  },
];

and then loop through them:

// insert this snippet instead at the point indicated above
for (const xpRole of roles) {
  if (originalXp < xpRole.threshold && updatedXp >= xpRole.threshold) {
    const role = message.member.guild.roles.cache.find(role => role.name === xpRole.name);
    // you could potentially remove the previous role here as well
    message.member.roles.add(role);
  }
}