SA-MP Forums

Go Back   SA-MP Forums > SA-MP Scripting and Plugins > Scripting Help > Tutorials

Reply
 
Thread Tools Display Modes
Old 05/01/2014, 05:36 AM   #1
newbienoob
High-roller
 
newbienoob's Avatar
 
Join Date: Jan 2012
Location: Follow the moaning coming from your parents' bedroom
Posts: 1,534
Reputation: 265
Default MySQL Registration System [Threaded Queries + Whirlpool]

Introduction
First of all, it's been a long time since I didn't release anything on this forum. So today I'm gonna release something that is, I think pretty useful for those who wanna learn how to create a registration system with mysql threaded queries. Many people use non-threaded queries because they think it's harder to use threaded queries. But in fact, it's just way easier than you thought. So today, I will teach so you how to make a registration system using mysql threaded queries(version r33 and above). In this tutorial, we will save player's name, password, ip, admin level, vip level, money, and player's position.

Things that you need
- Mysql plugin - Download it and place the files in your server's directory.
- Whirlpool - Download it and place the file in your server's directory.
- xampp - Download it and install it in your pc.

Creating a new database and table
- Run xampp-control and you should see this.


- Click Start on Apache and MySQL. If you have skype opened, close it.


- Click Admin at MySQL to manage/create your database.


- Click on Databases to create a new database.


- Name your server database and click Create (In this tutorial will use "server" as our database)


- After you click the Create button, you will notice your database has been created by looking at left side of your screen. Click your database to manage your table's name and column.


- You will see this screen after you click your database. (More explanation in the image)


- After you click Go button, it will redirect to you the next page where you can manage your table's column.


Congratulations, you have created your database and table! Now;

Let's start scripting!

pawn Code:
//First, of course we need to include these files first
#include <a_samp> //Without this, we won't be able to use all samp functions/callbacks
#include <a_mysql> //Without this, we won't be able to use all mysql functions

pawn Code:
//Let's define our mysql settings
#define host    "localhost" //This will be your mysql host. Default for xampp is localhost
#define user    "root" //This will be your mysql username. Default for xampp is root
#define db      "server" //This is your database name. Remember we have created a database called server before.
#define pass    "" //This is your mysql password. In xampp, the password didn't set. So leave it empty.

//dialogs
#define dregister    6287 //dialog register id
#define dlogin        6288 // ^

pawn Code:
//Global variables. We will use them later
static
    mysql, //This variable will be used to manage our database
    Name[MAX_PLAYERS][24], //We will use this variable to store player's name.
    IP[MAX_PLAYERS][16] //We will use this variable to store player's ip.
    ;

pawn Code:
native WP_Hash(buffer[], len, const str[]); //whirlpool, for hashing our password

pawn Code:
//Now let's create an enumerator that holds player's information
enum PDATA //We name our enumerator as PDATA (which stands for PlayerDATA). You can name it however you want.
{
    ID, //Will be used later to store player's ID from database so we can use it anywhere later
    Password[129], //We will load player's password into this varible from database
    Admin, //We will load player's admin level from database into this variable so we can use it anywhere later.
    VIP, //We will load player's VIP level from database into this variable so we can use it anywhere later.
    Money, //We will load player's money from database into this variable so we can use it anywhere later.
    Float:posX, //We will load player's X position from database into this variable so we can use it anywhere later.
    Float:posY, //We will load player's Y position from database into this variable so we can use it anywhere later.
    Float:posZ //We will load player's Z from database into this variable so we can use it anywhere later.

}
new pInfo[MAX_PLAYERS][PDATA]; //Variable that stores enumerator above

pawn Code:
public OnGameModeInit()
{
    mysql_log(LOG_ERROR | LOG_WARNING | LOG_DEBUG); //Let's enable debugging so we can detect a problem(if there is)
    mysql = mysql_connect(host, user, db, pass); //This function will connect your server to database. Remember we have defined our host, username, database and password. It's time to use it here.
    if(mysql_errno(mysql) != 0) print("Could not connect to database!"); //This will tell if your connection to database is successful or not. If it's not, check your host, username, database and password. Make sure they all right.
    return 1;
}

