MySQL Formats - Printable Version

MySQL Formats - 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 Formats (/showthread.php?tid=603324)

MySQL Formats - theonethatownz - 21.03.2016

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

Re: MySQL Formats - SickAttack - 21.03.2016

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.

Re: MySQL Formats - zPain - 21.03.2016

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

Re: MySQL Formats - theonethatownz - 21.03.2016

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?

Re: MySQL Formats - zPain - 21.03.2016

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.

Re: MySQL Formats - theonethatownz - 21.03.2016

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

Re: MySQL Formats - Konstantinos - 22.03.2016

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.