Mysql Errorid 1064 - Printable Version

Mysql Errorid 1064 - 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 Errorid 1064 (/showthread.php?tid=416895)

Mysql Errorid 1064 - Admigo - 18.02.2013

Heey all,

I have this error in my server log:

Code:

EID: 1064 | Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL serverversion for the right syntax to use near ''users' SET 'Money' = 5000, 'Score' = 0, 'Admin' = 0, 'Kills' = 0, 'Deaths' = 0 ' at line 1 | Query: UPDATE 'users' SET 'Money' = 5000, 'Score' = 0, 'Admin' = 0, 'Kills' = 0, 'Deaths' = 0 WHERE Username 'Admigo'

My code:

pawn Code:

public OnPlayerConnect(playerid)
{
    mysql_format(gConnectionHandle,Query, "SELECT * FROM `users` WHERE 'Username' = '%s'", GetName(playerid));
    mysql_function_query(gConnectionHandle, Query, false , "CheckPlayer", "d", playerid);
    return 1;
}

public OnPlayerDisconnect(playerid, reason)
{
    mysql_format(gConnectionHandle, Query, "UPDATE 'users' SET 'Money' = %d, 'Score' = %d, 'Admin' = %d, 'Kills' = %d, 'Deaths' = %d WHERE Username '%e'",GetPlayerMoney(playerid),GetPlayerScore(playerid),PlayerInfo[playerid][pAdmin],PlayerInfo[playerid][pKills],PlayerInfo[playerid][pDeaths],GetName(playerid));
    mysql_function_query(gConnectionHandle, Query, false , "OnQueryFinish", "s", Query);
    return 1;
}

How to fix this?
I am using Mysql R14.

Admigo

Re: Mysql Errorid 1064 - Gamer_Z - 18.02.2013

WHERE Username 'Admigo' >> WHERE Username = 'Admigo'

Re: Mysql Errorid 1064 - Shaneisace - 18.02.2013

pawn Code:

public OnPlayerConnect(playerid)
{
    mysql_format(gConnectionHandle,Query, "SELECT * FROM `users` WHERE `Username` = '%s'", GetName(playerid));
    mysql_function_query(gConnectionHandle, Query, false , "CheckPlayer", "d", playerid);
    return 1;
}

public OnPlayerDisconnect(playerid, reason)
{
    mysql_format(gConnectionHandle, Query, "UPDATE `users` SET `Money` = %d, `Score` = %d, `Admin` = %d, `Kills` = %d, `Deaths` = %d WHERE `Username` = '%s'",GetPlayerMoney(playerid),GetPlayerScore(playerid),PlayerInfo[playerid][pAdmin],PlayerInfo[playerid][pKills],PlayerInfo[playerid][pDeaths],GetName(playerid));
    mysql_function_query(gConnectionHandle, Query, false , "OnQueryFinish", "s", Query);
    return 1;
}

Don't use '' at UPDATE 'users' SET it must be UPDATE `users` SET and i have corrected your ' in the other places to `

Re: Mysql Errorid 1064 - Admigo - 18.02.2013

Quote:

Originally Posted by Shaneisace

pawn Code:

public OnPlayerConnect(playerid)
{
    mysql_format(gConnectionHandle,Query, "SELECT * FROM `users` WHERE `Username` = '%s'", GetName(playerid));
    mysql_function_query(gConnectionHandle, Query, false , "CheckPlayer", "d", playerid);
    return 1;
}

public OnPlayerDisconnect(playerid, reason)
{
    mysql_format(gConnectionHandle, Query, "UPDATE `users` SET `Money` = %d, `Score` = %d, `Admin` = %d, `Kills` = %d, `Deaths` = %d WHERE `Username` = '%s'",GetPlayerMoney(playerid),GetPlayerScore(playerid),PlayerInfo[playerid][pAdmin],PlayerInfo[playerid][pKills],PlayerInfo[playerid][pDeaths],GetName(playerid));
    mysql_function_query(gConnectionHandle, Query, false , "OnQueryFinish", "s", Query);
    return 1;
}

Don't use '' at UPDATE 'users' SET it must be UPDATE `users` SET and i have corrected your ' in the other places to `

