tutorial [Tutorial] How to create a database and save a player's coordinates in it

[Almeidowski]

How to create a database and save a player's coordinates in it

Hello. As a beginner I had lots of trouble finding a MySQL appropriate tutorial for beginners in development. I spent days learning how to create a basic table and save records in it. I decided to create this tutorial so that no one has to go throught what I went. This tutorial is divided in X parts. Feel free to jump ahead if you already to know how to do some.

 

Index

1. Downloading MySQL and MySQL Workbench
2. Installing MySQL Driver
3. Creating a database
4. Connecting to the database and creating a table in it
5. Manipulating the table

 

1- Installing MySQL and MySQL Workbench

For you to be able to save databases and work with them you must first install MySQL and MySQL Workbench.

Firstly, go to https://www.mysql.com/downloads/. Now, follow the steps on this video:
https://www.youtube.com/watch?v=GIRcpjg-3Eg. I won't do a tutorial on how to install MySQL as it is to complex and very standart to everyone.

 

2- Installing MySQL Driver

After installing MySQL we can access it by using Node.js. This tutorial will use the "mysql" module, downloaded from NPM. To download and install the "mysql" module, open the Command Terminal (go to your search bar and type 'cmd') and execute the following:

C:\Users\Your Name>npm install mysql

 

3- Creating a database

If you corretly followed the video above mentioned, you should have something like this once you open your MySQL Workbench:

https://imgur.com/wSHrWr0

https://imgur.com/wSHrWr0

You want to press on Local instance MySQL80 or on any other connection you have. Once you do, the following page will open:

https://imgur.com/vzSUhvS

Right click on the left side box named Schemas and click 'Create Schema':

https://imgur.com/rPg6RVi

Name it 'rpg' (or whatever you want, just pay attention on the future steps) and click Apply twice and Finish.

https://imgur.com/c1CrPwA

Nice. Your database has been created. Now, we must connect to it.

 

4- Connecting to the database and creating a table in it

To connect to a database, create a file on your server-files folder named connection.js. Once you create one go to your index.js and add in it the following:

require('./connection.js')

This will make your server know this file exists and run the code in it. Now, back to connection.js, type the following:

var mysql = require('mysql');

With the first line, we are now importing the 'mysql' module to the file.

Now, in the same file, type this:

const con = mysql.createConnection({
  host:'127.0.0.1', // host of server
  user:'user', // MySQL user
  password:'password', // MySQL password
  database: "rpg"
});

With this code we are connecting to the database you host. Make sure to replace the user, the password and the database with whatever you had chosen in the previous steps.

After connecting to the database, we can now manipulate it. Firstly, we'll create a table. The table can be created on MySQL Workbench or on the javascript file. If you create it on the file, it will try to create it everytime you open your ragemp-server, but it will return an innofensive error if it already exists. I'll create in on the file. Type the following:

var sql = "CREATE TABLE players (id int NOT NULL AUTO_INCREMENT PRIMARY KEY, scID int NOT NULL UNIQUE, posX int NOT NULL, posY int NOT NULL, posZ int NOT NULL)";
  con.query(sql, function (err, result) {
    if (err) {
      console.log('The following error happened while creating [players]: ' + err);  
      throw err;
    }
    console.log("[players] has been created");
});

With the code above we've just created a table called 'players' which has 5 columns: 'id', 'scID', 'posX', 'posY', 'posZ'. As you can see, all of them are integers, which means they only accept numbers. If you try to add 'banana' to 'posX', you'll get an error. Also, all of them are NOT NULL, which means that there must be a value in them, they can't be empty, or you'll also get an error. 'scID' is unique, which means that the same data can't be repeated twice. Finally, 'ID' is a Primary Key, which is a mix between NOT NULL and UNIQUE. It uniquely identifies each record in a table. It is also AUTO_INCREMENT, which means a new number will be automatically generated when a new record is inserted into a table.

After typing it, open your console. You received the following log: "[players] has been created". It obviously confirms that the table has been created. If you close your console and open it again, you'll instead receive this log: "The following error happened while creating [players]: Error: ER_TABLE_EXISTS_ERROR: Table 'players' already exists". That means that the table couldn't be created because it already exists. There's nothing wrong with the error. You can change it so the server doesn't try to create a table if it already exists, but I'll just leave it be.

Now that our table is created, we want to manipulate it, but first make sure you add the module below to the bottom of connection.js:

exports.con = con

This will make it so that the const 'con' can be used in any file that imports it, which will be necessary for the next step.

 

5- Manipulating the table

Now that we've created the table, we wan't to manipulate it according to our needs. You can do this on the same file, but we'll create another one. On the server-files folder, create a file named database.js (or whatever you want). Once again, make sure to add the require module to index.js:

require('./database.js')

Now, back to database.js, add the following:

