[Lorenc_]

Register System in SQLite using rBits
An efficient way to account creating using SQLite, dialogs and rBits.

Why? What? Why'd you make this

Recently I promised that'd I get a new register system tutorial done really really soon. But it took some time, so yeah. This is one of the second tutorials of registering players that beats the last one by a heap.

All sections are explained in the pawn tags. So notice that!

In this tutorial we'll be saving:

• Name

• Password

• IP address

• Score

• Cash

• Admin Level

Start

Defining and Creating variables and functions

pawn Code:

#include                        <a_samp>
#include                        <rBits>

/* ** Colours ** */
#define COL_GREEN               "{6EF83C}" // GREEN color in HEX ( USED )
#define COL_RED                 "{F81414}" // RED color in HEX ( USED )
#define COL_BLUE                "{00C0FF}" // BLUE color in HEX ( USED )

/* ** Player Data ** */
new
    Bit1:  g_PlayerLogged       <MAX_PLAYERS>, // Creates a 1 bit array
    Bit16: g_AdminLevel         <MAX_PLAYERS>, // Creates a 16 bit array again.
    DB: Database
;

We're going to need all of that above, paste it somewhere in your script, usually at the top.

Functions needed in the procedure!

pawn Code:

stock DB_Escape(text[])
{
    new
        ret[80 * 2],
        ch,
        i,
        j;
    while ((ch = text[i++]) && j < sizeof (ret))
    {
        if (ch == '\'')
        {
            if (j < sizeof (ret) - 2)
            {
                ret[j++] = '\'';
                ret[j++] = '\'';
            }
        }
        else if (j < sizeof (ret))
        {
            ret[j++] = ch;
        }
        else
        {
            j++;
        }
    }
    ret[sizeof (ret) - 1] = '\0';
    return ret;
}

You'll need these functions in order to continue with the tutorial.

Creating/Opening the database

pawn Code:

public OnFilterScriptInit()
{
    Database = db_open("ServerDatabase.db"); // This will open the database under the name, 'ServerDatabase.db'
    //If the table does not excists then we're going to write it inside.
    db_query(Database, "CREATE TABLE IF NOT EXISTS `USERS` (`NAME`, `PASSWORD`, `IP`, `SCORE`, `CASH`, `ADMINLEVEL`)");
    return 1;
}

Here once the server executes it'll create all the databases itself. The procedure of this is:
Open the database > Write to Database > Free result ( Close the Database )

That's why SQLite is a really interesting implemented feature. It's so damn useful! It'll do most of the stuff for you

So make sure you have this in your script otherwise everything else will bug up or glitch.

Warning
Make sure you close the database, anything could go wrong. to do so have it placed like this.

pawn Code:

public OnFilterScriptExit()
{
    for(new i; i != MAX_PLAYERS; i++) OnPlayerDisconnect(i, 1);
    db_close(Database);
    return 1;
}

Opening databases manually
In order to do this, you'll need a get a SQLite database opener. SQLite Database Browser is what I currently use. To get this, type into ****** "SQLite Database Browser 2.0 b1 download" and you will see some results appear.

Retrieving if the player is or isn't registered.

pawn Code:

public OnPlayerConnect(playerid)
{
    new
        Query[ 150 ], // Create a Query
        DBResult: Result, // Create a database Result
        name[ MAX_PLAYER_NAME ] // create a name string
    ;
    GetPlayerName(playerid, name, sizeof(name)); // Gather the players name.
    
    Bit1_Set(g_PlayerLogged, playerid, false); // We're going to reset this variable to 0 = false.
    
    // Okay, we're now going to select from `USERS` where the name equals the players name.
    format(Query, sizeof(Query), "SELECT `NAME` FROM `USERS` WHERE `NAME` = '%s' COLLATE NOCASE", DB_Escape(name));
    // We're going to insert the query inside the db result. Query is to execute what ever said to the DB
    Result = db_query(Database, Query);
    // If the num rows are there, then that means his registered.
    if(db_num_rows(Result))
    {
        // Send a welcome message
        format(Query, sizeof(Query), "{FFFFFF}Welcome "COL_BLUE"%s(%d){FFFFFF} to the server, you're registered\n\nPlease log in by inputting your password.", name, playerid);
        // Show a player the dialog. ( dialog login )
        ShowPlayerDialog(playerid, 1, DIALOG_STYLE_INPUT, "{FFFFFF}Register System", Query, "Login", "Leave");
    }
    else // Else if he isn't, he isn't registered.
    {
        // Send a welcome message
        format(Query, sizeof(Query), "{FFFFFF}Welcome "COL_BLUE"%s(%d){FFFFFF} to the server, you're "COL_RED"not{FFFFFF} registered\n\nPlease log in by inputting your password.", name, playerid);
        // Show a player the dialog. ( dialog register )
        ShowPlayerDialog(playerid, 0, DIALOG_STYLE_INPUT, "{FFFFFF}Register System", Query, "Register", "Leave");
    }
    db_free_result(Result);
    return 1;
}

This code above will show you the steps of how the detection works and is what you'll need, so make sure you have this in your script.

Registering and Logging the player

pawn Code:

public OnDialogResponse(playerid, dialogid, response, listitem, inputtext[])
{
    new
        Query[ 256 ], // Creating a big array this time. No lies :X
        DBResult: Result, // Create a database Result
        name[ MAX_PLAYER_NAME ], // create a name string
        ip[ 16 ] // Create a IP string
    ;
    GetPlayerName(playerid, name, sizeof(name)); // Gather the name of the player.
    GetPlayerIp(playerid, ip, sizeof(ip));
    
    if(dialogid == 1) // The core of the login dialog
    {
        if(response) // Do what ever if the player selected 'Login"
        {
            // if  we find inside users the NAME equaled to the players name and the password equals username then
            format(Query, sizeof(Query), "SELECT * FROM `USERS` WHERE `NAME` = '%s' COLLATE NOCASE AND `PASSWORD` = '%s'", DB_Escape(name), DB_Escape(inputtext));
            // storing ^ that inside the databases result
            Result = db_query(Database, Query);
            // if the result is found that the user and password are exact, do below
            if(db_num_rows(Result))
            {
                new Field[ 20 ]; //Creating a field to retrieve the data
                // get the field "SCORE"'s value and insert it to Field itself.
                db_get_field_assoc(Result, "SCORE", Field, 30);
                // Set the Player score
                SetPlayerScore(playerid, strval(Field));
                // strval is to convert a string into a number
                // Now we keep doing the same thing with the rest.

                // get the field "SCORE"'s value and insert it to Field itself.
                db_get_field_assoc(Result, "CASH", Field, 30);
                // Give the player the value of the field the amount of cash needed
                GivePlayerMoney(playerid, strval(Field));

                // get the field "SCORE"'s value and insert it to Field itself.
                db_get_field_assoc(Result, "ADMINLEVEL", Field, 30);
                // Set the value of the bit "g_AdminLevel" to the fields value
                Bit16_Set(g_AdminLevel, playerid, strval(Field));
                // Log in the player
                Bit1_Set(g_PlayerLogged, playerid, true); 
                // Send a client message about how the progress was between logging in
                SendClientMessage(playerid, -1, "You have "COL_GREEN"successfully{FFFFFF} logged in! ");
            }
            else // If the player's password is wrong:
            {
                // Send a welcome message
                format(Query, sizeof(Query), "{FFFFFF}Welcome "COL_BLUE"%s(%d){FFFFFF} to the server, you're registered\n\nPlease log in by inputting your password.", name, playerid);
                // Show a player the dialog. ( dialog login )
                ShowPlayerDialog(playerid, 1, DIALOG_STYLE_INPUT, "{FFFFFF}Register System", Query, "Login", "Leave");
                // Show the player the wrong password message.
                SendClientMessage(playerid, -1, ""COL_RED"Wrong{FFFFFF} password, try again!");
            }
            db_free_result(Result);
        }
        else return Kick(playerid); // Kick the player if he selected 'Leave'
    }
    if(dialogid == 0) // The core of the register dialog
    {
        if(response) // Do what ever if the player selected 'Register"
        {
            //checking if the password is not is less/higher than 3 characters
            if(strlen(inputtext) > 24 || strlen(inputtext) < 3)
            {
                // Send a welcome message
                format(Query, sizeof(Query), "{FFFFFF}Welcome "COL_BLUE"%s(%d){FFFFFF} to the server, you're "COL_RED"not{FFFFFF} registered\n\nPlease log in by inputting your password.", name, playerid);
                // Reshow this dialog, so we can do this again.
                ShowPlayerDialog(playerid, 0, DIALOG_STYLE_INPUT, "{FFFFFF}Register System", Query, "Register", "Leave");
                // Send a message about the length of characters used for their password.
                SendClientMessage(playerid, -1, "Your password length must be from 3 - 24 characters!");
            }
            else
            {
                // Inserting all these items into the database, confirming it's register was successful.
                format(Query, sizeof(Query), "INSERT INTO `USERS` (`NAME`, `PASSWORD`, `IP`, `SCORE`, `CASH`, `ADMINLEVEL`) VALUES('%s','%s','%s', '0', '500', '0')", DB_Escape(name), DB_Escape(inputtext), DB_Escape(ip));
                // Querying the formatted Query ^
                db_query(Database, Query);
                // Log in the player
                Bit1_Set(g_PlayerLogged, playerid, true); 
                GivePlayerMoney(playerid, 500); // Give Player the money.
                // Reset score.
                SetPlayerScore(playerid, 0);
                // Show a message :)
                SendClientMessage(playerid, -1, "You have "COL_GREEN"successfully{FFFFFF} registered! You have been automatically logged in!");
            }
        }
        else return Kick(playerid); // Kick the player if he selected 'Leave'
    }
    return 1;
}

