Login

BullseyeHawk · ( *Last edited by BullseyeHawk; 07/02/2014 at 03:05 PM.* )

A quick and basic tutorial for anyone, about an account system with MySQL-R34, if you need to know more about ORM(Object Relational Mapping). Read the tutorial by AndreT.

EDIT: Added saving with ORM.
This account system is going to be working on dialogs, basic and simple. No extra fixes.

At first, we would need to declare our player enum. Where it would have a structure of everything we are planning to use.

pawn Code:

enum _pInfo
{
    ORM:pORM_ID,
    pPassword[128], // 128 character long password, you can change it for your own needs. Note this only save a password 127 length long.
    pUsername[MAX_PLAYER_NAME+1], 
    pKills, // How many times a player killed
    pDeaths, // How many times a player was killed
    pMoney // How much money a player has, you can also set this a long:pMoney but it would need a very custom functioning system.
}
new PlayerInfo[MAX_PLAYERS][_pInfo]; // PlayerInfo[playerid][structure];

As we got that set in, it's time to insert our a_mysql.inc, simply by doings this at under a_samp.inc.

pawn Code:

#include <a_samp>
#include <a_mysql>

Alright, time to get to connect towards the mysql server. OnGameModeInit(); this should be executed:

pawn Code:

// Ontop, near new PlayerInfo[][];
new mysql_connectionHandle;

public OnGameModeInit()
{
    mysql_connectionHandle = mysql_connect("hostname_here", "username_here", "database_here", "password_here");
    // ^ Example: mysql_connect("127.0.0.1", "root", "gameserver_db", "admin");
   
    // Check if we connected!
    if(mysql_errno() == 2003)
    {
        print("[OnGameModeInit] Connection with mysql server couldn't be established, error: 2003");
        SendRconCommand("exit");
        return 1;
    }
    print("[OnGameModeInit] Established connection with mysql server");
}

If you can't connect towards the mysql server, it will return error 2003. (Read more about mysql errors here: click) And it will close the server.
If you could connect towards the mysql server, everything will be fine and you could continue.

Once a player connects, we would want to check if he is registered or not. At firstly, we would make a new thread to load us any variables that the player has in the database.

pawn Code:

public OnPlayerConnet(playerid)
{
    new playerQuery[128]; // Query string.
    GetPlayerName(playerid, playerQuery, sizeof(playerQuery)); // Using on playerQuery to not declare a new expression twice.
    mysql_format(mysql_connectionHandle, playerQuery, sizeof(playerQuery), "SELECT * FROM `accounts` WHERE `username` = '%e'", playerQuery); // mysql format.
    mysql_tquery(mysql_connectionHandle, playerQuery, "OnPlayerMysqlFinish", "d", playerid); // mysql tquery is a new mysql thread query, means it will run apart from the server and won't lag if a lot of data is being loaded.
}

Alright, so we got the player connection part handled, now we would want to actually react when it finishes to load.

pawn Code:

// I am lazy sometimes, so I just add this at the top of the script:
#define Skeleton::%1(%2) forward %1(%2); \
                        public %1(%2)
// This would be the dialog numbers, on top of the script as well.
#define DIALOG_ACCOUNT_LOGIN 1 
// Why not zero? Cause showing dialog zero clears all dialogs from screen.
#define DIALOG_ACCOUNT_REGISTER 2

Skeleton::OnPlayerMysqlFinish(playerid)
{
    if(cache_num_rows() == 1)
    { // Registered
        new ORM:ormid = PlayerInfo[playerid][pORM_ID] = orm_create("accounts");
        // Notice! We're using "accounts" and ... FROM "accounts" .. ; This is mandatory cause it applies on the mysql database.

        orm_addvar_string(ormid, PlayerInfo[playerid][pPassword], 128, "Password"); // Loading string
        orm_addvar_string(ormid, PlayerInfo[playerid][pUsername], MAX_PLAYER_NAME+1, "Username"); // Loading string
        orm_addvar_int(ormid, PlayerInfo[playerid][pKills], "Kills"); // Loading int, "kills"
        orm_addvar_int(ormid, PlayerInfo[playerid][pDeaths], "Deaths"); // Loading int, "deaths"
        orm_addvar_int(ormid, PlayerInfo[playerid][pMoney], "Money"); // Loading int, "money"

        orm_setkey(ormid, "Username");
        orm_apply_cache(); // Apply everything we did above.
        
        ShowPlayerDialog(playerid, DIALOG_ACCOUNT_LOGIN, DIALOG_STYLE_PASSWORD, "Welcome to my server", "Please insert your password down below.", "Login", "Exit");
    }
    else if(!cache_num_rows())
    { // Needs to register.
        ShowPlayerDialog(playerid, DIALOG_ACCOUNT_REGISTER, DIALOG_STYLE_PASSWORD, "Welcome to my server", "Please insert your password down below\nNotice! You'll need to save it to login under this account.", "Register", "Exit");
    }
    else
    { // Too much registered under same name.
        // You can do here whatever you want.
    }
    return 1;
}

