SA-MP Forums Archive
MySQL Help - 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 Help (/showthread.php?tid=599206)


MySQL Help - Lumineux - 22.01.2016

Hi there ! I have a problem with this MySQL code:
Код:
new query[1000], insertstring[1000], playername[128], string[512];
GetPlayerName(playerid, playername, sizeof(playername));
format(insertstring, sizeof(insertstring), "INSERT INTO accounts (Password, Name, Level, Admin, Helper, Premium, UpgradePoints, ConnectedTime, Registered, Sex, Age");
format(string, sizeof(string), ", Origin, Muted, Respect, Cash, Bank, Crimes, Kills, Deaths, Arrested, WantedDeaths, Phonebook, LottoNr, Fishes, BiggestFish, Job, Paycheck, HeadValue");
strcat(insertstring, string, sizeof(insertstring));
format(string, sizeof(string), ", Materials, Drugs, Lider, Membru, Rank, Char, DetSkill, SexSkill, BoxSkill, JackSkill, CarSkill, NewsSkill, DrugsSkill, CookSkill, FishSkill, pHealth, Interior");
strcat(insertstring, string, sizeof(insertstring));
format(string, sizeof(string), ", Local, WantedLevel, Jailed, JailTime, Telefon, FirstCar, SecondCar, Helicopter, Boat, Plane, Fwarn, House, Business, CarLic, FlyLic, BoatLic, FishLic");
strcat(insertstring, string, sizeof(insertstring));
format(string, sizeof(string), ", GunLic, Gun1, Gun2, Gun3, Gun4, Ammo1, Ammo2, Ammo3, Ammo4, CarTime, PayDay, PayDayHad, CDPlayer, Wins, Loses, AlcoholPerk, DrugPerk");
strcat(insertstring, string, sizeof(insertstring));
format(string, sizeof(string), ", MiserPerk, PainPerk, TraderPerk, Tutorial, Warnings, Adjustable, Married, AcceptPoints, MarriedTo, JobCD, WalkieTalkie, GasCan, Coins) VALUES ('%s', '%s', 1, NULL, NULL", inputtext, playername);
strcat(insertstring, string, sizeof(insertstring));
format(string, sizeof(string), ", NULL, NULL, NULL, 1, NULL, NULL, NULL, NULL, NULL, 50000, 50000, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL");
strcat(insertstring, string, sizeof(insertstring));
format(string, sizeof(string), ", NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 50.00, NULL, 15, 255, NULL, NULL, NULL, NULL, -1, -1, -1, -1, -1, NULL, 255, 255, NULL, NULL, NULL, NULL, NULL, NULL, NULL");
strcat(insertstring, string, sizeof(insertstring));
format(string, sizeof(string), ", NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 'No-One', NULL, NULL, NULL, NULL)");
strcat(insertstring, string, sizeof(insertstring));
mysql_format(mysql, query, sizeof(query), insertstring);
mysql_tquery(mysql, query, "OnPlayerRegister", "i", playerid);
I get those errors:

Код:
[23:27:35] [DEBUG] mysql_format - connection: 1, len: 1300, format: "INSERT INTO accounts (Password, Name, Level, Admin, Helper, Premium, UpgradePoints, ConnectedTime, Registered, Sex, Age, Origin,..."
[23:27:35] [DEBUG] mysql_tquery - connection: 1, query: "INSERT INTO accounts (Password, Name, Level, Admin, Helper, Prem", callback: "OnPlayerRegister", format: "i"
[23:27:35] [DEBUG] CMySQLQuery::Execute[OnPlayerRegister] - starting query execution
[23:27:35] [ERROR] CMySQLQuery::Execute[OnPlayerRegister] - (error #1064) Erreur de syntaxe prčs de 'Char, DetSkill, SexSkill, BoxSkill, JackSkill, CarSkill, NewsSkill, DrugsSkill, ' ŕ la ligne 1
[23:27:35] [DEBUG] CMySQLQuery::Execute[OnPlayerRegister] - error will be triggered in OnQueryError
Can anyone help me please ?

Re: MySQL Help - Jack_SMalls - 22.01.2016

Let's simplify that query. Make sure you set up default values in your table structure. After you have done that, remove all the nulls and base values that won't be set in this query specifically. Generally, you only need to insert values that have just been created, such as the username and their password... maybe a few other items.

Big queries like this can easily have small mistakes.

Re: MySQL Help - Lumineux - 22.01.2016

Quote:
Originally Posted by Jack_SMalls
Посмотреть сообщение
Let's simplify that query. Make sure you set up default values in your table structure. After you have done that, remove all the nulls and base values that won't be set in this query specifically. Generally, you only need to insert values that have just been created, such as the username and their password... maybe a few other items.

Big queries like this can easily have small mistakes.
You mean that i don't need to set the values to NULL, because they're already nulls ?
I will try to simplify that code.

Re: MySQL Help - Jack_SMalls - 22.01.2016

An insert query will put in any default values set in the structure.

Re: MySQL Help - AmigaBlizzard - 22.01.2016

Even for the values where you insert 50000, -1, 255, 50.00 and such, you can set those values as default in the structure of your table.
When you do that, your query can be reduced to barely 200 characters (of which 128 characters are needed for the hashed password), this means you only insert name and password, like Jack said already.

Re: MySQL Help - Lumineux - 23.01.2016

Thanks guys, problem solved. But now I have another one

Код:
[22:58:05] [DEBUG] mysql_format - connection: 1, len: 1500, format: "UPDATE accounts (Password, Name, Level, Admin, Helper, Premium, UpgradePoints, ConnectedTime, Registered, Sex, Age, Origin, Mute..."
[22:58:05] [DEBUG] mysql_tquery - connection: 1, query: "UPDATE accounts (Password, Name, Level, Admin, Helper, Premium, ", callback: "(null)", format: "(null)"
[22:58:05] [DEBUG] CMySQLQuery::Execute[] - starting query execution
[22:58:05] [ERROR] CMySQLQuery::Execute[] - (error #1064) Erreur de syntaxe prčs de '(Password, Name, Level, Admin, Helper, Premium, UpgradePoints, ConnectedTime, Re' ŕ la ligne 1
[22:58:05] [DEBUG] CMySQLQuery::Execute[] - error will be triggered in OnQueryError
[22:58:05] [DEBUG] Calling callback "OnQueryError"..
Код:
new query[1500], string[1500], string2[500];
format(string, sizeof(string), "UPDATE accounts (Password, Name, Level, Admin, Helper, Premium, UpgradePoints");
format(string2, sizeof(string2), ", ConnectedTime, Registered, Sex, Age, Origin, Muted, Respect, Cash, Bank, Crimes, Kills, Deaths, Arrested, WantedDeaths");
strcat(string, string2, sizeof(string));
format(string2, sizeof(string2), ", Phonebook, LottoNr, Fishes, BiggestFish, Job, Paycheck, HeadValue, Materials, Drugs, Lider, Membru, Rank, Skin, DetSkill");
strcat(string, string2, sizeof(string));
format(string2, sizeof(string2), ", SexSkill, BoxSkill, JackSkill, CarSkill, NewsSkill, DrugsSkill, CookSkill, FishSkill, pHealth, Interior, Local, WantedLevel");
strcat(string, string2, sizeof(string));
format(string2, sizeof(string2), ", Jailed, JailTime, Telefon, FirstCar, SecondCar, Helicopter, Boat, Plane, Fwarn, House, Business, CarLic, FlyLic, BoatLic, FishLic");
strcat(string, string2, sizeof(string));
format(string2, sizeof(string2), ", GunLic, Gun1, Gun2, Gun3, Gun4, Ammo1, Ammo2, Ammo3, Ammo4, CarTime, PayDay, PayDayHad, CDPlayer, Wins, Loses, AlcoholPerk");
strcat(string, string2, sizeof(string));
format(string2, sizeof(string2), ", DrugPerk, MiserPerk, PainPerk, TraderPerk, Tutorial, Warnings, Adjustable, Married, AcceptPoints, MarriedTo, JobCD, WalkieTalkie");
strcat(string, string2, sizeof(string));
format(string2, sizeof(string2), ", GasCan, Coins) SET ('%s', '%s', %d, %d, %d, %d, %d, %d, %d, %d, %d, %d, %d, %d, %d, %d, %d, %d, %d, %d, %d");
strcat(string, string2, sizeof(string));
format(string2, sizeof(string2), ", %d, %d, %d, %d, %d, %d, %d, %d, %d, %d, %d, %d, %d, %d, %d, %d, %d, %d, %d, %d, %d, %d, %d, %d, %d, %d");
strcat(string, string2, sizeof(string));
format(string2, sizeof(string2), ", %d, %d, %d, %d, %d, %d, %d, %d, %d, %d, %d, %d, %d, %d, %d, %d, %d, %d, %d, %d, %d, %d, %d, %d, %d, %d, %d, %d, %d, %d, %d");
strcat(string, string2, sizeof(string));
format(string2, sizeof(string2), ", %d, %d, %d, %d, %d, %d, %d, %d, %d, '%s', %d, %d, %d, %d) WHERE ID = %d");
strcat(string, string2, sizeof(string));
mysql_format(mysql, query, sizeof(query), string,
PlayerInfo[playerid][pKey], PlayerInfo[playerid][Name],
PlayerInfo[playerid][pLevel], PlayerInfo[playerid][pAdmin], PlayerInfo[playerid][pHelper], PlayerInfo[playerid][pDonateRank],
PlayerInfo[playerid][pUpgrade], PlayerInfo[playerid][pConnectTime], PlayerInfo[playerid][pReg], PlayerInfo[playerid][pSex],
PlayerInfo[playerid][pAge], PlayerInfo[playerid][pOrigin], PlayerInfo[playerid][pMuted], PlayerInfo[playerid][pExp],
PlayerInfo[playerid][pCash], PlayerInfo[playerid][pBank], PlayerInfo[playerid][pCrimes], PlayerInfo[playerid][pKills],
PlayerInfo[playerid][pDeaths], PlayerInfo[playerid][pArrested], PlayerInfo[playerid][pWantedDeaths], PlayerInfo[playerid][pPhoneBook],
PlayerInfo[playerid][pLottoNr], PlayerInfo[playerid][pFishes], PlayerInfo[playerid][pBiggestFish], PlayerInfo[playerid][pJob],
PlayerInfo[playerid][pPayCheck], PlayerInfo[playerid][pHeadValue], PlayerInfo[playerid][pMats], PlayerInfo[playerid][pDrugs],
PlayerInfo[playerid][pLider], PlayerInfo[playerid][pMembru], PlayerInfo[playerid][pRank], PlayerInfo[playerid][pChar],
PlayerInfo[playerid][pDetSkill], PlayerInfo[playerid][pSexSkill], PlayerInfo[playerid][pBoxSkill], PlayerInfo[playerid][pJackSkill],
PlayerInfo[playerid][pMechSkill], PlayerInfo[playerid][pNewsSkill], PlayerInfo[playerid][pDrugsSkill], PlayerInfo[playerid][pCookSkill],
PlayerInfo[playerid][pFishSkill], PlayerInfo[playerid][pHealth], PlayerInfo[playerid][pInt], PlayerInfo[playerid][pLocal],
WantedLevel[playerid], PlayerInfo[playerid][pJailed], PlayerInfo[playerid][pJailTime], PlayerInfo[playerid][pPtelefon],
PlayerInfo[playerid][pCarKey1], PlayerInfo[playerid][pCarKey2], PlayerInfo[playerid][pHeliKey], PlayerInfo[playerid][pBoatKey],
PlayerInfo[playerid][pPlaneKey], PlayerInfo[playerid][pFwarn], PlayerInfo[playerid][pHouseKey], PlayerInfo[playerid][pBizzKey],
PlayerInfo[playerid][pCarLic], PlayerInfo[playerid][pFlyLic], PlayerInfo[playerid][pBoatLic], PlayerInfo[playerid][pFishLic],
PlayerInfo[playerid][pGunLic], PlayerInfo[playerid][pGun1], PlayerInfo[playerid][pGun2], PlayerInfo[playerid][pGun3],
PlayerInfo[playerid][pGun4], PlayerInfo[playerid][pAmmo1], PlayerInfo[playerid][pAmmo2], PlayerInfo[playerid][pAmmo3],
PlayerInfo[playerid][pAmmo4], PlayerInfo[playerid][pCarTime], PlayerInfo[playerid][pPayDay], PlayerInfo[playerid][pPayDayHad],
PlayerInfo[playerid][pCDPlayer], PlayerInfo[playerid][pWins], PlayerInfo[playerid][pLoses], PlayerInfo[playerid][pAlcoholPerk],
PlayerInfo[playerid][pDrugPerk], PlayerInfo[playerid][pMiserPerk], PlayerInfo[playerid][pPainPerk], PlayerInfo[playerid][pTraderPerk],
PlayerInfo[playerid][pTut], PlayerInfo[playerid][pWarns], PlayerInfo[playerid][pAdjustable], PlayerInfo[playerid][pMarried],
PlayerInfo[playerid][pAcceptPoints], PlayerInfo[playerid][pMarriedTo], PlayerInfo[playerid][pJobCD], PlayerInfo[playerid][pWalkTalkie],
PlayerInfo[playerid][pGasCan], PlayerInfo[playerid][pCoins], PlayerInfo[playerid][ID]);
mysql_tquery(mysql, query, "", "");


Re: MySQL Help - AmigaBlizzard - 27.01.2016

You got to be kidding me.
You're using a DATABASE, not a textfile where you need to overwrite the entire file when you change 1 value.

When your players earn some money, and by that I mean they ONLY earn some money, nothing else, why would you update ALL those fields with the exact same data which is already there, just to be able to change 1 value (money)?

Split that query in ALOT of smaller queries which are only executed when your players change THAT specific field.

When players earn money, execute a query that only updates the money.
When they get a scorepoint, execute another query that updates the score.

You can even create a dedicated function for those things.

Snippet from my own script:
PHP код:
// This function adds the given Money and Score values to the given player
Player_Reward(playerid, Money, Score)
{
    new Query[128];
    // Add the given Money and Score to the player's account
    pData[playerid][PlayerMoney] = pData[playerid][PlayerMoney] + Money;
    pData[playerid][PlayerScore] = pData[playerid][PlayerScore] + Score;
    // Update money and score for this player in the player's account in MySQL
    mysql_format(SQL_db, Query, sizeof(Query), "UPDATE playerdata SET Money = '%i', Score = '%i' WHERE ID = '%i'", pData[playerid][PlayerMoney], pData[playerid][PlayerScore], pData[playerid][SQLID]);
    mysql_tquery(SQL_db, Query, "", "");
    return 1;
} 
I have alot more small functions like these.
They change the data in the server memory and also save them to the database.

This is one small function that only changes the money and score for the given player, nothing more, nothing less.
Small code and very easy to debug in case you have problems.

MySQL performs not well when it's being fed such huge queries all the time.
It has to break down the wall of text to single pieces, find each column and row, update the data (if it even needs updating), adjust search-indexes based on the data for faster searching and alot more.
A query that only has 50 characters is processed alot faster than a query that has 1500 characters.

If 1 value changes, don't update 100+ values.
Especially data that rarely changes, like a player's password, age, sex, admin-level, there is no need to update that all the time.

In real-life, when you wrote a message on a piece of paper with a crayon and you fix a typo, you don't go wiping out the entire piece of paper and rewrite everything as it was while fixing the typo, would you?
You only replace the typo and leave the rest alone.

Do the same when you use a database.

This looks like you're only saving everything at once upon OnPlayerDisconnect.

You really have to save whatever changes at the time those changes are made.
Player earns money -> save it.
Player earns score -> save it.
Player buys a house -> save it.
Player gets jailed -> save it (and again when he gets out of jail, and for longer jailtimes you might need to save in between as well).

Why?
Some players plays for hours in a row on your server and earn lots of money, lots of scorepoints, they buy a house, even some house-vehicle, they rank up, they make alot of kills, whatever they do.
And suddenly, there is a power failure at the server's datacenter.
Or the server gets attacked by a DDOS attack, or it just breaks down.
Basically, anything you can't possibly predict.

All the progress of that player is just gone because nothing has been saved for hours.

When he logs back in later on after such a crash, he still has the data he got when he logged in before the crash and lost hours of valuable stuff he acquired during his 10-hour long play time before the crash.
Nothing he acquired was saved and he has to start over from scratch and re-earn the stuff he already got yesterday.

My guess would be that he leaves and never comes back because of lousy data management by the scripter/server owner.
I would.