18.06.2016, 17:54
(
Last edited by Private200; 18/06/2016 at 07:16 PM.
)
Easy MySQL
Build 1.2
If you check my old scripts, you will see that I have been working with EasyDB in most of them. Being that EasyDB is an easy-to-use SQLite include, I got used to it. I was developing my World War Role Play gamemode and thought of MySQL UCP. First I was like it would be good to have a SQLite include, but reading some reviews there were about it on some forums I saw around (including these), there were many downsides in using it. I asked Gammix about a way around and he told me about his SQL include, with which you could use both SQLite and MySQL at the same time. I converted EasyDB and made it compatible with his include. Once done, I saw that there were problems with MySQL. Connection was successful, but nothing was being executed for some unknown reason. Build 1.2
That's when I thought that there was nothing else to do than start converting the script fully in MySQL, but before doing so, it was worth it giving a try into converting EasyDB itself into MySQL. I started developing this today and my main mission was changing the main functions into MySQL.
I've used both threaded queries and the normal query. Not being the best in MySQL, I've used the normal query in some parts where I could've used threaded instead and I apologize if so. Just tell me which part can be written differently and I will.
This include will not just help you to write codes easily without writing long queries at all, but will also help you if you want to convert from EasyDB. I'll make sure to write such a tutorial in case any of you asks me to do so.
However, let's get into what we're really doing this for. I don't think any of you wants to read my stories, so yeah.
Functions
Quote:
|
native MySQLConnect(const host[] = MYSQL_SERVER, const user[] = MYSQL_UID, const database[] = MYSQL_DATABASE, const password[] = MYSQL_PASSWORD, port = 3306, bool:autoreconnect = true, pool_size = 2) - You are able to define everything yourself. If you are already using MYSQL_SERVER, MYSQL_UID, MYSQL_DATABASE and/or MYSQL_PASSWORD, make sure to define them before the include loads in order for the include to load them in the script. In case you want to change just one of them, use underlines to use pre-defined settings, for example (changing just user): MySQLConnect(_, "Private200", _, _); native MySQLDisconnect(); - Describes its meaning. Will automatically disconnect from the MySQL server. native CreateRow(table[], const column[], { _,Float }:...) - Creates a new row in the database, inside the table you select. Note: Returns the newly created ID (check example code for usage) native RetrieveKey(table[], key[], keys[] = "", size = sizeof (keys), const column[], { _,Float }:...) - Retrieves the key for the specified column data. You are also able to retrieve multiple keys by using arrays (check example code for usage, single array used) Note: Returns INVALID_KEY if not key is found. native VerifyTable(table[], key[], bool:ai = true) - Checks if a table exists, if it doesen't exist, a new one will be created. You can select if the primary key will have auto increment as attribute or not, by setting ai to false. Note: Returns 0 if table will not be created, 1 if table will be successfully created. native VerifyColumn(table[], column[], DBType:type, len, { _,Float }:...) - Checks if a column exists in a specific table. Usage will be shown below. native SetFloatEntry(table[], key[], keys, const column[], Float:value) - Updates the float value for an entry in a specific table, that has a specific key. native SetStringEntry(table[], key[], keys, const column[], const value[]) - Updates the string value for an entry in a specific table, that has a specific key. native SetIntEntry(table[], key[], keys, const column[], value) - Updates the integer value for an entry in a specific table, that has a specific key. native bool:MultiGet(table[], key[], keys, const fmat[], {_, Float}:...) - You can get multiple data's from the MySQL, by using the table, key and the database id of the entry you want to retrieve data from. Example is shown in the example code below. native bool:MultiSet(table[], key[], keys, const fmat[], {_, Float}:...) - You can set multiple data's from the MySQL, by using the table, key and the database id of the entry you want to retrieve data from. Example is shown in the example code below. Note: Both of these functions return false if key is invalid and true if data edit is successful. NOTE: You are still able to use ALL predefined MySQL functions without any issue! |
TYPE_NUMBER - Creates an integer type column.
TYPE_FLOAT - Creates a float type column.
TYPE_STRING - Creates a string type column.
MySQL_INVALID - Used in place of return 0.
MySQL_INVALID_KEY - Used when a non existent key is trying to be retrieved or edited.
Usage of some functions
Grabbing multiple keys from RetrieveKey function:
Code:
// set the length of the key (how many keys you want to retrieve)
new key[5];
// Change tablename and tablekey to the names of you want to.
RetrieveKey(tablename, tablekey, key, sizeof key, column, column value);
// Column and column value must be changed to the data you want to use to retrieve the info
for(new i; i < sizeof key; i ++)
{
if(key != MySQL_INVALID_KEY)
{
// what you want to do with those available keys
}
}
Code:
#include <a_samp>
#include <easymysql>
#define DIALOG_LOGIN 1
#define DIALOG_REGISTER 2
enum playerinfo {
pPass,
pScore,
pMoney
}
new PlayerInfo[MAX_PLAYERS][playerinfo];
#define cTable "Characters2"
public OnFilterScriptInit()
{
MySQLConnect();
VerifyTable(cTable, "id");
VerifyColumn(cTable, "Password", TYPE_STRING, 129, "");
VerifyColumn(cTable, "Username", TYPE_STRING, 24, "");
VerifyColumn(cTable, "Money", TYPE_NUMBER, 11, 0);
VerifyColumn(cTable, "Score", TYPE_NUMBER, 11, 0);
return 1;
}
public OnFilterScriptExit()
{
MySQLDisconnect();
return 1;
}
public OnPlayerConnect(playerid)
{
new name[MAX_PLAYER_NAME];
GetPlayerName(playerid, name, sizeof name);
new id = RetrieveKey(cTable, "id", _, _, "Username", name);
if(id == MySQL_INVALID_KEY) // Checking if account doesen't exist
{
ShowPlayerDialog(playerid, DIALOG_REGISTER, DIALOG_STYLE_INPUT, "Register", "Please enter your password:", "Register", "Cancel");
}
else
{
MultiGet(cTable, "id", id, "s",
"Password", PlayerInfo[playerid][pPass]);
ShowPlayerDialog(playerid, DIALOG_LOGIN, DIALOG_STYLE_INPUT, "Login", "Please enter your password:", "Login", "Cancel");
}
return 1;
}
public OnDialogResponse(playerid, dialogid, response, listitem, inputtext[])
{
if(dialogid == DIALOG_REGISTER)
{
if(response)
{
if(!strcmp(inputtext, PlayerInfo[playerid][pPass]))
{
new name[MAX_PLAYER_NAME];
GetPlayerName(playerid, name, sizeof name);
new id = CreateRow(cTable, "Username", name); // Creating a new account with username and assigning its id to "id"
MultiSet(cTable, "id", id, "sii",
"Password", inputtext,
"Score", 100, // An example of how you can set his starting money and score, including password
"Money", 15000);
SendClientMessage(playerid, -1, "You are now registered!");
}
else Kick(playerid); // Player put wrong password
}
else
{
Kick(playerid); // Player didn't want to login
}
return 1;
}
if(dialogid == DIALOG_LOGIN)
{
if(response)
{
new name[MAX_PLAYER_NAME];
GetPlayerName(playerid, name, sizeof name);
new id = RetrieveKey(cTable, "id", _, _, "Username", name);
MultiGet(cTable, "id", id, "ii",
"Score", PlayerInfo[playerid][pScore], // Loading player score and successfully logging him in
"Money", PlayerInfo[playerid][pMoney]);
SendClientMessage(playerid, -1, "You are now logged in!");
}
else
{
Kick(playerid); // Player didn't want to login
}
return 1;
}
return 0;
}
• Gamminx for his EasyDB include (where most of these functions are taken and edited from)
• BlueG for his MySQL plugin
• SA:MP team for everything used in this project.