pawn Code:
//Checking player's account if they are registered or not.
public OnPlayerConnect(playerid)
{
    new query[128]; //We use this variable to format our query
    GetPlayerName(playerid, Name[playerid], 24); //Getting player's name
    GetPlayerIp(playerid, IP[playerid], 16); //Getting layer's IP
    mysql_format(mysql, query, sizeof(query),"SELECT `Password`, `ID` FROM `players` WHERE `Username` = '%e' LIMIT 1", Name[playerid]);
    // - We use mysql_format instead of format because we can use an %e specifier. %e specifier escapes a string so we can avoid sql injection which means we don't have to use mysql_real_escape_string
    // - Formatting our query; SELECT `Password`, `ID` FROM `players` WHERE `Username`='%e' means we are selecting a Password and ID's column in the table that has player's name in Username column.
    // - LIMIT 1; we only need 1 result to be shown
    mysql_tquery(mysql, query, "OnAccountCheck", "i", playerid);
    //lets execute the formatted query and when the execution is done, a callback OnAccountCheck will be called
    //You can name the callback however you like

    return 1;
}

pawn Code:
//OnAccountCheck is a custom callback which means it has to be forwarded.
forward OnAccountCheck(playerid);

//Now once the query has been processed;
public OnAccountCheck(playerid)
{
    new rows, fields; //a variable that will be used to retrieve rows and fields in the database.
    cache_get_data(rows, fields, mysql);//let's get the rows and fields from the database.
    if(rows) //if there is row
    {//then
        cache_get_field_content(0, "PASS", pInfo[playerid][Password], mysql, 129);
        //we will load player's password into pInfo[playerid][Password] to be used in logging in
        pInfo[playerid][ID] = cache_get_field_content_int(0, "ID"); //now let's load player's ID into pInfo[playerid][ID] so we can use it later
        printf("%s", pInfo[playerid][Password]); //OPTIONAL: Just for debugging. If it didn't show your password, then there must be something wrong while getting player's password
        ShowPlayerDialog(playerid, dlogin, DIALOG_STYLE_INPUT, "Login", "In order to play, you need to login", "Login", "Quit"); //And since we found a result from the database, which means, there is an account; we will show a login dialog
    }
    else //if we didn't find any rows from the database, that means, no accounts were found
    {
        ShowPlayerDialog(playerid, dregister, DIALOG_STYLE_INPUT, "Register", "In order to play, you need to register.", "Register", "Quit");
        //So we show them a dialog register
    }
    return 1;
}

pawn Code:
//Now let's response to the login and register dialog
public OnDialogResponse(playerid, dialogid, response, listitem, inputtext[])
{
    switch(dialogid)
    {
        case dlogin: //login dialog
        {
            if(!response) Kick(playerid); //if they clicked Quit, we will kick them
            new hpass[129]; //for password hashing
        new query[100]; // for formatting our query.
            WP_Hash(hpass, 129, inputtext); //hashing inputtext
            if(!strcmp(hpass, pInfo[playerid][Password])) //remember we have loaded player's password into this variable, pInfo[playerid][Password] earlier. Now let's use it to compare the hashed password with password that we load
            { //if the hashed password matches with the loaded password from database
                mysql_format(mysql, query, sizeof(query), "SELECT * FROM `players` WHERE `Username` = '%e' LIMIT 1", Name[playerid]);
                //let's format our query
                //We select all rows in the table that has your name and limit the result to 1
                mysql_tquery(mysql, query, "OnAccountLoad", "i", playerid);
                //lets execute the formatted query and when the execution is done, a callback OnAccountLoad will be called
                //You can name the callback however you like
            }
            else //if the hashed password didn't match with the loaded password(pInfo[playerid][Password])
            {
                //we tell them that they have inserted a wrong password
                ShowPlayerDialog(playerid, dlogin, DIALOG_STYLE_INPUT, "Login", "In order to play, you need to login\nWrong password!", "Login", "Quit");
            }
        }
        case dregister: //register dialog
        {
            if(!response) return Kick(playerid); //if they clicked Quit, we will kick them
            if(strlen(inputtext) < 6) return ShowPlayerDialog(playerid, dregister, DIALOG_STYLE_INPUT, "Register", "In order to play, you need to register.\nYour password must be at least 6 characters long!", "Register", "Quit");
            //strlen checks a lenght of a string. so if player types their password that is lower than 6, we tell them; Your password must be at least 6 characters long!
                new query[300];
            WP_Hash(pInfo[playerid][Password], 129, inputtext); //hashing inputtext
            mysql_format(mysql, query, sizeof(query), "INSERT INTO `players` (`Username`, `Password`, `IP`, `Admin`, `VIP`, `Money`, `PosX` ,`PosY`, `PosZ`) VALUES ('%e', '%s', '%s', 0, 0, 0, 0.0, 0.0, 0.0)", Name[playerid], pInfo[playerid][Password], IP[playerid]);
            //Now let's create a new row and insert player's information in it
            mysql_tquery(mysql, query, "OnAccountRegister", "i", playerid);
            //let's execute the query
        }
    }
    return 1;
}

