SA-MP Forums Archive
SQLite Login/Register problem - 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)
+--- Thread: SQLite Login/Register problem (/showthread.php?tid=630361)


SQLite Login/Register problem - Flamehaze7 - 13.03.2017

I can't really understand where this is wrong.
Basically everytime someone register the script doesn't write inside the database and so it doesn't save the stats.

OnPlayerConnect
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.

    gPlayerLogged[playerid] = 0;

    // 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 (ID: %d){FFFFFF} to the server, You're already 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;
}
OnPlayerDisconnect
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.
	new Float: X, Float: Y, Float: Z;
	GetPlayerPos(playerid,X,Y,Z);
	PlayerInfo[playerid][Pos_x] = X;
	PlayerInfo[playerid][Pos_y] = Y;
	PlayerInfo[playerid][Pos_z] = Z;
	if(gPlayerLogged[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 MONEY = '%d', ADMIN = '%d', LEVEL = '%d', EXP = '%d', KILLS = '%d', DEATHS = '%d', CASHBOX = '%d', HOTWIRE = '%d', DEATHMATCH = '%d', RACE = '%d', PEN = '%d', GUN1 = '%d', GUN2 = '%d', GUN3 = '%d', AMMO1 = '%d', AMMO2 = '%d', AMMO3, POSX = '%d', POSY = '%d', POSZ = '%d', SKIN = '%d' = '%d' WHERE `NAME` = '%s' COLLATE NOCASE",
																						GetPlayerMoney(playerid),
																						PlayerInfo[playerid][Admin],
																						PlayerInfo[playerid][Exp],
																						PlayerInfo[playerid][Kills],
																						PlayerInfo[playerid][Deaths],
																						PlayerInfo[playerid][Cashbox],
																						PlayerInfo[playerid][Hotwire],
																						PlayerInfo[playerid][Deathmatch],
																						PlayerInfo[playerid][Race],
																						PlayerInfo[playerid][PEN],
																						PlayerInfo[playerid][Gun1],
																						PlayerInfo[playerid][Gun2],
																						PlayerInfo[playerid][Gun3],
																						PlayerInfo[playerid][Ammo1],
																						PlayerInfo[playerid][Ammo2],
																						PlayerInfo[playerid][Ammo3],
																						PlayerInfo[playerid][Pos_x],
																						PlayerInfo[playerid][Pos_y],
																						PlayerInfo[playerid][Pos_z],
																						GetPlayerSkin(playerid),
																						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.
	    gPlayerLogged[playerid] = 0;
	    print("The player has successfully saved his stats");
    }
	return 1;
}
OnDialogResponse
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
			    
			    db_get_field_assoc(Result, "MONEY", Field, 30);
   				GivePlayerMoney(playerid,strval(Field));
			    
			    db_get_field_assoc(Result, "ADMIN", Field, 30);
   				PlayerInfo[playerid][Admin] = strval(Field);
			    
				db_get_field_assoc(Result, "LEVEL", Field, 30);
   				PlayerInfo[playerid][Level] = strval(Field);
   				
   				db_get_field_assoc(Result, "EXP", Field, 30);
   				PlayerInfo[playerid][Exp] = strval(Field);
   				
   				db_get_field_assoc(Result, "KILLS", Field, 30);
   				PlayerInfo[playerid][Kills] = strval(Field);
   				
   				db_get_field_assoc(Result, "DEATHS", Field, 30);
   				PlayerInfo[playerid][Deaths] = strval(Field);
   				
   				db_get_field_assoc(Result, "CASHBOX", Field, 30);
   				PlayerInfo[playerid][Cashbox] = strval(Field);
   				
   				db_get_field_assoc(Result, "HOTWIRE", Field, 30);
   				PlayerInfo[playerid][Hotwire] = strval(Field);
   				
   				db_get_field_assoc(Result, "DEATHMATCH", Field, 30);
   				PlayerInfo[playerid][Deathmatch] = strval(Field);
   				
   				db_get_field_assoc(Result, "RACE", Field, 30);
   				PlayerInfo[playerid][Race] = strval(Field);
   				
   				db_get_field_assoc(Result, "PEN", Field, 30);
   				PlayerInfo[playerid][PEN] = strval(Field);
   				
   				db_get_field_assoc(Result, "GUN1", Field, 30);
   				db_get_field_assoc(Result, "AMMO1", Field, 30);
   				GivePlayerWeapon(playerid,strval(Field),strval(Field));
   				
   				db_get_field_assoc(Result, "GUN2", Field, 30);
   				db_get_field_assoc(Result, "AMMO2", Field, 30);
   				GivePlayerWeapon(playerid,strval(Field),strval(Field));
   				
   				db_get_field_assoc(Result, "GUN3", Field, 30);
   				db_get_field_assoc(Result, "AMMO1", Field, 30);
   				GivePlayerWeapon(playerid,strval(Field),strval(Field));
   				
                db_get_field_assoc(Result, "HEALTH", Field, 30);
   				SetPlayerHealth(playerid, strval(Field));
   				
   				db_get_field_assoc(Result, "ARMOR", Field, 30);
   				SetPlayerArmour(playerid, strval(Field));
   				
   				db_get_field_assoc(Result, "POSX", Field, 30);
   				db_get_field_assoc(Result, "POSY", Field, 30);
   				db_get_field_assoc(Result, "POSZ", Field, 30);
   				SetPlayerPos(playerid,strval(Field),strval(Field),strval(Field));
   				
   				db_get_field_assoc(Result, "SKIN", Field, 30);
   				SetPlayerSkin(playerid,strval(Field));
   				
   				// Log in the player
    			gPlayerLogged[playerid] = 1;
    			// 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`, `MONEY`, `IP`, `ADMIN`, `LEVEL`, `EXP`, `KILLS`, `DEATHS`, `CASHBOX`, `HOTWIRE`, `DEATHMATCH`, `RACE`, `PEN`, `GUN1`, `GUN2`, `GUN3`, `AMMO1`, `AMMO2`, `AMMO3`, `HEALTH`, `ARMOR`, `POSX`, `POSY`, `POSZ`, `SKIN`) VALUES('%s','%s','0','%s','0','1','0','0','0','0','0','0','0','0','0','0','0','0','0','0','0','0','0','0','0')", DB_Escape(name), DB_Escape(inputtext), DB_Escape(ip));
				// Querying the formatted Query ^
				db_query(Database, Query);
 				// Log in the player
				gPlayerLogged[playerid] = 1;
				print("The player has successfully registered");
				GivePlayerMoney(playerid, 500); // Give Player the money.
				// Reset score.
				SetPlayerScore(playerid, 1);
				// 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;
}


