PDA

View Full Version : MySQL refuses to save users data


RedWolfX
28/09/2013, 06:20 PM
Hello, I am trying to convert my script in to mysql. But it refuses to save users.
This is my register dialog:


new EscapedText[60],Query[80];
new name[MAX_PLAYER_NAME];
GetPlayerName(playerid, name, sizeof(name));
mysql_real_escape_string(inputtext, EscapedText);
format(Query,sizeof(Query),"INSERT INTO `users` (Username,Password,Score,AdminLevel) VALUES ('%s','%s,'1'0')",GetPName(playerid),EscapedText);
mysql_query(Query);
mysql_store_result();
mysql_free_result();
PlayerInfo[playerid][pSurvivors] = 1;
IsInTut[playerid] = 1;


Using the mysql plugin by Stricken. Why dont the users save? :P

zT KiNgKoNg
28/09/2013, 06:21 PM
You're inserting not updating

RedWolfX
28/09/2013, 06:41 PM
I did a


stock SaveStats(playerid)
{
new Query[128];
format(Query,sizeof(Query),"UPDATE `users` SET `Score` = '%i', `AdminLevel` = '%i' WHERE `Username` = '%s'",
GetPlayerScore(playerid),
GetPName(playerid));
mysql_query(Query);
mysql_free_result();
return 1;
}


And put it in to when a player finishes the tutorial.
Still dosent save.

Sorry for being such a noob but im new to mysql

x96664
28/09/2013, 06:51 PM
Try ths:

new EscapedText[60],Query[80];
new name[MAX_PLAYER_NAME];
GetPlayerName(playerid, name, sizeof(name));
mysql_real_escape_string(inputtext, EscapedText);
format(Query,sizeof(Query),"INSERT INTO `users` (Username,Password,Score,AdminLevel) VALUES ('%s','%s','1','0')",GetPName(playerid),EscapedText);
mysql_query(Query);
mysql_store_result();
mysql_free_result();
PlayerInfo[playerid][pSurvivors] = 1;
IsInTut[playerid] = 1;

And this one

stock SaveStats(playerid)
{
new Query[128];
format(Query,sizeof(Query),"UPDATE `users` SET `Score` = '%i', `AdminLevel` = '%i' WHERE `Username` = '%s'",
GetPlayerScore(playerid),
//what about the AdminLevel ?
PInfo[playerid][pAdminLevel],//change with your defines
GetPName(playerid));
mysql_query(Query);
mysql_free_result();
return 1;
}

RedWolfX
28/09/2013, 07:03 PM
No still dosent work

Patrick
28/09/2013, 07:09 PM
Try these

stock SaveStats(playerid)
{
new Query[128];
format(Query,sizeof(Query),"UPDATE `users` SET `Score`= %i, /*`AdminLevel`= %i*/ WHERE `Username` = '%s'", GetPlayerScore(playerid), GetPName(playerid));
mysql_query(Query);
return 1;
}

First of all you don't need a '(Apostrophe) on an Integer you only need '(Apostrophe) on a string, and second you don't free the result when you're updating it, you only free the result when loading the Player's information, I hope this works!

PS: You forgot to put your Admin level Variable, just remove the /* */ when you added the AdminLevel variable

x96664
28/09/2013, 07:10 PM
Try these

stock SaveStats(playerid)
{
new Query[128];
format(Query,sizeof(Query),"UPDATE `users` SET `Score`= %i, /*`AdminLevel`= %i*/ WHERE `Username` = '%s'", GetPlayerScore(playerid), GetPName(playerid));
mysql_query(Query);
return 1;
}

First of all you don't need a '(Apostrophe) on an Integer you only need '(Apostrophe) on a string, and second you don't free the result when you're updating it, you only free the result when loading the Player's information, I hope this works!

PS: You forgot to put your Admin level Variable, just remove the /* */ when you added the AdminLevel variable

What about the AdminLevel ?

Riddy
28/09/2013, 07:12 PM
Enable the mysql_logs, and give us a log.

Patrick
28/09/2013, 07:14 PM
What about the AdminLevel ?

He forgot to put the AdminLevel variable, to be able to save the Player's admin level.

It should look like this

//If uses Enum
pInfo[playerid][AdminLevel] or PlayerInfo[playerid][AdminLevel]
//or normal variable
AdminLevel[playerid]