So we got that sorted out, let's proceed on towards the dialogs.
You'll need to use two dialogs, one for login, one for register.
Quick note here, I didn't use ORM on register, sorry!

pawn Code:

public OnDialogResponse(playerid, dialogid, response, listitem, inputtext[])
{
    if(dialogid == DIALOG_ACCOUNT_LOGIN)
    {
        if(!response) return Kick(playerid); // If he clicked "EXIT".
        if(response)
        {
             if(strlen(inputtext) == 0) return ShowPlayerDialog(playerid, DIALOG_ACCOUNT_LOGIN, DIALOG_STYLE_PASSWORD, "Welcome to my server", "Please insert your password down below.\nYou can't use an empty password.", "Login", "Exit");
             if(strcmp(inputtext, PlayerInfo[playerid][pPassword], false) == 0)
             { // If password was correct.
                 // Create your own spawn method!
             }
             else return ShowPlayerDialog(playerid, DIALOG_ACCOUNT_LOGIN, DIALOG_STYLE_PASSWORD, "Welcome to my server", "Please insert your password down below.\nPassword was incorrect!", "Login", "Exit");
        }
    }
    else if(dialogid == DIALOG_ACCOUNT_REGISTER)
    {
        if(!response) return Kick(playerid); // If he clicked "EXIT".
        if(response)
        {
            if(strlen(inputtext) == 0) return ShowPlayerDialog(playerid, DIALOG_ACCOUNT_REGISTER, DIALOG_STYLE_PASSWORD, "Welcome to my server", "Please insert your password down below\nNotice! You'll need to save it to login under this account.\nYou can't use an empty password!", "Register", "Exit");
            format(PlayerInfo[playerid][pPassword], 128, "%s", inputtext[0]);
            PlayerInfo[playerid][pKills] = PlayerInfo[playerid][pDeaths] = PlayerInfo[playerid][pMoney] = 0; // Zero-up our values.
            // You can add money with PlayerInfo[playerid][pMoney](server) & use GivePlayerMoney for visual view.
            new playerQuery[128]; // You can change the 128, try to stick towards the int*2 method. 64->128->256->512->etc.
            mysql_format(mysql_connectionHandle, playerQuery, sizeof playerQuery, "INSERT INTO accounts (pPassword, pKills, pDeaths, pMoney) VALUES('%s', '%d', '%d', '%d');", PlayerInfo[playerid][pPassword], PlayerInfo[playerid][pKills], PlayerInfo[playerid][pDeaths], PlayerInfo[playerid][pMoney]);
            mysql_query(mysql_connectionHandle, playerQuery); // NOTE! We are not using a threaded query.
            // Done! Create your own spawn method.
        }
    }
    return 1;
}

Let's add some functions towards the player class, shall we?

pawn Code:

public OnPlayerDeath(playerid, killerid, reason)
{
    if(killerid != INVALID_PLAYER_ID)
    {
        ++PlayerInfo[killerid][pKills];
        PlayerInfo[killerid][pMoney] += 100; GivePlayerMoney(killerid, 100);
        PlayerInfo[playerid][pMoney] -= 100; GivePlayerMoney(playerid, -100);
    }
    PlayerInfo[playerid][pDeaths] += 1;
    return 1;
}

Also, we should destroy ORM's by the end of each session, this should be a simply done when a player discconects:

pawn Code:

public OnPlayerDisconnect(playerid, reason)
{
    if(strlen(PlayerInfo[playerid][pUsername]) != 0)
        orm_update(PlayerInfo[playerid][pORM_ID]);
    if(_:PlayerInfo[playerid][pORM_ID] != 0)
        orm_destroy(PlayerInfo[playerid][pORM_ID]);

    // Let's also clear all values!
    for(new i = 0; i <_:pInfo; i++)
    {
        PlayerInfo[playerid][_pInfo:i] = 0;
    }
    return 1;
}

Alright, now you have completed the tutorial and your account system. A few tips before I let you off:
* Security! - Players can exploit the dialog method, use mysql_escape_string. -> https://sampwiki.blast.hk/wiki/MySQL/R33..._escape_string

This should be your final result:

pawn Code:

#include <a_samp>
#include <a_mysql>

#define Skeleton::%1(%2) forward %1(%2); \
                                    public %1(%2)

#define DIALOG_ACCOUNT_LOGIN 1 
#define DIALOG_ACCOUNT_REGISTER 2

enum _pInfo
{
    ORM:pORM_ID,
    pPassword[128],
    pKills,
    pDeaths,
    pMoney
}

new PlayerInfo[MAX_PLAYERS][_pInfo];
new mysql_connectionHandle;

public OnGameModeInit()
{
    mysql_connectionHandle = mysql_connect("127.0.0.1", "root", "gameserver_db", "admin");
    if(mysql_errno() == 2003)
    {
        print("[OnGameModeInit] Connection with mysql server couldn't be established, error: 2003");
        SendRconCommand("exit");
        return 1;
    }
    print("[OnGameModeInit] Established connection with mysql server");
}

public OnPlayerConnet(playerid)
{
    new playerQuery[128];
    GetPlayerName(playerid, playerQuery, sizeof(playerQuery));
    mysql_format(mysql_connectionHandle, playerQuery, sizeof(playerQuery), "SELECT * FROM `accounts` WHERE `username` = '%e'", playerQuery);
    mysql_tquery(mysql_connectionHandle, playerQuery, "OnPlayerMysqlFinish", "d", playerid);
    return 1;
}

Skeleton::OnPlayerMysqlFinish(playerid)
{
    if(cache_num_rows() == 1)
    {
        new ORM:ormid = PlayerInfo[playerid][pORM_ID] = orm_create("accounts");

        orm_addvar_string(ormid, PlayerInfo[playerid][pPassword], 128, "Password");
        orm_addvar_string(ormid, PlayerInfo[playerid][pUsername], MAX_PLAYER_NAME+1, "Username");
        orm_addvar_int(ormid, PlayerInfo[playerid][pKills], "Kills");
        orm_addvar_int(ormid, PlayerInfo[playerid][pDeaths], "Deaths");
        orm_addvar_int(ormid, PlayerInfo[playerid][pMoney], "Money");

        orm_setkey(ormid, "Username");
        orm_apply_cache();
        
        ShowPlayerDialog(playerid, DIALOG_ACCOUNT_LOGIN, DIALOG_STYLE_PASSWORD, "Welcome to my server", "Please insert your password down below.", "Login", "Exit");
    }
    else if(!cache_num_rows())
    {
        ShowPlayerDialog(playerid, DIALOG_ACCOUNT_REGISTER, DIALOG_STYLE_PASSWORD, "Welcome to my server", "Please insert your password down below\nNotice! You'll need to save it to login under this account.", "Register", "Exit");
    }
    else return SendClientMessage(playerid, -1, "Duplicated account, contanct the administartor."), Kick(playerid);
    return 1;
}

