[Shady]

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)

[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.

[Shady]

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?

[Konstantinos]

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

[Shady]

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.

[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)

[SickAttack]

Update, why would you use an old version?

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

[Shady]

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.

[Ghazal]

Try

pawn Код:

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

[Shady]

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')

[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.

[Shady]

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.

[SyS]

look after SET NAME there is an un neccesary `

[SickAttack]

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);

[WhiteGhost]

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.

[SyS]

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

[SickAttack]

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.

[DRIFT_HUNTER]

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`

[Shady]

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.

[SyS]

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