SQLite - Register system [ TUTORIAL FS ]

/*
 *      SQLite register system
 *
 *          Used rBits.inc the best for creating efficient arrays.
 *          codename: lorencRegister
 *
 *      Created by Lorenc (c)
*/

#include                        <a_samp>
#include                        <rBits>

/* ** Configuration ** */
#define FILE_VERSION            1.7

/* ** 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
;


public OnFilterScriptInit()
{
    print("\nLorencRegister System ( VERSION "#FILE_VERSION" )\n");
    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;
}

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

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'", 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;
}

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'",
                                                                                        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;
}

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' 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;
}

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;
}