pawn Code:
forward OnAccountLoad(playerid);
forward OnAccountRegister(playerid);
//let's load player's information
public OnAccountLoad(playerid)
{
    pInfo[playerid][Admin] = cache_get_field_content_int(0, "Admin"); //we're getting a field 4 from row 0. And since it's an integer, we use cache_get_row_int
    pInfo[playerid][VIP] = cache_get_field_content_int(0, "VIP"); //Above
    pInfo[playerid][Money] = cache_get_field_content_int(0, "Money");//Above
    pInfo[playerid][posX] = cache_get_field_content_float(0, "PosX");//Above. Since player's position is a float, we use cache_get_field_content_float
    pInfo[playerid][posY] = cache_get_field_content_float(0, "PosY");//Above
    pInfo[playerid][posZ] = cache_get_field_content_float(0, "PosZ");//Above
   
    GivePlayerMoney(playerid, pInfo[playerid][Money]);//Let's set their money
    //For player's position, set it once they spawn(OnPlayerSpawn)
    SendClientMessage(playerid, -1, "Successfully logged in"); //tell them that they have successfully logged in
    return 1;
}

public OnAccountRegister(playerid)
{
    pInfo[playerid][ID] = cache_insert_id(); //loads the ID of the player in the variable once they registered.
    printf("New account registered. ID: %d", pInfo[playerid][ID]); //just for debugging.
    return 1;
}

pawn Code:
public OnPlayerDisconnect(playerid, reason)
{
    new query[128], Float:pos[3]; //query[128] is for formatting our query and Float:pos[3] is for getting and saving player's position
    GetPlayerPos(playerid, pos[0], pos[1], pos[2]); //let's get player's position when they leave your server
    mysql_format(mysql, query, sizeof(query), "UPDATE `players` SET `Admin`=%d, `VIP`=%d, `Money`=%d, `posX`=%f, `posY`=%f, `posZ`=%f WHERE `ID`=%d",\
    pInfo[playerid][Admin], pInfo[playerid][VIP], pInfo[playerid][Money], pos[0], pos[1], pos[2], pInfo[playerid][ID]);
    //We update the table(`players`) by getting player's admin level, vip level, money, and positions and save them in the database
    mysql_tquery(mysql, query, "", "");
    //let's execute the query.
    return 1;
}

pawn Code:
public OnPlayerSpawn(playerid)
{
    SetPlayerPos(playerid, pInfo[playerid][posX], pInfo[playerid][posZ], pInfo[playerid][posZ]);
    //Set player's position to the last saved position.
    return 1;
}

Thank you for reading my tutorial. If there's a mistake somewhere, please let me know.
NOTE: If you're having a problem, check your mysql logs.
Thanks to;
BlueG - MySQL plugin
AndreT - For his tutorial
Y_Less - Whirlpool plugin
ReneG - For his mysql gamemode. I learned everything from his script
__________________
Help me, please /a chat

Last edited by newbienoob; 25/03/2014 at 05:14 PM. Reason: UPDATED
newbienoob is offline   Reply With Quote
Old 05/01/2014, 05:46 AM   #2
SyntaxQ
Big Clucker
 
SyntaxQ's Avatar
 
Join Date: Jan 2014
Posts: 190
Reputation: 36
Default Re: MySQL Registration System [Threaded Queries(R33+) + Whirlpool]

Nice tutorial.
SyntaxQ is offline   Reply With Quote
Old 05/01/2014, 07:19 AM   #3
Ryan_Bowe
Huge Clucker
 
Join Date: Jul 2012
Location: United States
Posts: 217
Reputation: 16
Default Re: MySQL Registration System [Threaded Queries(R33+) + Whirlpool]

Another tutorial from the troll.

Last edited by Ryan_Bowe; 21/02/2015 at 01:56 AM.
Ryan_Bowe is offline   Reply With Quote
Old 05/01/2014, 07:27 AM   #4
Lordzy
High-roller
 
Lordzy's Avatar
 
Join Date: Mar 2012
Location: NetherRealm
Posts: 2,710
Reputation: 1176
Default Re: MySQL Registration System [Threaded Queries(R33+) + Whirlpool]

Nice, well explained. But in case if there's too much fields, it's better to use functions like cache_get_field_content, cache_get_field_content_int, cache_get_field_content_float, etc. So that the user who uses won't get confused in knowing the field ID.
__________________
Currently inactive - I don't play at any SA-MP servers nor work on anything in PAWN for now. The projects that I've done so far in PAWN, which requires updates will be taking some time.
Lordzy is offline   Reply With Quote
Old 05/01/2014, 07:30 AM   #5
iZN
High-roller
 