If you forgot to add the variable while updating the Player's information could result a Saving Problem

RedWolfX
28/09/2013, 07:19 PM
Still dosent work.

And this is the mysql.log


[Sat Sep 28 22:23:08 2013] Connected (0) to samp01_db @ Localhost via UNIX socket.
[Sat Sep 28 22:23:08 2013] MySQL Server Version 5.5.32-cll.
[Sat Sep 28 22:23:25 2013] Error (0): Failed to exeute query. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''Daryl_Raleigh' at line 1.
[Sat Sep 28 22:23:25 2013] Error (0): Function: mysql_store_result called when no prior successful query executed. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''Daryl_Raleigh' at line 1.
[Sat Sep 28 22:24:24 2013] Error (0): Failed to exeute query. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''Daryl_Raleigh' at line 1.
[Sat Sep 28 22:24:24 2013] Error (0): Function: mysql_store_result called when no prior successful query executed. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''Daryl_Raleigh' at line 1.



Edit:

This all the lines that I have MySQL lines on:



stock SaveStats(playerid)
{
new Query[128];
format(Query,sizeof(Query),"UPDATE `users` SET `Score`= %i, `AdminLevel`= %i WHERE `Username` = '%s'", GetPlayerScore(playerid),PlayerInfo[playerid][pAdmin], GetPName(playerid));
mysql_query(Query);
return 1;
}
stock LoginPlayer(playerid,const password[])
{
new EscapedText[60], Query[80];
new name[MAX_PLAYER_NAME];
GetPlayerName(playerid, name, sizeof(name));
mysql_real_escape_string(password, EscapedText);
format(Query,sizeof(Query),"SELECT * FROM `users` WHERE `Username` = '%s' AND `Password` = '%s'",name,EscapedText);
mysql_query(Query);
mysql_store_result();
if(mysql_num_rows() != 0)
{
SendClientMessage(playerid,COLOR_RED,"You have been logged in!");
LoadStats(playerid);
}
else
{
SendClientMessage(playerid,RED,"Wrong password!");
Kick(playerid);
}
mysql_free_result();
return 1;
}

if(strlen(inputtext) == 0)
{
ShowPlayerDialog(playerid, REGISTER, DIALOG_STYLE_PASSWORD, "Welcome to Island Survival", "{FFFFFF}Welcome to {FF0000}Island Survival:RP\n{FFFFFF}You are about to make an account here!\n\n\n{00FF00}Type in your password you want!", "Register", "Leave");
}
else
{
LoginPlayer(playerid,inputtext);
}

OnPlayerConnect

new Query[80],pNamE[24];
GetPlayerName(playerid,pNamE,24);
format(Query,sizeof(Query),"SELECT `Username` FROM `users` WHERE `Username` = '%s' LIMIT 1;",pNamE);
mysql_query(Query);
mysql_store_result();
if(mysql_num_rows() != 0)
{
ShowPlayerDialog(playerid, LOGIN, DIALOG_STYLE_PASSWORD, "Welcome back!", "Welcome back to Island Survival!\n\nType in your password below to login", "Login", "Leave");
}
else
{
ShowPlayerDialog(playerid, REGISTER, DIALOG_STYLE_PASSWORD, "Welcome to Island Survival", "{FFFFFF}Welcome to {FF0000}Island Survival:RP\n{FFFFFF}You are about to make an account here!\n\n\n{00FF00}Type in your password you want!", "Register", "Leave");
}
mysql_free_result();

enum pInfo
{
Username[23],
Password[24],
pAdmin,
pHelper,
pScore,
pWaterBottle,
pOthers,
pFaction,
pRank,
pLeader,
pSurvivors,
pFishingNet,
pBoar,
pBoarMeat,
pNote,
pRope,
pFruit,
pFishingRod,
pSkin,
pMale,
pFemale,
pPlayTime,
pWater,
pFish,
pHeroin,
pMatches,
pWood,
pBerry,
pWhiskey,
pMask,
pClothes,
pFishDone,
pMute,
pRmute,
pJailed,
pJailTime,
pRespect,
pCoOwner,
pHunter,
pDrugAddict,
pDoctor,
pThief,
pFisher,
pTech,
pGun,
pAmmo,
pAmmo2,
pAmmo3,
pAmmo4,
pAmmo5,
pGun2,
pGun3,
pGun4,
pGun5,
Float:pPos_x,
Float:pPos_y,
Float:pPos_z,
pVip,
pWalkieTalkie,
pWalkieBroken,
pChoosenTeam,
pRelationShip,
pRelationWith[125],
pInt,
pWarn,
pSlots,
pBackPack,
pSlotsFree,
pBan,
}
new PlayerInfo[MAX_PLAYERS][pInfo];