public OnDialogResponse(playerid, dialogid, response, listitem, inputtext[])
{
    if(dialogid == DIALOG_ACCOUNT_LOGIN)
    {
        if(!response) return Kick(playerid);
        if(response)
        {
             if(strlen(inputtext) == 0) return ShowPlayerDialog(playerid, DIALOG_ACCOUNT_LOGIN, DIALOG_STYLE_PASSWORD, "Welcome to my server", "Please insert your password down below.\nYou can't use an empty password.", "Login", "Exit");
             if(strcmp(inputtext, PlayerInfo[playerid][pPassword], false) == 0) {}
             else return ShowPlayerDialog(playerid, DIALOG_ACCOUNT_LOGIN, DIALOG_STYLE_PASSWORD, "Welcome to my server", "Please insert your password down below.\nPassword was incorrect!", "Login", "Exit");
        }
    }
    else if(dialogid == DIALOG_ACCOUNT_REGISTER)
    {
        if(!response) return Kick(playerid);
        if(response)
        {
            if(strlen(inputtext) == 0) return ShowPlayerDialog(playerid, DIALOG_ACCOUNT_REGISTER, DIALOG_STYLE_PASSWORD, "Welcome to my server", "Please insert your password down below\nNotice! You'll need to save it to login under this account.\nYou can't use an empty password!", "Register", "Exit");
            format(PlayerInfo[playerid][pPassword], 128, "%s", inputtext[0]);
            PlayerInfo[playerid][pKills] = PlayerInfo[playerid][pDeaths] = PlayerInfo[playerid][pMoney] = 0;
            new playerQuery[128];
            mysql_format(mysql_connectionHandle, playerQuery, sizeof playerQuery, "INSERT INTO accounts (pPassword, pKills, pDeaths, pMoney) VALUES('%s', '%d', '%d', '%d');", PlayerInfo[playerid][pPassword], PlayerInfo[playerid][pKills], PlayerInfo[playerid][pDeaths], PlayerInfo[playerid][pMoney]);
            mysql_query(mysql_connectionHandle, playerQuery);
        }
    }
    return 1;
}

public OnPlayerDeath(playerid, killerid, reason)
{
    if(killerid != INVALID_PLAYER_ID)
    {
        ++PlayerInfo[killerid][pKills];
        PlayerInfo[killerid][pMoney] += 100; GivePlayerMoney(killerid, 100);
        PlayerInfo[playerid][pMoney] -= 100; GivePlayerMoney(playerid, -100);
    }
    PlayerInfo[playerid][pDeaths] += 1;
    return 1;
}

public OnPlayerDisconnect(playerid, reason)
{
    if(strlen(PlayerInfo[playerid][pORM_ID]) != 0)
        orm_update(PlayerInfo[playerid][pORM_ID]);
    if(_:PlayerInfo[playerid][pORM_ID] != 0)
        orm_destroy(PlayerInfo[playerid][pORM_ID]);

    for(new i = 0; i <_:pInfo; i++)
    {
        PlayerInfo[playerid][_pInfo:i] = 0;
    }
    return 1;
}

Konstantinos · 07.02.2014, 10:56

It's a good tutorial. It'd be nice if you used ID for each player and use orm_setkey function as well. When a player disconnects, orm_update should be used so it will update the row of that player.

Something last:

pawn Code:

// OnPlayerMysqlFinish:
    if(cache_num_rows() == 1)
    {
        new ORM:ormid = PlayerInfo[playerid][pORM_ID] = orm_create("accounts");

        orm_addvar_string(ormid, PlayerInfo[playerid][pPassword], 128, "Password");
        orm_addvar_int(ormid, PlayerInfo[playerid][pKills], "Kills");
        orm_addvar_int(ormid, PlayerInfo[playerid][pDeaths], "Deaths");
        orm_addvar_int(ormid, PlayerInfo[playerid][pMoney], "Money");

        orm_apply_cache();
        
        ShowPlayerDialog(playerid, DIALOG_ACCOUNT_LOGIN, DIALOG_STYLE_PASSWORD, "Welcome to my server", "Please insert your password down below.", "Login", "Exit");
    }
    else if(!cache_num_rows())
    {
        ShowPlayerDialog(playerid, DIALOG_ACCOUNT_REGISTER, DIALOG_STYLE_PASSWORD, "Welcome to my server", "Please insert your password down below\nNotice! You'll need to save it to login under this account.", "Register", "Exit");
    }
    else {}

It can become:

pawn Code:

if(cache_num_rows())
{
    new ORM:ormid = PlayerInfo[playerid][pORM_ID] = orm_create("accounts");
    
    orm_addvar_string(ormid, PlayerInfo[playerid][pPassword], 128, "Password");
    orm_addvar_int(ormid, PlayerInfo[playerid][pKills], "Kills");
    orm_addvar_int(ormid, PlayerInfo[playerid][pDeaths], "Deaths");
    orm_addvar_int(ormid, PlayerInfo[playerid][pMoney], "Money");
    
    orm_apply_cache();
    
    ShowPlayerDialog(playerid, DIALOG_ACCOUNT_LOGIN, DIALOG_STYLE_PASSWORD, "Welcome to my server", "Please insert your password down below.", "Login", "Exit");
}
else ShowPlayerDialog(playerid, DIALOG_ACCOUNT_REGISTER, DIALOG_STYLE_PASSWORD, "Welcome to my server", "Please insert your password down below\nNotice! You'll need to save it to login under this account.", "Register", "Exit");

Good effort though.

BullseyeHawk · 07.02.2014, 13:59

Quote:

Originally Posted by Konstantinos

It's a good tutorial. It'd be nice if you used ID for each player and use orm_setkey function as well. When a player disconnects, orm_update should be used so it will update the row of that player.

Something last:

pawn Code:

// OnPlayerMysqlFinish:
    if(cache_num_rows() == 1)
    {
        new ORM:ormid = PlayerInfo[playerid][pORM_ID] = orm_create("accounts");

        orm_addvar_string(ormid, PlayerInfo[playerid][pPassword], 128, "Password");
        orm_addvar_int(ormid, PlayerInfo[playerid][pKills], "Kills");
        orm_addvar_int(ormid, PlayerInfo[playerid][pDeaths], "Deaths");
        orm_addvar_int(ormid, PlayerInfo[playerid][pMoney], "Money");

        orm_apply_cache();
        
        ShowPlayerDialog(playerid, DIALOG_ACCOUNT_LOGIN, DIALOG_STYLE_PASSWORD, "Welcome to my server", "Please insert your password down below.", "Login", "Exit");
    }
    else if(!cache_num_rows())
    {
        ShowPlayerDialog(playerid, DIALOG_ACCOUNT_REGISTER, DIALOG_STYLE_PASSWORD, "Welcome to my server", "Please insert your password down below\nNotice! You'll need to save it to login under this account.", "Register", "Exit");
    }
    else {}

It can become:

pawn Code:

if(cache_num_rows())
{
    new ORM:ormid = PlayerInfo[playerid][pORM_ID] = orm_create("accounts");
    
    orm_addvar_string(ormid, PlayerInfo[playerid][pPassword], 128, "Password");
    orm_addvar_int(ormid, PlayerInfo[playerid][pKills], "Kills");
    orm_addvar_int(ormid, PlayerInfo[playerid][pDeaths], "Deaths");
    orm_addvar_int(ormid, PlayerInfo[playerid][pMoney], "Money");
    
    orm_apply_cache();
    
    ShowPlayerDialog(playerid, DIALOG_ACCOUNT_LOGIN, DIALOG_STYLE_PASSWORD, "Welcome to my server", "Please insert your password down below.", "Login", "Exit");
}
else ShowPlayerDialog(playerid, DIALOG_ACCOUNT_REGISTER, DIALOG_STYLE_PASSWORD, "Welcome to my server", "Please insert your password down below\nNotice! You'll need to save it to login under this account.", "Register", "Exit");

Good effort though.

I'll see what I will do with the orm_update,

on the second suggestion, I've declared it in that way, due to mysql can have duplications of the same name.
That blocks it by default. if it finds one, okay. If it finds none, okay. If it finds more then one, not okay.

iZN · 07.02.2014, 14:23

Very nice tutorial. I do understand ORM now; they're more clear, thanks. Those who are thinking to converting their script from cache to ORM, there is no difference between them, just that ORM is easy to manage and clear up some mess which doesn't needs much MySQL knowledge :P

Konstantinos · 07.02.2014, 14:38

ORM is used to execute any query for you, so when a player registers and using orm_insert will do that automatically instead of format the query and execute it.

Quote:

Originally Posted by BullseyeHawk

.. due to mysql can have duplications of the same name.

It allows users to register their name only and only if there are not already rows so it will never going to duplicate the same user name.

Last, in OnPlayerDeath prevent a run time error: Array index out of bounds from being caused by simply checking whether the killerid is a valid player otherwise it will access element at index 65535 (INVALID_PLAYER_ID).