This code is the core to registering and logging in, without it nothing would work. This will register you/login you in the system.

Saving the player data

pawn Code:

public OnPlayerDisconnect(playerid, reason)
{
    new
        Query[ 200 ], // We need to create such a query so we can format it.
        name[ MAX_PLAYER_NAME ] // create a name string
    ;
    GetPlayerName(playerid, name, sizeof(name)); // Gather the name of the player.

    if(Bit1_Get(g_PlayerLogged, playerid) == 1)
    {
        // Formatting the query containing all the updating stuff, this will update the database with the latest information.
        format(Query,sizeof(Query),"UPDATE `USERS` SET SCORE = '%d', CASH = '%d', ADMINLEVEL = '%d' WHERE `NAME` = '%s' COLLATE NOCASE",
                                                                                        GetPlayerScore(playerid), // Gather the player's score
                                                                                        GetPlayerMoney(playerid), // Gather the player's money
                                                                                        Bit16_Get(g_AdminLevel, playerid), // Gather the Admin Level
                                                                                        DB_Escape(name)); // Gather the name of the player then escape it.
        // querying the formatted Query
        db_query(Database, Query);
        // We're going to reset this bit array to 0, = false.
        Bit1_Set(g_PlayerLogged, playerid, false);
    }
    return 1;
}

All this code will reset the player bit array, g_PlayerLogged; Will even save all the new information the player has.