Re: SQLite Login/Register problem - SyS - 13.03.2017

increase the size of variable query in dusconnect callback

Re: SQLite Login/Register problem - Flamehaze7 - 13.03.2017

Quote:
Originally Posted by Sreyas
View Post
increase the size of variable query in dusconnect callback
Tried but it still doesn't work, the problem is that when you register it doesn't write inside the Database

Re: SQLite Login/Register problem - SyS - 13.03.2017

enable db logging and db log queries and post what printing on console

Re: SQLite Login/Register problem - Flamehaze7 - 13.03.2017

Quote:
Originally Posted by Sreyas
View Post
enable db logging and db log queries and post what printing on console
Sorry but i'm trying to learn SQLite so i don't know much, could you explain me how to enable db logging and db log queries?

Re: SQLite Login/Register problem - X337 - 13.03.2017

You can enable them by adding/editing these options in server.cfg.
Code:
db_logging = 1
db_log_queries = 1
https://sampwiki.blast.hk/wiki/Server.cfg

Edit:
I see an error in your UPDATE query.
Code:
UPDATE `USERS` SET MONEY = '%d', ADMIN = '%d', LEVEL = '%d', EXP = '%d', KILLS = '%d', DEATHS = '%d', CASHBOX = '%d', HOTWIRE = '%d', DEATHMATCH = '%d', RACE = '%d', PEN = '%d', GUN1 = '%d', GUN2 = '%d', GUN3 = '%d', AMMO1 = '%d', AMMO2 = '%d', AMMO3, POSX = '%d', POSY = '%d', POSZ = '%d', SKIN = '%d' = '%d' WHERE `NAME` = '%s' COLLATE NOCASE
Which should be
Code:
UPDATE `USERS` SET MONEY = '%d', ADMIN = '%d', LEVEL = '%d', EXP = '%d', KILLS = '%d', DEATHS = '%d', CASHBOX = '%d', HOTWIRE = '%d', DEATHMATCH = '%d', RACE = '%d', PEN = '%d', GUN1 = '%d', GUN2 = '%d', GUN3 = '%d', AMMO1 = '%d', AMMO2 = '%d', AMMO3, POSX = '%d', POSY = '%d', POSZ = '%d', SKIN = '%d' WHERE `NAME` = '%s' COLLATE NOCASE
and also, it will be much easier if you set default value for columns inside USERS table.

Re: SQLite Login/Register problem - Flamehaze7 - 13.03.2017

Code:
[11:42:26] 
[11:42:26] Filterscripts
[11:42:26] ---------------
[11:42:26]   Loaded 0 filterscripts.