Thanks. Can you only edit this for me?

Code:

case 0:
	    {
	        if(!response)return Kick(playerid);
	        mysql_format(gConnectionHandle, Query , "SELECT * FROM 'users' WHERE Username '%e' AND Password = md5('%e')",GetName(playerid),inputtext);
	        mysql_function_query(gConnectionHandle , Query , false ,"LoginPlayer" , "ds" , playerid,inputtext);
	    
	    }
	    case 1:
	    {
	        if(!response)return Kick(playerid);
	        new IP[50];
	        GetPlayerIp(playerid,IP,sizeof (IP));
			mysql_format(gConnectionHandle, Query, "INSERT INTO `users` (`Username`, `Password`, `Money`, `Score`, `Admin`, `Kills`, `Deaths`, `IP`) VALUES('%e' , md5('%e'), 5000, 0, 0, 0, 0, '%e')",GetName(playerid),inputtext,IP);
			mysql_function_query(gConnectionHandle, Query, false , "OnQueryFinish", "s",Query);
			GivePlayerMoney(playerid,5000);
			SetPlayerScore(playerid,0);
		}

Re: Mysql Errorid 1064 - Vince - 18.02.2013

Just leave the backticks out. They are only necessary when the table or field name is a keyword.

Re: Mysql Errorid 1064 - Shaneisace - 19.02.2013

pawn Code:

case 0:
        {
            if(!response)return Kick(playerid);
            mysql_format(gConnectionHandle, Query , "SELECT * FROM 'users' WHERE Username '%s' AND Password = md5('%s')",GetName(playerid),inputtext);
            mysql_function_query(gConnectionHandle , Query , false ,"LoginPlayer" , "ii" , playerid,inputtext);
        
        }
        case 1:
        {
            if(!response)return Kick(playerid);
            new IP[50];
            GetPlayerIp(playerid,IP,sizeof (IP));
            mysql_format(gConnectionHandle, Query, "INSERT INTO `users` (`Username`, `Password`, `Money`, `Score`, `Admin`, `Kills`, `Deaths`, `IP`) VALUES('%s' , md5('%s'), 5000, 0, 0, 0, 0, '%s')",GetName(playerid),inputtext,IP);
            mysql_function_query(gConnectionHandle, Query, false , "OnQueryFinish", "ii",Query);
            GivePlayerMoney(playerid,5000);
            SetPlayerScore(playerid,0);
        }

Re: Mysql Errorid 1064 - 3ventic - 19.02.2013

Quote:

Originally Posted by Shaneisace

pawn Code:

case 0:
        {
            if(!response)return Kick(playerid);
            mysql_format(gConnectionHandle, Query , "SELECT * FROM 'users' WHERE Username '%s' AND Password = md5('%s')",GetName(playerid),inputtext);
            mysql_function_query(gConnectionHandle , Query , false ,"LoginPlayer" , "ii" , playerid,inputtext);
        
        }
        case 1:
        {
            if(!response)return Kick(playerid);
            new IP[50];
            GetPlayerIp(playerid,IP,sizeof (IP));
            mysql_format(gConnectionHandle, Query, "INSERT INTO `users` (`Username`, `Password`, `Money`, `Score`, `Admin`, `Kills`, `Deaths`, `IP`) VALUES('%s' , md5('%s'), 5000, 0, 0, 0, 0, '%s')",GetName(playerid),inputtext,IP);
            mysql_function_query(gConnectionHandle, Query, false , "OnQueryFinish", "ii",Query);
            GivePlayerMoney(playerid,5000);
            SetPlayerScore(playerid,0);
        }

You forgot 'users' in the first query, -> `users` or users

Re: Mysql Errorid 1064 - Admigo - 19.02.2013

Thanks all. No errors now.
Only i have a problem with logging in.
When i enter the right password the server stops running.
OnDialogResponse:

pawn Code:

case 0:
        {
            if(!response)return Kick(playerid);
            mysql_format(gConnectionHandle, Query , "SELECT * FROM `users` WHERE `Username` = '%s' AND `Password` = md5('%s')",GetName(playerid),inputtext);
            mysql_function_query(gConnectionHandle , Query , false ,"LoginPlayer" , "ii" , playerid,inputtext);
        }
        case 1:
        {
            if(!response)return Kick(playerid);
            new IP[50];
            GetPlayerIp(playerid,IP,sizeof (IP));
            mysql_format(gConnectionHandle, Query, "INSERT INTO `users` (`Username`, `Password`, `Money`, `Score`, `Admin`, `Kills`, `Deaths`, `IP`) VALUES('%s' , md5('%s'), 5000, 0, 0, 0, 0, '%s')",GetName(playerid),inputtext,IP);
            mysql_function_query(gConnectionHandle, Query, false , "OnQueryFinish", "ii",Query);
            GivePlayerMoney(playerid,5000);
            SetPlayerScore(playerid,0);
        }

LoginPlayer check:

pawn Code:

public LoginPlayer(playerid,password)
{
    mysql_store_result();
    if(mysql_num_rows(gConnectionHandle) == 1)
    {
        if(mysql_retrieve_row())
        {
            mysql_fetch_field_row(mysqlstring, "Money"); GivePlayerMoney(playerid,strval(mysqlstring));
            mysql_fetch_field_row(mysqlstring, "Score"); SetPlayerScore(playerid,strval(mysqlstring));
            mysql_fetch_field_row(mysqlstring, "Admin"); PlayerInfo[playerid][pAdmin] = strval(mysqlstring);
            mysql_fetch_field_row(mysqlstring, "Kills"); PlayerInfo[playerid][pKills] = strval(mysqlstring);
            mysql_fetch_field_row(mysqlstring, "Deaths"); PlayerInfo[playerid][pDeaths] = strval(mysqlstring);
            SendClientMessage(playerid,-1,"Succesfull Logged IN!");
        }
    }
    else
    {
        SetPVarInt(playerid,"WrongPassword", GetPVarInt(playerid , "WrongPassword") + 1);
        if(GetPVarInt(playerid,"WrongPassword")==3)
        {
            SendClientMessage(playerid,-1,"Kicked From Server , Reason : Failed Logins !");
            return Kick(playerid);
        }
        format(mysqlstring,sizeof (mysqlstring),"Welcome %s /n Type your password below to Login on your CnR Account!",GetName(playerid));
        ShowPlayerDialog(playerid, 0 , DIALOG_STYLE_PASSWORD, "Login on your CnR Account!",mysqlstring,"OK","Cancel");
        SendClientMessage(playerid,-1,"Wrong Password!");
    }
    mysql_free_result();
    return 1;
}

How can i fix this?

Re: Mysql Errorid 1064 - Gamer_Z - 19.02.2013

Quote:

Originally Posted by Admigo

Thanks all. No errors now.
Only i have a problem with logging in.
When i enter the right password the server stops running.
OnDialogResponse:

pawn Code:

case 0:
        {
            if(!response)return Kick(playerid);
            mysql_format(gConnectionHandle, Query , "SELECT * FROM `users` WHERE `Username` = '%s' AND `Password` = md5('%s')",GetName(playerid),inputtext);
            mysql_function_query(gConnectionHandle , Query , false ,"LoginPlayer" , "ii" , playerid,inputtext);
        }
        case 1:
        {
            if(!response)return Kick(playerid);
            new IP[50];
            GetPlayerIp(playerid,IP,sizeof (IP));
            mysql_format(gConnectionHandle, Query, "INSERT INTO `users` (`Username`, `Password`, `Money`, `Score`, `Admin`, `Kills`, `Deaths`, `IP`) VALUES('%s' , md5('%s'), 5000, 0, 0, 0, 0, '%s')",GetName(playerid),inputtext,IP);
            mysql_function_query(gConnectionHandle, Query, false , "OnQueryFinish", "ii",Query);
            GivePlayerMoney(playerid,5000);
            SetPlayerScore(playerid,0);
        }