const con = require('./connection').con;

This will import the 'con' const from connection.js, so that we can use it on this file.

Now, we want to add a player's Rockstar Games Social Services ID to our table once he joins for the first time. To do it, write the code above:

mp.events.add('playerJoin', (player) => {
    const socialID = player.rgscId;
    console.log(`[SERVER]: ${player.name} [SCID: ${player.rgscId}, ID: ${player.id}] entrou no servidor.`);
    con.query('INSERT INTO players (scID, posX, posY, posZ) VALUES ('+socialID+', 174, -919, 30.687)', function (err, result) {
        if (err) {
                    console.log('The following error happened while inserting a new record to [players]: ' + err)
                    throw err;
        }
        else {
            console.log(`New player: ${player.name}`);
        };
        player.spawn(new mp.Vector3(174, -919, 30.687));
    });
});

With this code, once a player join's we'll get is Social ID, send a log to the console informing us about his connection, insert his Social ID into our database (so that we can identify him later) and spawning him on X: 174, Y: -919, Z: 30.687. Now, as we made it so that the scID (Social ID) was UNIQUE, if a player is already in the database we can't add him again, and if we try to it will return an error. So, if you connect to your server for the first time, you'll get the log: "New player: [Your Name]". If you connect again, you'll get the log: 'The following error happened while inserting a new record to [players]: Error: ER_DUP_ENTRY: Duplicate entry '12345678' for key 'players.scID'. This means it detected that the scID you tried to insert already existed and, because the column is unique, we can't insert a similar one.

Now, we can use this error to our advantage, as we don't need to check if a value already exists, because the error already tells us if it does or not. So, replace the code above with the one below:

mp.events.add('playerJoin', (player) => {
    const socialID = player.rgscId;
    console.log(`[SERVER]: ${player.name} [SCID: ${player.rgscId}, ID: ${player.id}] entrou no servidor.`);
    con.query('INSERT INTO players (scID, posX, posY, posZ) VALUES ('+socialID+', 174, -919, 30.687)', function (err, result) {
        if (err) {
            switch(err.code) {
                case "ER_DUP_ENTRY":
                    console.log("The following error happened while inserting a new record to [players]: " + err);
                    con.query('SELECT posX, posY, posZ FROM players WHERE scID = '+socialID+'', function (err, result) {
                        if (err) {
                            console.log('The following error happened while selecting scID from [players]: ' + err);
                            throw err;
                        }
                        else {
                            player.spawn(new mp.Vector3(result[0].posX, result[0].posY, result[0].posZ));
                        }
                    });
                    break;
                default:
                    console.log('The following error happened while inserting a new record to [players]: ' + err)
                    throw err;
            }
        }
        else {
            console.log(`Novo jogador: ${player.name}`);
        };
        player.spawn(new mp.Vector3(174, -919, 30.687));
    });
});

With this code, if there's an rror we'll check if the error is "ER_DUP_ENTRY" (if the value is duplicated). If it is, we will stop trying to insert it and actually use the coordinates assigned to a Social ID to spawn the player with that Social ID on those coordinates. We select 'posX', 'posY' and 'posZ' from the row where the 'scID' value is equal to the Social ID of the player that joined the server and spawn that player in those positions. If there's an error while selecting the values, we'll make sure that it is logged. Also, as we don't know for sure that the only error that we will face is "ER_DUP_ENTRY", we make sure to add a switch statement. If the error is the mention, we'll try to spawn the player and if it isn't we'll log it.

Now, we made it so that a player spawns in the coordinates associated to him, but we need to actually made those coordinates change whenever he leaves. For this, add this code to the same file:

mp.events.add('playerQuit', (player, exitType, reason) => {
    const socialID = player.rgscId;
    pos = player.position
    console.log(`[SERVER]: ${player.name} [SCID: ${player.rgscId}, ID: ${player.id}] left the server.`);
    con.query('UPDATE players SET posX = '+pos.x+', posY = '+pos.y+', posZ = '+pos.z+' WHERE scID = '+socialID+'', function (err, result) {
        if (err) {
            console.log(err);
            throw err;
        }
        else {
            console.log("Coordinates added")
        }
    });
});

This code means that once a player quits the server, we'll log the console of his disconnect and update the 'players' table with the coordinates where he left the server. We search for the row where his Social ID equals 'scID' and change his coordinates to the ones we obtained with the 'pos' var. If we do it successfuly, we'll log to the console: "Coordinates added". If there's an error, we'll log it.

This is it. Thank you very much. If you know of anyway I can make my code better, let me know.

Edited September 3, 2020 by Almeidowski

[Spliddorama]

.

Edited May 30, 2021 by Spliddorama

[Almeidowski]

 

Coo9l

[eubenG07]

what does it mean by on the java script file