MySQL database saving 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: MySQL database saving problem (/showthread.php?tid=576516)
+- 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: MySQL database saving problem (/showthread.php?tid=576516)
MySQL database saving problem - GloomY - 04.06.2015
I have a problem with not updating all the database of a players account. It updates to LoginDate, everything else stays at 0. I can't seem to find a problem. It worked well untill I started to add more lines of mysql_format because the variable wouldn't allow me to put them all in one line.
This is the code:
PHP код:
new query[3096];
new Float:x, Float:y, Float:z, Float:a;
GetPlayerPos(playerid, x, y, z);
GetPlayerFacingAngle(playerid, a);
mysql_format(mysql, query, sizeof(query), "UPDATE `accounts` SET`Admin`= %d, `GameMaster`= %d, `VIP`= %d, `Money`= %d, `Registered`= %d, `Tutorial`= %d WHERE `ID`= '%d'",\
pInfo[playerid][pAdmin], pInfo[playerid][pGameMaster], pInfo[playerid][pVIP], pInfo[playerid][pMoney], pInfo[playerid][pRegistered], pInfo[playerid][pTutorial], pInfo[playerid][pID]);
mysql_format(mysql, query, sizeof(query), "UPDATE `accounts` SET`RegisterDate`= %d, `LoginDate`= %d, `Gender`= %d, `Age`= %d, `Origin`= %e, `Respect`= %d, `Money`= %d, `Bank`= %d, `Skin`= %d WHERE `ID`= '%d'",\
pInfo[playerid][pRegisterDate], pInfo[playerid][pLoginDate], pInfo[playerid][pGender], pInfo[playerid][pAge], pInfo[playerid][pOrigin], pInfo[playerid][pRespect], pInfo[playerid][pMoney], pInfo[playerid][pBank], pInfo[playerid][pSkin], pInfo[playerid][pID]);
mysql_format(mysql, query, sizeof(query), "UPDATE `accounts` SET`Interior`= %d, `World`= %d, `Jailed`= %d, `JailTime`= %d, `Muted`= %d, `Warnings`= %d, `Warn1`= %e, `Warn2`= %e, `Job`= %d WHERE `ID`= '%d'",\
pInfo[playerid][pInterior], pInfo[playerid][pWorld], pInfo[playerid][pJailed], pInfo[playerid][pJailTime], pInfo[playerid][pMuted], pInfo[playerid][pWarnings], pInfo[playerid][pWarn1], pInfo[playerid][pWarn2], pInfo[playerid][pJob], pInfo[playerid][pID]);
mysql_format(mysql, query, sizeof(query), "UPDATE `accounts` SET`PlayingHours`= %d, `PlayingMinutes`= %d, `House`= %d, `Business`= %d, `Phone`= %d, `Faction`= %d, `FactionRank`= %d, `MaskID`= %d WHERE `ID`= '%d'",\
pInfo[playerid][pPlayingHours], pInfo[playerid][pPlayingMinutes], pInfo[playerid][pHouse], pInfo[playerid][pBusiness], pInfo[playerid][pPhone], pInfo[playerid][pFaction], pInfo[playerid][pFactionRank], pInfo[playerid][pMaskID], pInfo[playerid][pID]);
mysql_format(mysql, query, sizeof(query), "UPDATE `accounts` SET`Married`= %d, `MarriedTo`= %e, `Watch`= %d, `Ciggaretes`= %d, `Lighter`= %d WHERE `ID`= '%d'",\
pInfo[playerid][pMarried], pInfo[playerid][pMarriedTo], pInfo[playerid][pWatch], pInfo[playerid][pCiggaretes], pInfo[playerid][pLighter], pInfo[playerid][pID]);
for (new i = 0; i < 13; i ++)
{
mysql_format(mysql, query, sizeof(query), "%s, `Gun%d` = '%d', `Ammo%d` = '%d'", query, i + 1, pInfo[playerid][pGuns][i], i + 1, pInfo[playerid][pAmmo][i]);
}
mysql_tquery(mysql, query, "", "");
Код:
[23:53:27] [DEBUG] mysql_format - connection: 1, len: 3096, format: "UPDATE `accounts` SET`Admin`= %d, `GameMaster`= %d, `VIP`= %d, `Money`= %d, `Registered`= %d, `Tutorial`= %d WHERE `ID`= '%d'" [23:53:27] [DEBUG] mysql_format - connection: 1, len: 3096, format: "UPDATE `accounts` SET`RegisterDate`= %d, `LoginDate`= %d, `Gender`= %d, `Age`= %d, `Origin`= %e, `Respect`= %d, `Money`= %d, `Ba..." [23:53:27] [DEBUG] mysql_format - connection: 1, len: 3096, format: "UPDATE `accounts` SET`Interior`= %d, `World`= %d, `Jailed`= %d, `JailTime`= %d, `Muted`= %d, `Warnings`= %d, `Warn1`= %e, `Warn2..." [23:53:27] [DEBUG] mysql_format - connection: 1, len: 3096, format: "UPDATE `accounts` SET`PlayingHours`= %d, `PlayingMinutes`= %d, `House`= %d, `Business`= %d, `Phone`= %d, `Faction`= %d, `Faction..." [23:53:27] [DEBUG] mysql_format - connection: 1, len: 3096, format: "UPDATE `accounts` SET`Married`= %d, `MarriedTo`= %e, `Watch`= %d, `Ciggaretes`= %d, `Lighter`= %d WHERE `ID`= '%d'" [23:53:27] [DEBUG] mysql_format - connection: 1, len: 3096, format: "%s, `Gun%d` = '%d', `Ammo%d` = '%d'" [23:53:27] [DEBUG] mysql_format - connection: 1, len: 3096, format: "%s, `Gun%d` = '%d', `Ammo%d` = '%d'" [23:53:27] [DEBUG] mysql_format - connection: 1, len: 3096, format: "%s, `Gun%d` = '%d', `Ammo%d` = '%d'" [23:53:27] [DEBUG] mysql_format - connection: 1, len: 3096, format: "%s, `Gun%d` = '%d', `Ammo%d` = '%d'" [23:53:27] [DEBUG] mysql_format - connection: 1, len: 3096, format: "%s, `Gun%d` = '%d', `Ammo%d` = '%d'" [23:53:27] [DEBUG] mysql_format - connection: 1, len: 3096, format: "%s, `Gun%d` = '%d', `Ammo%d` = '%d'" [23:53:27] [DEBUG] mysql_format - connection: 1, len: 3096, format: "%s, `Gun%d` = '%d', `Ammo%d` = '%d'" [23:53:27] [DEBUG] mysql_format - connection: 1, len: 3096, format: "%s, `Gun%d` = '%d', `Ammo%d` = '%d'" [23:53:27] [DEBUG] mysql_format - connection: 1, len: 3096, format: "%s, `Gun%d` = '%d', `Ammo%d` = '%d'" [23:53:27] [DEBUG] mysql_format - connection: 1, len: 3096, format: "%s, `Gun%d` = '%d', `Ammo%d` = '%d'" [23:53:27] [DEBUG] mysql_format - connection: 1, len: 3096, format: "%s, `Gun%d` = '%d', `Ammo%d` = '%d'" [23:53:27] [DEBUG] mysql_format - connection: 1, len: 3096, format: "%s, `Gun%d` = '%d', `Ammo%d` = '%d'" [23:53:27] [DEBUG] mysql_format - connection: 1, len: 3096, format: "%s, `Gun%d` = '%d', `Ammo%d` = '%d'" [23:53:27] [DEBUG] mysql_tquery - connection: 1, query: "UPDATE `accounts` SET`Married`= 0, `MarriedTo`= , `Watch`= 0, `C", callback: "(null)", format: "(null)" [23:53:27] [DEBUG] CMySQLQuery::Execute[] - starting query execution [23:53:27] [ERROR] CMySQLQuery::Execute[] - (error #1064) Erreur de syntaxe prčs de ' `Watch`= 0, `Ciggaretes`= 0, `Lighter`= 0 WHERE `ID`= '1', `Gun1` = '0', `Ammo1' ŕ la ligne 1 [23:53:27] [DEBUG] CMySQLQuery::Execute[] - error will be triggered in OnQueryError
Re: MySQL database saving problem - Vince - 04.06.2015
Normalize your database. You're not using a file system. If columns names have numbers in them you are doing databases wrong. https://sampforum.blast.hk/showthread.php?tid=505081
A single table should under normal circumstances not contain more than 30 or so columns; anything that is not directly related to the account should not be in the table. Anything that does not apply to the majority of players should also not be in the accounts table.
Each player can have one account (that is one table). An account can have zero or more warns (that's another table). An account can have zero or more characters (that's yet another table). A character can have zero or more guns (yes, yet another table). I hope you get my drift.
Re: MySQL database saving problem - GloomY - 04.06.2015
Can you please give me an example with my code?
Re: MySQL database saving problem - PowerPC603 - 05.06.2015
Also, don't update EVERY column everytime.
Does the money update everytime you save the data? Nope.
Does the admin-level change everytime? Also nope.
Does the registered value change everytime? Nope.
Only update whatever needs to be updated.
If you change one value, why update 50 variables in your table, overwriting everything that hasn't been changed?
It's like rewriting an entire piece of paper when you only need to fix a typo.
In normal circumstances, you would only erase the faulty character and rewrite only that character, not the entire page, would you?
Using files, you don't have much choice, you need to rewrite the entire file.
But using a database, you only need to update what has changed, not everything at once.
And the biggest mistake you've made:
You format a query but you only send the last one using the mysql_tquery command.
You're not sending all others, you just format them, then overwrite them on the next line.
Re: MySQL database saving problem - GloomY - 05.06.2015
Thank you guys.
Rep+ for you.
Fixed the problem and optimized my database to run faster!