LoginPlayer check:

pawn Code:

public LoginPlayer(playerid,password)
{
    mysql_store_result();
    if(mysql_num_rows(gConnectionHandle) == 1)
    {
        if(mysql_retrieve_row())
        {
            mysql_fetch_field_row(mysqlstring, "Money"); GivePlayerMoney(playerid,strval(mysqlstring));
            mysql_fetch_field_row(mysqlstring, "Score"); SetPlayerScore(playerid,strval(mysqlstring));
            mysql_fetch_field_row(mysqlstring, "Admin"); PlayerInfo[playerid][pAdmin] = strval(mysqlstring);
            mysql_fetch_field_row(mysqlstring, "Kills"); PlayerInfo[playerid][pKills] = strval(mysqlstring);
            mysql_fetch_field_row(mysqlstring, "Deaths"); PlayerInfo[playerid][pDeaths] = strval(mysqlstring);
            SendClientMessage(playerid,-1,"Succesfull Logged IN!");
        }
    }
    else
    {
        SetPVarInt(playerid,"WrongPassword", GetPVarInt(playerid , "WrongPassword") + 1);
        if(GetPVarInt(playerid,"WrongPassword")==3)
        {
            SendClientMessage(playerid,-1,"Kicked From Server , Reason : Failed Logins !");
            return Kick(playerid);
        }
        format(mysqlstring,sizeof (mysqlstring),"Welcome %s /n Type your password below to Login on your CnR Account!",GetName(playerid));
        ShowPlayerDialog(playerid, 0 , DIALOG_STYLE_PASSWORD, "Login on your CnR Account!",mysqlstring,"OK","Cancel");
        SendClientMessage(playerid,-1,"Wrong Password!");
    }
    mysql_free_result();
    return 1;
}

How can i fix this?

at which line does it crash? Yes you can debug that..

and ByTheWay, you don't need to supply password anymore, if there is a result which matches the playername ANd the password the password is already correct.

also, you have a memory leak at "return Kick(playerid);"

Re: Mysql Errorid 1064 - BlackBank - 19.02.2013

pawn Code:

public LoginPlayer(playerid,password)
{
    mysql_store_result(gConnectionHandle);
    if(mysql_retrieve_row(gConnectionHandle) == 1)
    {
        mysql_fetch_field_row(mysqlstring, "Money"); GivePlayerMoney(playerid,strval(mysqlstring));
        mysql_fetch_field_row(mysqlstring, "Score"); SetPlayerScore(playerid,strval(mysqlstring));
        mysql_fetch_field_row(mysqlstring, "Admin"); PlayerInfo[playerid][pAdmin] = strval(mysqlstring);
        mysql_fetch_field_row(mysqlstring, "Kills"); PlayerInfo[playerid][pKills] = strval(mysqlstring);
        mysql_fetch_field_row(mysqlstring, "Deaths"); PlayerInfo[playerid][pDeaths] = strval(mysqlstring);
        SendClientMessage(playerid,-1,"Succesfull Logged IN!");
        mysql_free_result(gConnectionHandle);
    }
    else
    {
        mysql_free_result(gConnectionHandle);
        SetPVarInt(playerid,"WrongPassword", GetPVarInt(playerid , "WrongPassword") + 1);
        if(GetPVarInt(playerid,"WrongPassword")==3)
        {
            SendClientMessage(playerid,-1,"Kicked From Server , Reason : Failed Logins !");
            return Kick(playerid);
        }
        format(mysqlstring,sizeof (mysqlstring),"Welcome %s /n Type your password below to Login on your CnR Account!",GetName(playerid));
        ShowPlayerDialog(playerid, 0 , DIALOG_STYLE_PASSWORD, "Login on your CnR Account!",mysqlstring,"OK","Cancel");
        SendClientMessage(playerid,-1,"Wrong Password!");
    }
    return 1;
}

Try this code.
There is no need to check how much rows it has, you could simple fix this to add 'LIMIT 1' after your login query.
Also i have added 'gConnectionHandle' to all the mysql function, maybe this causes the crash.