[11:42:26] [db_log_queries]: CREATE TABLE IF NOT EXISTS `USERS` (`NAME`, `PASSWORD`, `MONEY`, `IP`, `ADMIN`, `LEVEL`, `EXP`, `KILLS`, `DEATHS`, `CASHBOX`, `HOTWIRE`, `DEATHMATCH`, `RACE`, `PEN`, `GUN1`, `GUN2`, `GUN3`, `AMMO1`, `AMMO2`, `AMMO3`, `HEALTH`, `ARMOR`, `POSX`, `POSY`, `POSZ`, `SKIN`)
[11:42:26] 
----------------------------------
[11:42:26]             Public Enemy LS: Remake
[11:42:26]                        RE-Coded by Flamehaze
[11:42:26] ----------------------------------

[11:42:26] Number of vehicle models: 14
[11:42:48] [connection] 127.0.0.1:56300 requests connection cookie.
[11:42:49] [connection] incoming connection: 127.0.0.1:56300 id: 0
[11:42:50] [join] [EvO]Flamehaze_ has joined the server (0:127.0.0.1)
[11:42:50] [db_log_queries]: SELECT `NAME` FROM `USERS` WHERE `NAME` = '[EvO]Flamehaze_' COLLATE NOCASE
[11:43:07] [db_log_queries]: INSERT INTO `USERS` (`NAME`, `PASSWORD`, `MONEY`, `IP`, `ADMIN`, `LEVEL`, `EXP`, `KILLS`, `DEATHS`, `CASHBOX`, `HOTWIRE`, `DEATHMATCH`, `RACE`, `PEN`, `GUN1`, `GUN2`, `GUN3`, `AMMO1`, `AMMO2`, `AMMO3`, `HEALTH`, `ARMOR`, `POSX`, `POSY`, `POSZ`, `SKIN`) VALUES('[EvO]Flamehaze_','pswtest','0','127.0.0.1','0','1','0','0','0','0','0','0','0','0','0','0','0','0','0','0','0','0','0','0','0')
[11:43:07] [Warning] db_query: Query failed: 25 values for 26 columns
[11:43:07] The player has successfully registered
[11:43:32] [db_log_queries]: UPDATE `USERS` SET MONEY = '500', ADMIN = '0', LEVEL = '0', EXP = '0', KILLS = '0', DEATHS = '0', CASHBOX = '0', HOTWIRE = '0', DEATHMATCH = '0', RACE = '0', PEN = '0', GUN1 = '0', GUN2 = '0', GUN3 = '0', AMMO1 = '0', AMMO2 = '0', AMMO3, POSX = '1159499948', POSY = '-992965571', POSZ = '1096150080', SKIN = '105' = '91' WHERE `NAME` = '' COLLATE NOCASE
[11:43:32] [Warning] db_query: Query failed: near ",": syntax error
[11:43:32] The player has successfully saved his stats
[11:43:32] [part] [EvO]Flamehaze_ has left the server (0:1)
EDIT: I managed to fix the 26 columns error but then there's this:

Code:
[11:58:20] [db_log_queries]: UPDATE `USERS` SET MONEY = '30500', ADMIN = '0', LEVEL = '0', EXP = '1', KILLS = '0', DEATHS = '0', CASHBOX = '0', HOTWIRE = '0', DEATHMATCH = '0', RACE = '0', PEN = '0', GUN1 = '0', GUN2 = '0', GUN3 = '0', AMMO1 = '0', AMMO2 = '0', AMMO3, POSX = '1159498234', POSY = '-992965497', POSZ = '1096148473', SKIN = '105' WHERE `NAME` = '[EvO]Flamehaze_' COLLATE NOCASE
[11:58:20] [Warning] db_query: Query failed: near ",": syntax error


Re: SQLite Login/Register problem - w1z4rd - 13.03.2017

Quote:
Originally Posted by Flamehaze7
View Post
EDIT: I managed to fix the 26 columns error but then there's this:

Code:
[11:58:20] [db_log_queries]: UPDATE `USERS` SET MONEY = '30500', ADMIN = '0', LEVEL = '0', EXP = '1', KILLS = '0', DEATHS = '0', CASHBOX = '0', HOTWIRE = '0', DEATHMATCH = '0', RACE = '0', PEN = '0', GUN1 = '0', GUN2 = '0', GUN3 = '0', AMMO1 = '0', AMMO2 = '0', AMMO3, POSX = '1159498234', POSY = '-992965497', POSZ = '1096148473', SKIN = '105' WHERE `NAME` = '[EvO]Flamehaze_' COLLATE NOCASE
[11:58:20] [Warning] db_query: Query failed: near ",": syntax error
Have you checked you update query? In UPDATE you dont have any value set for AMMO3 like others do

Re: SQLite Login/Register problem - Flamehaze7 - 13.03.2017

Quote:
Originally Posted by w1z4rd
View Post
Have you checked you update query? In UPDATE you dont have any value set for AMMO3 like others do
Oh thank you, fixed it now!

Thanks everyone for the help.