MYSQL syntax error - Printable Version

MYSQL syntax error - 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 syntax error (/showthread.php?tid=353692)

MYSQL syntax error - Jstylezzz - 24.06.2012

Hi everyone,

With the help I got last time, I was able to fix the biggest problem in my gamemode, the loading part.
Now that that is fixed, I found out the saving system is not working..
This is the error in the debug:

pawn Код:

An error has occured. (Error ID: 1064, 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 'WHERE Username = 'Jari_Johnson'' at line 1)

This is my savestats Query:

pawn Код:

forward SaveStats(playerid);
public SaveStats(playerid)
{
    if(GetPVarInt(playerid,"Logged") == 1)
    {
    
    new query[1024];
    format(query, sizeof(query), "UPDATE Users SET \
    Money = '%d', \
    Level = '%d', \
    Admin = '%d', \
    Helper = '%d', \
    VipLevel = '%d', \
    Skin = '%d', \
    Kills = '%d', \
    Deaths = '%d', \
    Muted = '%d', \
    Cell = '%d', \
    Cigs = '%d', \
    Watch = '%d', \
    Mask = '%d', \
    GasCan = '%d', \
    Sprunk = '%d', \
    Stereo = '%d', \
    GoldCoins = '%d', \
    PaymentMethod = '%d', \
    DriversLic = '%d', \
    WeaponLic = '%d', \
    HeavyVLic = '%d', \
    PilotLic = '%d', \
    BlockedPM = '%d',\
    Proposing = '%d',\
    Warnings = '%d',\
    MarriedTo = '%s',\
    WHERE Username = '%s'",
    GetPlayerMoney(playerid),
    GetPVarInt(playerid,"Score"),
    GetPVarInt(playerid,"Level"),
    GetPVarInt(playerid,"Helper"),
    GetPVarInt(playerid,"Vip Level"),
    GetPVarInt(playerid,"Skin"),
    GetPVarInt(playerid,"Kills"),
    GetPVarInt(playerid,"Deaths"),
    GetPVarInt(playerid,"Muted"),
    GetPVarInt(playerid,"HasCellphone"),
    GetPVarInt(playerid,"HasCigs"),
    GetPVarInt(playerid,"HasWatch"),
    GetPVarInt(playerid,"HasMask"),
    GetPVarInt(playerid,"GasCan"),
    GetPVarInt(playerid,"HasSprunk"),
    GetPVarInt(playerid,"HasStereo"),
    GetPVarInt(playerid,"GoldCoins"),
    GetPVarInt(playerid,"PMethod"),
    GetPVarInt(playerid,"DriversLic"),
    GetPVarInt(playerid,"WeaponLic"),
    GetPVarInt(playerid,"HeavyVLic"),
    GetPVarInt(playerid,"PilotLic"),
    PInfo[playerid][BlockedPM],
    PInfo[playerid][Proposing],
    PInfo[playerid][Warnings],
    PInfo[playerid][MarriedTo],
    GetPlayerNameEx(playerid));
    mysql_query(query);
    SendClientMessage(playerid,COLOR_RED,"Saving 1 debugged");
    
    format(query, sizeof(query), "UPDATE Users SET \
    Age = '%d',\
    PNumber = '%d',\
    PDesc = '%s',\
    PMail = '%s',\
    PCountry = '%s',\
    PSex = '%d',\
    PMailConfirmed = '%d',\
    WHERE Username = '%s'",
    PInfo[playerid][Age],
    PInfo[playerid][PNumber],
    PInfo[playerid][PDesc],
    PInfo[playerid][PMail],
    PInfo[playerid][PCountry],
    PInfo[playerid][PSex],
    PInfo[playerid][PMailConfirmed],
    GetPlayerNameEx(playerid));
    mysql_query(query);
    SendClientMessage(playerid,COLOR_RED,"Saving 2 debugged");
    }
}

The error says it occurs near where Username = so this means it's somewhere at the bottom(s).
I don't see the problem, but I hope someone of you sees it.
Thanks

Re: MYSQL syntax error - sanrock - 24.06.2012

Don't put , before WHERE

Re: MYSQL syntax error - Jstylezzz - 24.06.2012

Err.. i don't think this is the right solution, because now it saves my stats in every row that exists on my server...
But, thanks for the fast answer!

Re: MYSQL syntax error - ReneG - 24.06.2012

Print the queries and post what they output, you can always figure it out by looking at it full formatted query.

Re: MYSQL syntax error - iggy1 - 24.06.2012

Could you post the finished query please? Might spot the error easier with that.

pawn Код:

printf("%s", query);

EDIT: Too slow.

Re: MYSQL syntax error - Jstylezzz - 24.06.2012

It prints out exactly what the MYSQL_Debug did....
But in the debug it describes the error as, Near Where username =

Finished query:

pawn Код:

[13:57:39] UPDATE Users SET Money = '6000', Level = '1', Admin = '6', Helper = '1', VipLevel = '0', Skin = '240', Kills = '0', Deaths = '0', Muted = '0', Cell = '0', Cigs = '50', Watch = '1', Mask = '1', GasCan = '50', Sprunk = '50', Stereo = '1', GoldCoins = '999', PaymentMethod = '1', DriversLic = '0', WeaponLic = '0', HeavyVLic = '0', PilotLic = '0', BlockedPM = '0',Proposing = '0',Warnings = '0',MarriedTo = '',WHERE Username = 'Jari_Johnson'
[13:57:39] UPDATE Users SET Age = '100',PNumber = '72',PDesc = 'Description',PMail = 'email',PCountry = 'Country',PSex = '0',PMailConfirmed = '0',WHERE Username = 'Jari_Johnson'

Re: MYSQL syntax error - ReneG - 24.06.2012

Remove the , before the WHERE clause.

EDIT:

Just noticed, the MarriedTo collumn is null when you're formatting the query.

Re: MYSQL syntax error - Jstylezzz - 24.06.2012

aaaaah so that is what sanrock also meant...
fail from me.. sorry.
So it should be like:

pawn Код:

MarriedTo = '%s'\ //without a ,
WHERE Username = '%s'",

Re: MYSQL syntax error - ReneG - 24.06.2012

Yes, only use ,'s when you're updating, inserting, selecting more than one value. The comma acts as a separator.

Re: MYSQL syntax error - Jstylezzz - 24.06.2012

Thanks very much for the help everyone

I will edit my post to say if it worked(i am sure it will)
EDIT: everything is working fine, thanks