Discord JS | MYSQL read from mysql Table

Question

I'm trying to connect my Discord Bot to an MYSQL Database. My schema is:

> CREATE DATABASE my_db;

CREATE TABLE Guilds (
    guildId VARCHAR(100) NOT NULL PRIMARY KEY,
    guildOwnerId VARCHAR(100) NOT NULL
);

CREATE TABLE GuildConfigurable (
    guildId VARCHAR(100) NOT NULL PRIMARY KEY,
    cmdPrefix VARCHAR(10) DEFAULT '<',
    radioChannelId VARCHAR(100) DEFAULT ' ' NOT NULL,
    memberCountId VARCHAR(100) DEFAULT ' ' NOT NULL,
    AutoStartRadio VARCHAR (10) DEFAULT 'N'
);

I can write in the Database using:

client.on('guildCreate', (guild) => {
    console.log('[INFO]'.green + ' The Bot has been added to a new Server! (' + guild + ')')
    connection.query(
        `INSERT INTO GuildConfigurable(guildId) VALUES ('${guild.id}')`
    )
    connection.query(
        `INSERT INTO Guilds VALUES('${guild.id}', '${guild.ownerID}')`
    )
});

But when trying to read Data using:

client.guilds.cache.forEach(guild => {
    connection.promise().query(
        `SELECT cmdPrefix FROM GuildConfigurable WHERE guildId = '${guild.id}'`
    ).then(result => {
        guildCommandPrefix.set(guild.id, result[0][0].cmdPrefix);
    }).catch(err => console.log(err));
})

I get this error:

TypeError: Cannot read property 'cmdPrefix' of undefined
    at /home/ubuntu/discord-bot-v2/main.js:39:55
    at processTicksAndRejections (node:internal/process/task_queues:94:5)
TypeError: Cannot read property 'cmdPrefix' of undefined
    at /home/ubuntu/discord-bot-v2/main.js:39:55
    at processTicksAndRejections (node:internal/process/task_queues:94:5)

Btw. When adding this line:

console.log(result[0][0])

I get this output:

TextRow { cmdPrefix: '<' }
undefined
TypeError: Cannot read property 'cmdPrefix' of undefined
    at /home/ubuntu/discord-bot-v2/main.js:40:55
    at processTicksAndRejections (node:internal/process/task_queues:94:5)
TextRow { cmdPrefix: '<' }
undefined
TypeError: Cannot read property 'cmdPrefix' of undefined
    at /home/ubuntu/discord-bot-v2/main.js:40:55
    at processTicksAndRejections (node:internal/process/task_queues:94:5)

But I want to write the result (the value of "cmdPrefix") into a variable to use later. What am I doing wrong?

cseitz · Accepted Answer · 2021-03-07 18:57:28Z

1

It seems like you are performing operations on guilds that aren't in the database.

You are only inserting into the database when the client joins a guild, but are fetching the command prefix from all the guilds the client is in. There must be a guild that your bot is in that is not in the database.

Even though it is displaying cmdPrefix: '<' inside the TextRow, the fact that it is returning undefined means that the record does not exist.

I would recommend you change console.log(result[0][0]) to console.log(guild.id, result[0][0]). This will print the guild it is trying to fetch along with the TextRow.

Double-check that every guild id it tries to fetch is, in fact, in the database.

If it is the case that you are missing some guilds, you could run another insert upon a undefined entry, or just use some conditional logic and your problem should be solved.

guildCommandPrefix.set(guild.id, result[0][0].cmdPrefix || '<');

answered Mar 7, 2021 at 18:57

cseitz

1,2461 gold badge6 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

SoldØut Over a year ago

Ok now I'm getting the guild ids and the prefix. But how can i only get that informations from 1 server and not from every?

cseitz Over a year ago

I am unsure of what you mean by that. Can you explain a little more?

SoldØut Over a year ago

I want to make a command to change the Bot prefix. That's why I need to save them in a Database. So I read from the Database table the prefix for a server. So I want to "ask" the Database for the prefix from a specific guildId and then write the result (only the prefix) in a variable. Hope u understand me… I'm so bad at explaining things... :D

SoldØut Over a year ago

now I'm getting all the guildIds and prefixes as result. So I want the output to be just "<".

cseitz Over a year ago

Take a look into SQL update queries. w3schools.com/sql/sql_update.asp UPDATE GuildConfigurable SET cmdPrefix = "!" WHERE guildId = "GUILD ID GOES HERE"

|

Collectives™ on Stack Overflow

Discord JS | MYSQL read from mysql Table

1 Answer 1

8 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Related