RedWolfX
28/09/2013, 10:24 PM
Bump, this is the Update code:

format(Query,sizeof(Query),"UPDATE `users` SET `IP` = '%s',`Score` = '%i', `AdminLevel` = '%i', WHERE `Username` = '%s'",

This is the error in mysql.log


[Sun Sep 29 01:26:56 2013] Connected (0) to samp01_db @ Localhost via UNIX socket.
[Sun Sep 29 01:26:56 2013] MySQL Server Version 5.5.32-cll.
[Sun Sep 29 01:27:13 2013] Error (0): Failed to exeute query. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''93D35' at line 1.
[Sun Sep 29 01:27:13 2013] Error (0): Function: mysql_store_result called when no prior successful query executed. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''93D35' at line 1.
[Sun Sep 29 01:27:27 2013] Error (0): Failed to exeute query. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''Daryl_Rale' at line 1.
[Sun Sep 29 01:28:00 2013] Error (0): Failed to exeute query. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1.


What should I do to fix this?

This is the tables atm:


if(mysql_connect(SQL_HOST,SQL_USER,SQL_PASSWORD,SQ L_DATABASE,conne,1)) { print("Connected 1"); }
mysql_query(
"CREATE TABLE IF NOT EXISTS `users` ("\
"`AccID` int(10) NOT NULL AUTO_INCREMENT,"\
"`Username` varchar(24) NOT NULL,"\
"`Password` varchar(24) NOT NULL,"\
"`Score` int(15) NULL,"\
"`AdminLevel` int(1) NULL,"\
"`IP` varchar(16) NULL,"\
"PRIMARY KEY (`AccID`)"\
") ENGINE=InnoDB DEFAULT CHARSET=latin1;");

Airin
28/09/2013, 11:17 PM
Well I'm not a good scripter by any means in fact I'm new at it but mine saves fine. So I'll just paste a few lines of my code and hopefully it will help you! :)

Code:

#include <a_mysql>: First and foremost you MUST have this include code line towards the top of your script and also this inside both the pawno include folder and the regular include folder as well as the plugin folder.

forward ConnectMySQL(); U must have this Forward Line towards the top as well with the rest of your forward's.

public LoadStuff()
{
new query[156];
format(query,sizeof(query),"SELECT * FROM statistics");
mysql_query(query);
mysql_store_result();
new line[1024];
mysql_fetch_row(line);
sscanf(line, "p<|>s[256]s[30]ds[64]s[64]dds[30]s[64]s[64]",
ServerMOTD,ServerMOTDSetBy,ServerMaxPlayers,Server MaxPlayersDate,ServerMaxPlayersTime,ServerTickets, ServerMaxFlightStreak,ServerMaxFlightStreakWinner, ServerMaxFlightStreakDate,ServerMaxFlightStreakTim e);
mysql_free_result();
sTrackPlayers=1;

public YourOnline(playerid)
{
new EscName[MAX_PLAYER_NAME];
mysql_real_escape_string(PlayerName(playerid),EscN ame);
new query[100];
format(query,sizeof(query),"UPDATE users SET online='1' WHERE username='%s'", EscName);
mysql_reconnect();
mysql_query(query);

public YourOffline(playerid)
{
new EscName[MAX_PLAYER_NAME];
mysql_real_escape_string(PlayerName(playerid),EscN ame);
new query[100];
format(query,sizeof(query),"UPDATE users SET online='0' WHERE username='%s'", EscName);
mysql_reconnect();
mysql_query(query);

Now that's not every single line of MySQL Code in my script because my script is over 5k lines long lol! But I just thought I'd post a few lines to help get you on the right track and to help get you started. If there is anything else that I can help you with please let me know. Maybe we can help eachother out? Anyways, let me know how it goes! :) And if I was helpful please don't forget to give me rep! :) I could really use it! :)

BlackBank
29/09/2013, 10:31 AM
Bump, this is the Update code:

format(Query,sizeof(Query),"UPDATE `users` SET `IP` = '%s',`Score` = '%i', `AdminLevel` = '%i', WHERE `Username` = '%s'",

This is the error in mysql.log


[Sun Sep 29 01:26:56 2013] Connected (0) to samp01_db @ Localhost via UNIX socket.
[Sun Sep 29 01:26:56 2013] MySQL Server Version 5.5.32-cll.
[Sun Sep 29 01:27:13 2013] Error (0): Failed to exeute query. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''93D35' at line 1.
[Sun Sep 29 01:27:13 2013] Error (0): Function: mysql_store_result called when no prior successful query executed. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''93D35' at line 1.
[Sun Sep 29 01:27:27 2013] Error (0): Failed to exeute query. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''Daryl_Rale' at line 1.
[Sun Sep 29 01:28:00 2013] Error (0): Failed to exeute query. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1.


You have a ',' before the WHERE, so remove that. ;)

Also one tip, escape your usernames.

RedWolfX
29/09/2013, 10:35 AM
You have a ',' before the WHERE, so remove that. ;)

Also one tip, escape your usernames.

I removed that ','. Still didnt work. But It removed 2 errors(I think).

This is the ones I get now:

[Sun Sep 29 13:36:01 2013] Error (0): Failed to exeute query. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''93D35' at line 1.
[Sun Sep 29 13:36:01 2013] Error (0): Function: mysql_store_result called when no prior successful query executed. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''93D35' at line 1.


And I think its from my register dialog:


new EscapedText[64],buffer[129],Query[90];
mysql_real_escape_string(inputtext, EscapedText);
WP_Hash(buffer,sizeof(buffer),EscapedText);
format(Query,sizeof(Query),"INSERT INTO `users` (Username,Password,IP,Score,AdminLevel) VALUES('%s','%s','%s','1','0')",GetPName(playerid),buffer,GetIP(playerid),GetPlay erScore(playerid),PlayerInfo[playerid][pAdmin]);
mysql_query(Query);
mysql_store_result();
mysql_free_result();
LoadStats(playerid);

BlackBank
29/09/2013, 02:19 PM
I removed that ','. Still didnt work. But It removed 2 errors(I think).

This is the ones I get now:

[Sun Sep 29 13:36:01 2013] Error (0): Failed to exeute query. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''93D35' at line 1.
[Sun Sep 29 13:36:01 2013] Error (0): Function: mysql_store_result called when no prior successful query executed. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''93D35' at line 1.


And I think its from my register dialog:


new EscapedText[64],buffer[129],Query[90];
mysql_real_escape_string(inputtext, EscapedText);
WP_Hash(buffer,sizeof(buffer),EscapedText);
format(Query,sizeof(Query),"INSERT INTO `users` (Username,Password,IP,Score,AdminLevel) VALUES('%s','%s','%s','1','0')",GetPName(playerid),buffer,GetIP(playerid),GetPlay erScore(playerid),PlayerInfo[playerid][pAdmin]);
mysql_query(Query);
mysql_store_result();
mysql_free_result();
LoadStats(playerid);


You do know that whirlpool needs 145 string size, so that means that the Query string also need a bigger size. So 90 + 145.

RedWolfX
29/09/2013, 05:54 PM
Oh, gonna try that now then

Konstantinos
29/09/2013, 05:56 PM
You do know that whirlpool needs 145 string size, so that means that the Query string also need a bigger size. So 90 + 145.

129*


INSERT INTO `users` (Username,Password,IP,Score,AdminLevel) VALUES('%s','%s','%s',1,0)


That's 250 lenght.

Also a tip. Use default values (when a tables is created) to prevent inserting numbers like that in queries.

RedWolfX
29/09/2013, 06:25 PM
129*


INSERT INTO `users` (Username,Password,IP,Score,AdminLevel) VALUES('%s','%s','%s',1,0)


That's 250 lenght.

Also a tip. Use default values (when a tables is created) to prevent inserting numbers like that in queries.

That worked! Thank you so much.

This problem has been driving me crazy