Join Date: Jun 2010
Posts: 2,400
Reputation: 569
Default Re: MySQL Registration System [Threaded Queries(R33+) + Whirlpool]

For update query, shouldn't we use this (?):

pawn Code:
mysql_format(mysql, query, sizeof(query), "UPDATE `players` SET `Admin`=%d, `VIP`=%d, `Money`=%d, `posX`=%f, `posY`=%f, `posZ`=%f WHERE `ID`=%d",\
    pInfo[playerid][Admin], pInfo[playerid][VIP], pInfo[playerid][Money], pos[0], pos[1], pos[2], pInfo[playerid][ID]);
mysql_query(mysql, query, false); // use_cache bool is not enabled, that means un-cached query.

/* native mysql_query(conhandle, query[], bool:use_cache = true); */

Also, you are escaping player name, why don't you escape password, lol?
__________________
iZN is offline   Reply With Quote
Old 05/01/2014, 09:51 AM   #6
newbienoob
High-roller
 
newbienoob's Avatar
 
Join Date: Jan 2012
Location: Follow the moaning coming from your parents' bedroom
Posts: 1,534
Reputation: 265
Default Re: MySQL Registration System [Threaded Queries(R33+) + Whirlpool]

mysql_query and mysql_tquery are 2 different things. Also we're not escaping password because we're using whirlpool which hashes the password. So the password would be like;
Code:
B97DE512E91E3828B40D2B0FDCE9CEB3C4A71F9BEA8D88E75C4FA854DF36725FD2B52EB6544EDCACD6F8BEDDFEA403CB55AE31F03AD62A5EF54E42EE82C3FB35
But it's up to you to escape it or not.
__________________
Help me, please /a chat
newbienoob is offline   Reply With Quote
Old 05/01/2014, 10:34 AM   #7
dusk
High-roller
 
dusk's Avatar
 
Join Date: Jul 2008
Posts: 1,113
Reputation: 46
Default Re: MySQL Registration System [Threaded Queries(R33+) + Whirlpool]

Perfect! It has eveything. From setting up XAMP to the scrip!
dusk is offline   Reply With Quote
Old 06/01/2014, 07:08 AM   #8
]Rafaellos[
Gangsta
 
Join Date: Feb 2012
Location: Cyprus
Posts: 731
Reputation: 48
Default Re: MySQL Registration System [Threaded Queries(R33+) + Whirlpool]

How to get string for the database?

For int is cache_get_row_int(0, *);. For string?

Thanks for the tutorial.
__________________
]Rafaellos[ is offline   Reply With Quote
Old 06/01/2014, 07:20 AM   #9
iZN
High-roller
 
Join Date: Jun 2010
Posts: 2,400
Reputation: 569
Default Re: MySQL Registration System [Threaded Queries(R33+) + Whirlpool]

Quote:
Originally Posted by ]Rafaellos[ View Post
How to get string for the database?

For int is cache_get_row_int(0, *);. For string?

Thanks for the tutorial.
http://wiki.sa-mp.com/wiki/MySQL/R33#cache_get_row
__________________
iZN is offline   Reply With Quote
Old 06/01/2014, 10:10 AM   #10
Vince
Spam Machine
 
Vince's Avatar
 
Join Date: Sep 2007
Location: Belgium
Posts: 10,103
Reputation: 2655
Default Re: MySQL Registration System [Threaded Queries(R33+) + Whirlpool]

This looks good, just a couple remarks. It is common practice to write definitions in UPPERCASE and all other variables (ID, VIP) in lowercase or CamelCase. The other thing; I don't really consider it useful creating tables from within the script. This will most likely only ever be executed once in the lifetime of the server. Lastly, only select the fields you will actually need instead of using the '*' selector. In you account check function, for example, you only use the password.
__________________
Vince is offline   Reply With Quote
Reply

Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Baffled - mySQL Vehicle Loading (threaded queries) Moglizorz. Server Support 0 18/05/2013 01:14 AM
Comparing password [MySQL Threaded queries] emokidx Scripting Help 3 02/04/2013 12:07 PM
MySQL Threaded Queries CONTROLA Scripting Help 4 25/11/2012 03:02 PM
Threaded Queries - BlueG's MYSQL Plugin R7 Ballu Miaa Scripting Help 3 05/11/2012 05:36 PM
[Help] BlueG's MySQL Plugin R7 - Threaded Queries Maxips2 Scripting Help 4 19/10/2012 09:35 AM


All times are GMT. The time now is 10:52 AM.


Powered by vBulletin® Version 3.8.6
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.