[theonethatownz]

I am trying to create a register system with mySQL, how would I split up my formats?

This is original:

pawn Код:

mysql_format(mysql, query, sizeof(query), "INSERT INTO `accounts` (`Name`, `Password`, `IP`, `Admin`, `VIP`, `Caps`, `PosX`, `PosY`, `PosZ`, `PosA`, `Health, `Armour`, `Skin`, `Thirst`, `Hunger`, `Banned`, `Bans`, `Kicks`, `Warns`, `Muted`, `Quests`, `TimeH`, `TimeM`, `Infection`, `Storage`, `Item1`, `Item2`, `Item3`, `Item4`, `Item5`, `Item6`, `Item7`, `Item8`, `Item9`, `Item10`)) VALUES ('%e', '%e', '%e', 0, 0, 0, %f, %f, %f, %f, 100, 0, 23, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0)", playername, Player[playerid][Password], playerip, SPAWN_X, SPAWN_Y, SPAWN_Z, SPAWN_A);

The only way I can think, but doesn't work or insert data is:

pawn Код:

mysql_format(mysql, query, sizeof(query), "INSERT INTO `accounts` (`Name`, `Password`, `IP`, `Admin`, `VIP`, `Caps`, `PosX`, `PosY`, `PosZ`, `PosA`, `Health, `Armour`, `Skin`, `Thirst`, `Hunger`, `Banned`, `Bans`, `Kicks`, `Warns`, `Muted`, `Quests`) VALUES ('%e', '%e', '%e', 0, 0, 0, %f, %f, %f, %f, 100, 0, 23, 0, 0, 0, 0, 0, 0, 0, 0)", playername, Player[playerid][Password], playerip, SPAWN_X, SPAWN_Y, SPAWN_Z, SPAWN_A); 
            mysql_format(mysql, query, sizeof(query), "INSERT INTO `accounts` (`TimeH`, `TimeM`, `Infection`, `Storage`, `Item1`, `Item2`, `Item3`, `Item4`, `Item5`, `Item6`, `Item7`, `Item8`, `Item9`, `Item10`) VALUES (0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0)");

Would I have to use two queries?

Original:

pawn Код:

mysql_tquery(mysql, query, "OnAccountRegister", "i", playerid);

New:

pawn Код:

mysql_tquery(mysql, query1, "", "i", playerid); 
mysql_tquery(mysql, query, "OnAccountRegister", "i", playerid);

[SickAttack]

Use format + strcat. Example:

pawn Код:

new query[256], temp[128], player_name[MAX_PLAYER_NAME];
GetPlayerName(playerid, player_name, MAX_PLAYER_NAME);

format(temp, sizeof(temp), "SELECT `%s`,`%s`,`%s`,`%s`,`%s`");
strcat(query, temp);

format(temp, sizeof(temp), ",`%s`,`%s`,`%s`,`%s`,`%s` FROM `%s`");
strcat(query, temp);

format(temp, sizeof(temp), " WHERE `%s` = '%q'", player_name);
strcat(query, temp);

Note that the specifier "%q" in format, is used to escape a string.

[zPain]

You can also assign default values to some fields. It would shorten the query considerably.

[theonethatownz]

Quote:

Originally Posted by zPain You can also assign default values to some fields. It would shorten the query considerably.

Would I do this on the mysql phpmyadmin part? How would that work with updating?

[zPain]

http://www.w3schools.com/sql/sql_default.asp

You can do it on phpmyadmin or execute an ALTER TABLE statement.

As for updating, if you have a large amount of variables whose value you need to save, which will result in a very large query, you can write a stored procedure and then call it on your sa-mp script.

[theonethatownz]

Okay thanks.

I have an issue of it not adding my name, password or IP when creating a row, on quitting it updates other variables.

This is the error

Quote:

[19:00:56] [DEBUG] mysql_format - connection: 1, len: 2024, format: "INSERT INTO `accounts` (`Name`, `Password`, `IP`, `Admin`, `VIP`, `Caps`, `PosX`, `PosY`, `PosZ`, `PosA`, `Health, `Armour`, `Sk..." [19:00:56] [DEBUG] mysql_tquery - connection: 1, query: "INSERT INTO `accounts` (`Name`, `Password`, `IP`, `Admin`, `VIP`", callback: "(null)", format: "i" [19:00:56] [DEBUG] mysql_format - connection: 1, len: 2024, format: "INSERT INTO `accounts` (`TimeH`, `TimeM`, `Infection`, `Storage`, `Item1`, `Item2`, `Item3`, `Item4`, `Item5`, `Item6`, `Item7`,..." [19:00:56] [DEBUG] mysql_tquery - connection: 1, query: "INSERT INTO `accounts` (`TimeH`, `TimeM`, `Infection`, `Storage`", callback: "OnAccountRegister", format: "i" [19:00:56] [DEBUG] CMySQLQuery::Execute[] - starting query execution [19:00:56] [ERROR] CMySQLQuery::Execute[] - (error #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 'Armour`, `Skin`, `Thirst`, `Hunger`, `Banned`, `Bans`, `Kicks`, `Warns`, `Muted`' at line 1 [19:00:56] [DEBUG] CMySQLQuery::Execute[] - error will be triggered in OnQueryError

[Konstantinos]

After the Health field, you forgot the grave accent character -> `

As for it though, it's not necessary so if deleting all those around fields' name you can reduce the query along with the default values zPain mentioned.