pawn Code:

// Those:
PlayerInfo[killerid][pKills] += 1;
...
PlayerInfo[killerid][pMoney] += 100;

BullseyeHawk · 07.02.2014, 15:07

Quote:

Originally Posted by Konstantinos

ORM is used to execute any query for you, so when a player registers and using orm_insert will do that automatically instead of format the query and execute it.

It allows users to register their name only and only if there are not already rows so it will never going to duplicate the same user name.

Last, in OnPlayerDeath prevent a run time error: Array index out of bounds from being caused by simply checking whether the killerid is a valid player otherwise it will access element at index 65535 (INVALID_PLAYER_ID).

pawn Code:

// Those:
PlayerInfo[killerid][pKills] += 1;
...
PlayerInfo[killerid][pMoney] += 100;

Hmh, never thought of it that way. The script I am originaly building (apart from this tutorial) is based on a web-site, I took pre-caution if anything happens to not create duplicated accounts. So that way if a duplicate account happens to be found, it'll alert the player to contact an admin.

Oh yeah, about the OnPlayerDeath, I forgot it could call an invalid playerid. I fixed that up quickly, thanks for notifying.

Dorito · 08.02.2014, 06:00

You should add in saving positions to this tutorial, would be great!

Other than that, good job.

Lordzy · 08.02.2014, 06:37

Good tutorial, but why this? :

Quote:

pawn Code:

// This would be the dialog numbers, on top of the script as well.
#define DIALOG_ACCOUNT_LOGIN 1 
// Why not zero? Cause showing dialog zero clears all dialogs from screen.

Zero doesn't clear every dialogs from the screen, -1 does. Also, explaining about "_:" being used before those arrays would be good, because many of them don't know that it's being used to avoid tag mismatches.

yvoms · 08.11.2015, 15:25

is this piece of code invalid or outdated?
i've tried debugging it, and its not using the skeleton.

Code:

public OnPlayerConnect(playerid)
{
    print("[Debug] OnplayerConnect is being called.");
    new playerQuery[128]; // Query string.
    GetPlayerName(playerid, playerQuery, sizeof(playerQuery)); // Using on playerQuery to not declare a new expression twice.
    mysql_format(mysql_connectionHandle, playerQuery, sizeof(playerQuery), "SELECT * FROM `accounts` WHERE `username` = '%e'", playerQuery); // mysql format.
    print("[Debug] It checked for the account and calls OnPlayerMysqlFinish");
	mysql_tquery(mysql_connectionHandle, playerQuery, "OnPlayerMysqlFinish", "d", playerid); // mysql tquery is a new mysql thread query, means it will run apart from the server and won't lag if a lot of data is being loaded.
    print("[Debug]	OnPlayerMysqlFinish Passed");
	return 1;
}

Skeleton::OnPlayerMysqlFinish(playerid)
{
	if(cache_num_rows())
	{
	new ORM:ormid = PlayerInfo[playerid][ORM_ID] = orm_create("accounts");
	orm_addvar_string(ormid, PlayerInfo[playerid][Username], MAX_PLAYER_NAME+1, "Username"); // Loading string
	orm_addvar_string(ormid, PlayerInfo[playerid][Password], 128, "Password");
	orm_addvar_int(ormid, PlayerInfo[playerid][Money], "Money");
	orm_addvar_int(ormid, PlayerInfo[playerid][Kills], "Kills");
	orm_addvar_int(ormid, PlayerInfo[playerid][Deaths], "Deaths");
	orm_setkey(ormid, "Username");
	orm_apply_cache();
	ShowPlayerDialog(playerid, DIALOG_ACCOUNT_LOGIN, DIALOG_STYLE_PASSWORD, "Welcome to my server", "Please insert your password down below.", "Login", "Exit");
	}
	else ShowPlayerDialog(playerid, DIALOG_ACCOUNT_REGISTER, DIALOG_STYLE_PASSWORD, "Welcome to my server", "Please insert your password down below\nNotice! You'll need to save it to login under this account.", "Register", "Exit");
	return 1;
}

(using the latest mysql plugin)

Login
Username:
Password:	Lost Password?
	Remember me