MySQL Query Errors. - Printable Version

MySQL Query Errors. - 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 Query Errors. (/showthread.php?tid=612041)

MySQL Query Errors. - Shady - 13.07.2016

Hello,

I was working on my gamemode and I found a problem with saving queries into the database, actually I'm using MySQL R6 (Don't tell me to upgrade) - so, I found that accounts aren't being saved or added into the database.

Save/Update Query Codes:

pawn Код:

stock SavePlayer(playerid)
{
    new query[126], pName[MAX_PLAYER_NAME];
    GetPlayerName(playerid, pName, sizeof(pName));

    GetPlayerPos(playerid, Player[playerid][Position][0], Player[playerid][Position][1], Player[playerid][Position][2]);

    format(query, sizeof(query), "UPDATE accounts SET Name = '%s', Password = '%s', PlayerX = '%f', PlayerY = '%f', PlayerZ = '%f' WHERE Name = '%s'", pName, Player[playerid][Password], Player[playerid][Position][0], Player[playerid][Position][1], Player[playerid][Position][2], pName);
    mysql_query(query);

    printf("[server] Player ID %d (%s) has been saved into the database.", playerid, pName);
}

Errors while attempt to update in queries:

pawn Код:

[18:25:45] CMySQLHandler::Query(UPDATE accounts SET Name = 'Steve_Malcom', Password = '', PlayerX = '1962.481079', PlayerY = '1342.575927', PlayerZ = '15.4) - An error has occured. (Error ID: 1064, You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ''15.4' at line 1)

Insert/Adding Codes (DIALOG):

pawn Код:

case 1:
        {
            if(response)
            {
                new query[126], pName[MAX_PLAYER_NAME];
                GetPlayerName(playerid, pName, sizeof(pName));

                format(query, sizeof(query), "INSERT INTO accounts VALUES ('%s', '%s')", pName, inputtext);
                mysql_query(query);
                SendClientMessage(playerid, COLOR_WHITE, "You have successfully registered into the server!");

                SetSpawnInfo(playerid, 1, 299, 1, 2, 3, 1, 0, 0, 0, 0, 0, 0);
                SpawnPlayer(playerid);
            }

Errors while attempt to insert a new data in the database:

pawn Код:

[18:25:23] CMySQLHandler::Query(INSERT INTO accounts VALUES ('Steve_Malcom', 'passhere')) - An error has occured. (Error ID: 1136, Column count doesn't match value count at row 1)

Re: MySQL Query Errors. - Konstantinos - 13.07.2016

Not sure about the UPDATE query but you should seriously change the way you save. Name does not change unless it is done by the script, so does password; thus those need to be updated when necessary and not every time a player disconnects.

About the INSERT query, you have more columns than 2 in the table. You can avoid writing the columns but you have to specify all the values (unless there are default values which you can ignore and those should be always last). For now, add the name and password columns along with the query.

Re: MySQL Query Errors. - Shady - 13.07.2016

Quote:

Originally Posted by Konstantinos

Not sure about the UPDATE query but you should seriously change the way you save. Name does not change unless it is done by the script, so does password; thus those need to be updated when necessary and not every time a player disconnects.

About the INSERT query, you have more columns than 2 in the table. You can avoid writing the columns but you have to specify all the values (unless there are default values which you can ignore and those should be always last). For now, add the name and password columns along with the query.

I'm still learning MySQL, so I'm not great with it. However, I found that I made a mistake on these codes (I wrote ' ' on the queries instead of ` `) and I solved it, but I got another errors and gonna post it below.

Inserting Query Codes - DIALOG:

pawn Код:

case 1:
        {
            if(response)
            {
                new query[126], pName[MAX_PLAYER_NAME];
                GetPlayerName(playerid, pName, sizeof(pName));

                format(query, sizeof(query), "INSERT INTO accounts VALUES (`%s`, `%s`)", pName, inputtext);
                mysql_query(query);
                SendClientMessage(playerid, COLOR_WHITE, "You have successfully registered into the server!");

                SetSpawnInfo(playerid, 1, 299, 1, 2, 3, 1, 0, 0, 0, 0, 0, 0);
                SpawnPlayer(playerid);
            }
        }

Errors while inserting into the DB:

pawn Код:

[18:50:42] CMySQLHandler::Query(INSERT INTO accounts VALUES (`Steve_Malcom`, `passytest`)) - An error has occured. (Error ID: 1054, Unknown column 'Steve_Malcom' in 'field list')

Updating Query Codes:

pawn Код:

stock SavePlayer(playerid)
{
    new query[126], pName[MAX_PLAYER_NAME];
    GetPlayerName(playerid, pName, sizeof(pName));

    GetPlayerPos(playerid, Player[playerid][Position][0], Player[playerid][Position][1], Player[playerid][Position][2]);

    format(query, sizeof(query), "UPDATE accounts SET Name = `%s`, Password = `%s`, PlayerX = `%f`, PlayerY = `%f`, PlayerZ = `%f` WHERE Name = `%s`", pName, Player[playerid][Password], Player[playerid][Position][0], Player[playerid][Position][1], Player[playerid][Position][2], pName);
    mysql_query(query);

    printf("[server] Player ID %d (%s) has been saved into the database.", playerid, pName);
}

Errors while attempt to update in queries:

pawn Код:

[18:50:49] CMySQLHandler::Query(UPDATE accounts SET Name = `Steve_Malcom`, Password = ``, PlayerX = `1958.378295`, PlayerY = `1343.157226`, PlayerZ = `15.3) - An error has occured. (Error ID: 1054, Unknown column 'Steve_Malcom' in 'field list')

Am I getting these errors, because of the other three columns which they are PlayerX, PlayerY and PlayerZ?

Re: MySQL Query Errors. - Konstantinos - 13.07.2016

No, strings need apostrophes around them like '%s' or '%e' (escaped). If using `, it thinks those are columns which are not.

Re: MySQL Query Errors. - Shady - 13.07.2016

Quote:

Originally Posted by Konstantinos

No, strings need apostrophes around them like '%s' or '%e' (escaped). If using `, it thinks those are columns which are not.

PM'ed you more details about the error.

Re: MySQL Query Errors. - PrO.GameR - 13.07.2016

Your update query's problem is your query size, increase it to ~300 at least (not sure how you would save password but lets say 100 characters is that if you hash, each names are 24 each, each floats are 4+1+6 each(precision) that'll take you to 100+48+33 alone, were to lazy to count other characters in your query, but it surely is more than 126.

However if you follow Konstantinoses tips it'll probably be enough (I.E if you use player sqlid as index and only update the pos)

Re: MySQL Query Errors. - SickAttack - 13.07.2016

Update, why would you use an old version?

And you aren't even hashing passwords, shame on you.

Re: MySQL Query Errors. - Shady - 13.07.2016

Quote:

Originally Posted by PrO.GameR

Your update query's problem is your query size, increase it to ~300 at least (not sure how you would save password but lets say 100 characters is that if you hash, each names are 24 each, each floats are 4+1+6 each(precision) that'll take you to 100+48+33 alone, were to lazy to count other characters in your query, but it surely is more than 126.

However if you follow Konstantinoses tips it'll probably be enough (I.E if you use player sqlid as index and only update the pos)

After increasing the number of the queries, It fixed the first problem (which it is saving), but the second problem (inserting) wasn't fixed and still the same.

Quote:

Originally Posted by SickAttack

Update, why would you use an old version?

And you aren't even hashing passwords, shame on you.

The reason I do not update, because the latest version doesn't work on my PC.

I'm gonna hash the passwords later, I just started the game-mode with the Login and Registration only m8.

Re: MySQL Query Errors. - Ghazal - 13.07.2016

Try

pawn Код:

format(query, sizeof(query), "INSERT INTO `accounts` VALUES (`%s`, `%s`);", pName, inputtext);

Re: MySQL Query Errors. - Shady - 13.07.2016

Quote:

Originally Posted by Slotomic

Try

pawn Код:

format(query, sizeof(query), "INSERT INTO `accounts` VALUES (`%s`, `%s`);", pName, inputtext);

Still the same, nothing was changed.

@All; This is the error I'm getting from the Debug.

pawn Код:

[01:10:05] CMySQLHandler::Query(INSERT INTO `accounts` VALUES (`Shady_EG`, `mypass123`);) - An error has occured. (Error ID: 1054, Unknown column 'Shady_EG' in 'field list')

Re: MySQL Query Errors. - DRIFT_HUNTER - 13.07.2016

Increase query length. Your query is getting cut off half way.

EDIT: Second problem. Set default values in mysql table and try to insert like these:
[sql]INSERT INTO `Users` SET `UserName`='%s', `Password`='%s'[/sql]

Dont forget to escape strings for safety.

Re: MySQL Query Errors. - Shady - 13.07.2016

Quote:

Originally Posted by DRIFT_HUNTER

Increase query length. Your query is getting cut off half way.

EDIT: Second problem. Set default values in mysql table and try to insert like these:
[sql]INSERT INTO `Users` SET `UserName`='%s', `Password`='%s'[/sql]

Dont forget to escape strings for safety.

Got an error.

pawn Код:

[01:32:44] CMySQLHandler::Query(INSERT INTO `accounts` SET Name`='Keith_Lawrence', `Password`='123456') - An error has occured. (Error ID: 1064, You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '`='Keith_Lawrence', `Password`='123456'' at line 1)

PM'ed you for more details.

Re: MySQL Query Errors. - SyS - 14.07.2016

look after SET NAME there is an un neccesary `

Re: MySQL Query Errors. - SickAttack - 14.07.2016

Quote:

Originally Posted by Sreyas

look after SET NAME there is an un neccesary `

You made an SQL tutorial yet you don't know how to create an INSERT statement.

INSERT INTO table (columns) VALUES (values);

Re: MySQL Query Errors. - WhiteGhost - 14.07.2016

Quote:

Originally Posted by SickAttack

You made an SQL tutorial yet you don't know how to create an INSERT statement.

INSERT INTO table (columns) VALUES (values);

Lmfaoo i like this guy hes funny.

Anyways if theres anything i learn from Stinged

its that:

INSERT if it doesnt exist and UPDATE when it does.

Re: MySQL Query Errors. - SyS - 14.07.2016

Quote:

Originally Posted by SickAttack

You made an SQL tutorial yet you don't know how to create an INSERT statement.

INSERT INTO table (columns) VALUES (values);

lol u dont understand what I said also insert can be used in this format too read the 3rd page in my tut also I was in mob pc got sicked so I cant type the codes just saying concept

Re: MySQL Query Errors. - SickAttack - 14.07.2016

Quote:

Originally Posted by Sreyas

lol u dont understand what I said also insert can be used in this format too read the 3rd page in my tut also I was in mob pc got sicked so I cant type the codes just saying concept

It's not the correct syntax.

Re: MySQL Query Errors. - DRIFT_HUNTER - 14.07.2016

Quote:

Originally Posted by ShadyEG

Got an error.

pawn Код:

[01:32:44] CMySQLHandler::Query(INSERT INTO `accounts` SET Name`='Keith_Lawrence', `Password`='123456') - An error has occured. (Error ID: 1064, You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '`='Keith_Lawrence', `Password`='123456'' at line 1)

PM'ed you for more details.

Like Sreyas said, you are missing apostrophe ( ` ).

SET `Name`

Re: MySQL Query Errors. - Shady - 14.07.2016

Quote:

Originally Posted by SickAttack

You made an SQL tutorial yet you don't know how to create an INSERT statement.

INSERT INTO table (columns) VALUES (values);

Close this, solved.

Re: MySQL Query Errors. - SyS - 14.07.2016

Quote:

Originally Posted by SickAttack

It's not the correct syntax.

not surely INSERT can also be used like that like I said in ma tutorial it's an alternate method