[Tutorial] Easy MySQL Register/Login system!

[Tutorial] Easy MySQL Register/Login system! - Printable Version

+- SA-MP Forums Archive (https://sampforum.blast.hk)
+-- Forum: SA-MP Scripting and Plugins (https://sampforum.blast.hk/forumdisplay.php?fid=8)
+--- Forum: Scripting Help (https://sampforum.blast.hk/forumdisplay.php?fid=12)
+---- Forum: Tutorials (https://sampforum.blast.hk/forumdisplay.php?fid=70)
+---- Thread: [Tutorial] Easy MySQL Register/Login system! (/showthread.php?tid=160971)

Pages: 1 2 3

Easy MySQL Register/Login system! (Includes every single step!) - WackoX - 18.07.2010

Introduction

Introduction

Over the past few years people have been using dini or ffile to create a user register/login system.
I find this very idle, slow, and difficult, and i think a lot of 'new-scripters' feel the same way as i do.

In this tutorial i will show an easy and free example of a MySQL Register/Login system, but you'll have to modify it yourself since i don't know what variables you're going to use in your gamemode.

And yes i know, i thought there was also another MySQL tutorial on the forums, but i think i can make it easyer for you guys.
If you do not agree, pick the one you like, you don't have to do it on my way.

Knowledge

Before you start doing some MySQL scripting inside your gamemode/filterscript, you'll need to have some knowledge about MySQL coding, here a few tips:

int(length) = Can be used for decimals or an integers (1337).
varchar(length) = Can be used for strings (leet), but also for floats (13.37).

Optimization

When you create a gamemode/filterscript, and certainly when based on MySQL, you need to have a good optimized script, otherwise you're gonna run out of RAM or CPU.

I recommend reading ****** topic first: https://sampforum.blast.hk/showthread.php?tid=57018
When you don't have time for this, remember always, make the strings as short as you can!
(Example: A player name doesn't have to be 256 characters, 24 is the maximum in SA:MP)

Setting Everything Up

Plugins and Includes
Besides from just the scripting, you're gonna need some Plugins and Includes for this.
I recommend using G-sTyLeZzZ's: https://sampforum.blast.hk/showthread.php?tid=56564

When you're done dowloading the right parts for your hosting, put the files in the right folders.

(Linux)
File "mysql.inc" >> Folder "Pawno/Includes"
File "mysql.so" >> Folder "Plugins" (Create if not exist)

(Windows)
File "mysql.inc" >> Folder "Pawno/Includes"
File "mysql.dll" >> Folder "Plugins" (Create if not exist)

Add this in your server.cfg:
(Linux) plugins mysql.so
(Windows) plugins mysql.dll

Creating a MySQL Database

Make sure your host has a local MySQL server running, with "PhpMyAdmin" access to it.
(Do NOT use free ones, because most of the time it doesn't work or it is way too slow!)

Go to your PhpMyAdmin site (http://YOUR_HOST_IP_HERE/PhpMyAdmin/) and login.
Now you see something like this:

Enter a new, simple, short name for your database under the text "Create new database", and press "Create".
If everything went alright you will get a notification with the text "Database NAME_HERE has been created.",
now you're inside your MySQL database and left your MySQL homepage.

Now, you want to create a new Table called "Users", now put that in, and the number of fields doesn't matter, you can always change it later,
but it has to be the same amout of variables you're gonna use (for name, password, admin level, etc).

Now, you're gonna see something like this (I've already put those fields in for example)

Like i already said in the topics "Knowledge" and "Optimization", make those 'Length/Values' as short as posible, and use the right 'Types'.
Note: All those field names (Name, Password, Admin, Money) doesn't have to be the same name as the variables inside your Gamemode,
but i recommend it since it's very useful.

When you're done press 'Save', or if you want more fields press 'Go', and leave a field blank when you don't need that one.
Now you will see something like this:

When there are players registered, you can use the button 'Browse', but since there aren't one at the moment, you can't use it.

Creating The Script

Now when you're done create the Database, we're gonna move over to your Gamemode, open that one now.

Add this include:

Код:

#include <a_mysql>

Now add this at the very beginning of your Gamemode (after all the includes).
Put in all the information you got from your host, the IP, the username to login with the password, and the name of the database you just created.
NOTE: Not the name of the Tabel (in this case: Users), but the name of the Database (in this case: Test)!!

Код:

#define MYSQL_HOST	"ip"
#define MYSQL_USER	"username"
#define MYSQL_DB	"database"
#define MYSQL_PASS 	"password"

Код:

#undef MAX_PLAYERS
#define MAX_PLAYERS 50 // Put this as low as you could!

Код:

enum pEnum
{
	Name[MAX_PLAYER_NAME],
	Password[32],
	Admin,
	Money,
};
new UserStats[MAX_PLAYERS][pEnum];

new AccountExists[MAX_PLAYERS];
new PlayerLogged[MAX_PLAYERS];

Now, we're gonna put some large parts inside our gamemode, just put it somewhere it doesn't matter, but i recommend to put it all down.

Код:

ConnectMySQL()
{
	if(mysql_connect(MYSQL_HOST, MYSQL_USER, MYSQL_DB, MYSQL_PASS))
	    print("[MySQL] Connection to the MySQL Database was successfully!");

	else
	    print("[MySQL] Could not connect to the MySQL Database!");
}

CheckMySQL()
{
	if(mysql_ping() == -1)
		mysql_connect(MYSQL_HOST, MYSQL_USER, MYSQL_DB, MYSQL_PASS);
}

Код:

CheckAccountExists(account[])
{
	new string[128];
    	format(string, sizeof(string), "SELECT * FROM Users WHERE Name = '%s'", account);
    	mysql_query(string);
    
	mysql_store_result();

	new value;
	value = mysql_num_rows();
	mysql_free_result();
	return value;
}

And you're also gonna need the 'Explode' function, since the 'Split' function is mutch slower:

(Created by Westie)

Код:

explode(const sSource[], aExplode[][], const sDelimiter[] = " ", iVertices = sizeof aExplode, iLength = sizeof aExplode[]) // Created by Westie
{
	new
		iNode,
		iPointer,
		iPrevious = -1,
		iDelimiter = strlen(sDelimiter);

	while(iNode < iVertices)
	{
		iPointer = strfind(sSource, sDelimiter, false, iPointer);

		if(iPointer == -1)
		{
			strmid(aExplode[iNode], sSource, iPrevious, strlen(sSource), iLength);
			break;
		}
		else
		{
			strmid(aExplode[iNode], sSource, iPrevious, iPointer, iLength);
		}

		iPrevious = (iPointer += iDelimiter);
		++iNode;
	}
	return iPrevious;
}

Now put in the Register/Login/Save parts, you'll have to edit them to your varibles!

Код:

RegisterPlayer(playerid, password[])
{
	if(AccountExists[playerid])
		return SendClientMessage(playerid, COLOR_RED, "[ACCOUNT] You're already registered!");

 	if(PlayerLogged[playerid])
		return SendClientMessage(playerid, COLOR_RED, "[ACCOUNT] You're already logged in!");

	if(strlen(password) < 3 || strlen(password) >= 32)
		return SendClientMessage(playerid, COLOR_RED, "[ACCOUNT] Your password is too short or too long!");

	CheckMySQL();

	new string[128];
	format(string, sizeof(string), "INSERT INTO Users (Name,Password) VALUES ('%s','%s')", UserStats[playerid][Name], password);
	mysql_query(string);

    	AccountExists[playerid] = 1;
	SendClientMessage(playerid, COLOR_YELLOW, "[ACCOUNT] Your account has been created, please login now!");

	LoginPlayer(playerid, password);
	return 1;
}

LoginPlayer(playerid, password[])
{
	if(!AccountExists[playerid])
		return SendClientMessage(playerid, COLOR_RED, "[ACCOUNT] You're not registered!");

	if(PlayerLogged[playerid])
	    	return SendClientMessage(playerid, COLOR_RED, "[ACCOUNT] You're already logged in!");

 	if(strlen(password) < 3 || strlen(password) >= 32)
	    	return SendClientMessage(playerid, COLOR_RED, "[ACCOUNT] Your password is too short or too long!");

	CheckMySQL();

    	new string[128];
	format(string, sizeof(string), "SELECT * FROM Users WHERE Name = '%s' AND Password = '%s'", UserStats[playerid][Name], password);
	mysql_query(string);
	mysql_store_result();

	if(!mysql_num_rows())
		return SendClientMessage(playerid, COLOR_RED, "[ACCOUNT] Incorrect password!");

	new row[128]; // The length of 1 'row' total.
	new field[4][32]; // [4] = Amount of fields, [24] = Max length of the bigest field. 

	mysql_fetch_row_format(row, "|");
	explode(row, field, "|");
	mysql_free_result();

	// The field starts here with 1, because the field 'Name' = 0, and we already have the name in a variable.
	format(UserStats[playerid][Password], 32, "%s", field[1]);
 	UserStats[playerid][Admin] = strval(field[2]);
 	UserStats[playerid][Money] = strval(field[3]);

 	GivePlayerMoney(playerid, UserStats[playerid][Money]);
 	
	
	format(string, sizeof(string), "[ACCOUNT] Welcome back %s, you are now logged in!", UserStats[playerid][Name]);
    	SendClientMessage(playerid, COLOR_YELLOW, string);

    	PlayerLogged[playerid] = 1;
    	return 1;
}

SavePlayer(playerid)
{
	if(!PlayerLogged[playerid])
		return 0;

    	UserStats[playerid][Money] = GetPlayerMoney(playerid);
    

	CheckMySQL();
    
    	new string[256];
    	format(string, sizeof(string), "UPDATE Users SET Password='%s',Admin='%d',Money='%d' WHERE Name='%s'", UserStats[playerid][Password], UserStats[playerid][Admin], UserStats[playerid][Money], UserStats[playerid][Name]);
    	mysql_query(string);
    	return 1;
}

Now we're gonna update your OnGameModeInit, OnPlayerConnect and OnPlayerDisconnect:

Код:

public OnGameModeInit()
{
	ConnectMySQL();
	return 1;
}

public OnPlayerConnect(playerid)
{
	GetPlayerName(playerid, UserStats[playerid][Name], MAX_PLAYER_NAME);
	
	if(CheckAccountExists(UserStats[playerid][Name])) AccountExists[playerid] = 1;
	else AccountExists[playerid] = 0;
	return 1;
}

public OnPlayerDisconnect(playerid, reason)
{
	SavePlayer(playerid);

	UserStats[playerid][Admin] = 0;
	UserStats[playerid][Money] = 0;
	return 1;
}

And now the last step, you need to implement the commands.
You can use strcmp+strtok wich is very slow and unuseful, or you can use zcmd or dcmd.

In this case we're going to use dcmd, but you can use whatever you like:

Код:

public OnPlayerCommandText(playerid, cmdtext[])
{
	dcmd(register, 8, cmdtext);
	dcmd(login, 5, cmdtext);
	return 0;
}

dcmd_register(playerid, params[])
{
	RegisterPlayer(playerid, params);
	return 1;
}

dcmd_login(playerid, params[])
{
	LoginPlayer(playerid, params);
	return 1;
}

You're done!
Congratulations with your very first own scripted MySQL based Gamemode!
Put on your server, and you'll see it will connect to the MySQL database and everything works fine!

Re: Easy MySQL Register/Login system! - Calgon - 18.07.2010

You've forgotten to add credits for the explode function, created by Westie.

Re: Easy MySQL Register/Login system! - WackoX - 18.07.2010

Quote:

Originally Posted by Calgon

You've forgotten to add credits for the explode function, created by Westie.

Thank you they've been edited! do you also know how to make those screenshots smaller in the [img] code?
It stretches the forum now at my screen.

Re: Easy MySQL Register/Login system! - Kar - 18.07.2010

wow Nice imma use this in the future

Re: Easy MySQL Register/Login system! - Lewwy - 18.07.2010

No idea why but I did what you said, haven't edited a single piece from your tutorial and when I connect and register, all is well. Once I leave however, everything but my password saves, it leaves it blank. Why is this?

Re: Easy MySQL Register/Login system! - WackoX - 18.07.2010

Quote:

Originally Posted by Lewwy

No idea why but I did what you said, haven't edited a single piece from your tutorial and when I connect and register, all is well. Once I leave however, everything but my password saves, it leaves it blank. Why is this?

Yes i see it now, i forgot to add the Connection part in the script :P
Please look again at the OnGameModeInit part, and put that one also in your script.

Having more problems? please post them i'd like to help you.

Re: Easy MySQL Register/Login system! - Lewwy - 18.07.2010

I had already done that, everything else loads and saves fine, except my password. It leaves the field blank after I quit or GMX.

Re: Easy MySQL Register/Login system! - WackoX - 18.07.2010

Quote:

Originally Posted by Lewwy

I had already done that, everything else loads and saves fine, except my password. It leaves the field blank after I quit or GMX.

Fixed, please look at the LoginPlayer part again.
When you load in a string from MySQL, you'll need to use format or strcat.

Re: Easy MySQL Register/Login system! - [HiC]TheKiller - 18.07.2010

A few things that I suggest:

1. Instead of using a enum just use PVars for all of them.
2. Use Sscanf
3. Use dialogs
4. Please explain some of it using comments
5. Did you just copy and paste this from a script?

Re: Easy MySQL Register/Login system! - Lewwy - 18.07.2010

That line you posted in your first post doesn't work correctly for me.

Use this:

pawn Code:

format(UserStats[playerid][Password], 32, "%s", field[1]);

Re: Easy MySQL Register/Login system! - WackoX - 18.07.2010

Quote:

Originally Posted by [HiC]TheKiller

A few things that I suggest:

1. Instead of using a enum just use PVars for all of them.
2. Use Sscanf
3. Use dialogs
4. Please explain some of it using comments
5. Did you just copy and paste this from a script?

1. People can change it to whatever they want, and BTW if a standard value has to be like 255 for a house system or something, it reset's it automatically to 0 when a player disconnect, result: bugged house system.

And another way, i don't like the way of pVars:

* They reset automatically when i DON'T want to.
* You cannot use things like Text3D and other stuff in them, result: 2 different ways of saving for 1 system.
* They use less RAM, but take up more CPU.

2. Why? i don't use any parameters at the dcmd part, there's no need for.
3. Like i already said, people can modify whatever they want, and the title says EASY System.
4.

5. Yes, from my own created script, but edited it for the tutorial.

Re: Easy MySQL Register/Login system! - [HiC]TheKiller - 19.07.2010

Quote:

Originally Posted by WackoX

5. Yes, from my own created script, but edited it for the tutorial.

Well, to put it this way, all I see is just code. You haven't explained any part of the script in comments hence it not really being much of a tutorial.

Re: Easy MySQL Register/Login system! - Scenario - 19.07.2010

Yeah, I was confused about what was happening in this tutorial. It wasn't explained well, it seems like a bunch of images, rather than explanations. Once MySQL is setup, I am able to create stuff, but I always have a hard time setting it up from scratch, so I think its time to learn.

Re: Easy MySQL Register/Login system! - Яσскѕтая - 21.07.2010

For some reason I was already registered and when I browse the structures in PHPMyAdmin theres nothing there...

EDIT: My MySQL database was a little funny, but now when I try to execute it I get a error:

Code:

CREATE TABLE(
  `Name` varchar(24) collate utf8_unicode_ci NOT NULL,
  `Password` varchar(32) collate utf8_unicode_ci NOT NULL,
  `Admin` int(1) NOT NULL,
  `Money` int(8) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

EDIT2: Forgot to put the Users on create table, but it still is saying im registered.

Re: Easy MySQL Register/Login system! - Gforcez - 21.07.2010

Thanks Wackox! =D

Re: Easy MySQL Register/Login system! - Luis_Leone - 31.07.2010

I am getting these Errors,

Code:

C:\Users\BooNii3\Geramia RP\LS-RP\gamemodes\LS-RP.pwn(73) : error 021: symbol already defined: "main"
C:\Users\BooNii3\Geramia RP\LS-RP\gamemodes\LS-RP.pwn(82) : error 021: symbol already defined: "OnGameModeInit"
C:\Users\BooNii3\Geramia RP\LS-RP\gamemodes\LS-RP.pwn(91) : error 021: symbol already defined: "OnGameModeExit"
C:\Users\BooNii3\Geramia RP\LS-RP\gamemodes\LS-RP.pwn(96) : error 021: symbol already defined: "OnPlayerRequestClass"
C:\Users\BooNii3\Geramia RP\LS-RP\gamemodes\LS-RP.pwn(147) : error 021: symbol already defined: "OnPlayerCommandText"
C:\Users\BooNii3\Geramia RP\LS-RP\gamemodes\LS-RP.pwn(164) : error 010: invalid function or declaration

Re: Easy MySQL Register/Login system! - WackoX - 02.08.2010

Quote:

Originally Posted by Luis_Leone

I am getting these Errors,

Code:

C:\Users\BooNii3\Geramia RP\LS-RP\gamemodes\LS-RP.pwn(73) : error 021: symbol already defined: "main"
C:\Users\BooNii3\Geramia RP\LS-RP\gamemodes\LS-RP.pwn(82) : error 021: symbol already defined: "OnGameModeInit"
C:\Users\BooNii3\Geramia RP\LS-RP\gamemodes\LS-RP.pwn(91) : error 021: symbol already defined: "OnGameModeExit"
C:\Users\BooNii3\Geramia RP\LS-RP\gamemodes\LS-RP.pwn(96) : error 021: symbol already defined: "OnPlayerRequestClass"
C:\Users\BooNii3\Geramia RP\LS-RP\gamemodes\LS-RP.pwn(147) : error 021: symbol already defined: "OnPlayerCommandText"
C:\Users\BooNii3\Geramia RP\LS-RP\gamemodes\LS-RP.pwn(164) : error 010: invalid function or declaration

You only need to add those functions if they do not exist, if they do then 'add' the code inside of it.

Re: Easy MySQL Register/Login system! - RealLifeRP - 03.08.2010

nevermind

Re: Easy MySQL Register/Login system! - Vince - 03.08.2010

I'd rather add an SQL ID in the database as well. This should be the first value in the table and should be set as Primary Key and Auto-increment.

You can then retrieve this value when someone registers (mysql_insert_id) and assign it to a variable. You can then use this value to execute future queries on the database without retrieving the player's name every time.

And you can use sscanf instead of explode to split rows into the data you need, as TheKiller already suggested.

Re: Easy MySQL Register/Login system! - WackoX - 03.08.2010

Quote:

Originally Posted by Vince

I'd rather add an SQL ID in the database as well. This should be the first value in the table and should be set as Primary Key and Auto-increment.

You can then retrieve this value when someone registers (mysql_insert_id) and assign it to a variable. You can then use this value to execute future queries on the database without retrieving the player's name every time.

And you can use sscanf instead of explode to split rows into the data you need, as TheKiller already suggested.

Maybe that's your way of scripting, but it makes no sence, why add a variable while you can just use he's name?
it doesn't slows it down or something, it works fine, and it's more handy.
And explode is way faster then sscanf for doing MySQL stuff like this.

I don't know why, but since i started this topic people are only whining about the 'problems' there are in this script a.k.a. not scripted on the way they should do.

There are no problems with this script, this is the fastest way to do it, the script has a clean optimized code, and i do explain a lot.
And this is the only MySQL tutorial that actually shows (with screens) how to set up a database!