Credits
Lorenc - Creating the tutorial
RyDeR` - rBits
SA-MP - Implemented SQLite

Download the script

Version 1.7

Thanks Bakr for the efficiency tips. The script should be much much better now.

Thanks AndreT for those player name & ip tips.

Thanks JaTochNietDan for pointing some things out.

* Fixed loading issue
* Uses DB_Escape
* Not using ReturnPlayerName and ReturnPlayerIP
* Slightly more efficient
* Much better in performance.
* Fixed a DB_Escape issue.

Problems?
Please do report problems. I'll be repairing them ASAP. The code looks fine and should work well.

[Jack_Wilson]

That is a nice tutorial, nice to see some SQL being used around on the forum.

[Lorenc_]

Thanks, Here's a screen shot to the GUI and how the styles like.



Have fun coding

[Kush]

Very nice. Well done Lorenc_.

[Davz*|*Criss]

Awesome Tutorial! Good job Lorenc_ :P!

[juraska]

Very nice tutorial !

[Kaperstone]

nice tutorial
very useful..

[Darnell]

A very usefull tutorial.

[Lorenc_]

Thanks for the comments, well said; if there is bugs or problems, tell me so it can be fixed.

99% sure there's nothing wrong though.

[Kush]

I like your method of using the native SQL. Y_Less and I both had discussion about this and it's actually really useful.

[Scenario]

Can I ask why you don't save the username and password into a variable?

[antonio112]

Holy, nice tutorial. I'll give it a try later on. Thanks alot.

[Lorenc_]

Quote:

Originally Posted by RealCop228 Can I ask why you don't save the username and password into a variable?

What's the point firstly?

Quote:

Originally Posted by Kush I like your method of using the native SQL. Y_Less and I both had discussion about this and it's actually really useful.

Haha, thanks

[Bakr]

Nicely explained, however, there are a few errors and or unnecessary things done I would like to point out:

• You free a result when you don't select anything (when creating the table if not exists)
• You don't free the result after selecting information (under OnPlayerConnect)
• You use ` around field names (only necessary around table names)
• You use ' around integer values (only necessary around strings)
• You check if the player is registered under OnDialogResponse in the registration dialog, even though you only show it if they are registered
• When seeing if the player is registered, you select ALL their information, which is unnecessary, and even inefficient with large amounts of fields. Just retrieve one value.

That was just a quick look at the code, didn't study it much. Not to put you down! I'm just trying to help you improve this tutorial.

[Lorenc_]

Quote:

Originally Posted by Bakr Nicely explained, however, there are a few errors and or unnecessary things done I would like to point out: You free a result when you don't select anything (when creating the table if not exists) You don't free the result after selecting information (under OnPlayerConnect) You use ` around field names (only necessary around table names) You use ' around integer values (only necessary around strings) You check if the player is registered under OnDialogResponse in the registration dialog, even though you only show it if they are registered When seeing if the player is registered, you select ALL their information, which is unnecessary, and even inefficient with large amounts of fields. Just retrieve one value. That was just a quick look at the code, didn't study it much. Not to put you down! I'm just trying to help you improve this tutorial.

-- Never mind this post, understand what you mean. I'm adding it now and updating the thread. Thanks a lot.

[Tee]

Well I read somewhere, were freeing something that hasn't been stored would increase the memory usage, anyway nice tutorial.

[Lorenc_]

Quote:

Originally Posted by Tee Well I read somewhere, were freeing something that hasn't been stored would increase the memory usage, anyway nice tutorial.

If that is so, I'll go in to this tommorow, I really need to work on the CTF mode right now :X

Thanks for telling me

[Cowboy]

How will I be able to check the admin level with enums?

How do I open the database, find the players admin level and then use it to check if a player can use a command?

Also, what does "30" mean from this code?

pawn Code:

db_get_field_assoc(Result, "ADMINLEVEL", Field, 30);

Thanks

EDIT:

Need some help on this code

pawn Code:

db_get_field_assoc(Result, "ADMINLEVEL", Field, 30);
PlayerInfo[playerid][pAdminLevel] = strval(Field); // how can I retrieve the data and then put it in that line? The last line occurs an error

[HyperZ]

Great TUT .

[Gh0sT_]

1. 16 bit for admin level? O_O
2. Why you're checking is player connected? There's no point I think