[Include] Easy MySQL (stop writing long queries)
#1

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.

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!
Types and definitions

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
     }
}
Basic login/register system using EasyMySQL

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;
}
Credits

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




Download

Pastebin | Mediafire
Reply
#2

No "Get" functions ?
Reply
#3

Quote:
Originally Posted by Gammix
View Post
No "Get" functions ?
Forgot to mention them in the functions list, yeah.

There's MultiGet as well as MultiSet.
Reply
#4

Quote:
Originally Posted by Private200
View Post
Forgot to mention them in the functions list, yeah.

There's MultiGet as well as MultiSet.
You did forgot them as well to mention in the list.

And there should only be Multi(Get/Set) functions, EasyDB is old. A new version will be there.
Reply
#5

Quote:
Originally Posted by Gammix
View Post
You did forgot them as well to mention in the list.

And there should only be Multi(Get/Set) functions, EasyDB is old. A new version will be there.
The list just got updated with the MultiGet and MultiSet functions. The individual entry edit functions are there just for the time being. You can always use MultiSet and MultiGet for individual changes, there's no issue in that.

The example code for both of these functions is in the example script.
Reply
#6

Nice Include. Very Useful but why you not use Github.
Reply
#7

https://sampforum.blast.hk/showthread.php?tid=606930

I am already develop and your include isnt easy.
Reply
#8

Quote:
Originally Posted by eco1999
View Post
https://sampforum.blast.hk/showthread.php?tid=606930

I am already develop and your include isnt easy.
native SQL::GetStringEntry
native SQL::GetStringEntry2
native SQL::GetStringEntryEx
native SQL::GetStringEntryEx2
native SQL::GetIntEntryEx
native SQL::GetIntEntryEx2
native Float:SQL::GetFloatEntryEx
native Float:SQL::GetFloatEntryEx2
native SQL::GetIntEntry
native SQL::GetIntEntry2

Those are from your include and seems like the 10 functions you are using to get the data (supporting multiple columns as well), are all mushed up in 1 simple functions "MultiGet". I'm not willing to start a debate, but please point me out the part you are finding hard to understand and I'll explain it to you.

If you don't know the basics of English, then this might be hard for some to understand. You have the example script in the post as well that you can check out.
Reply
#9

Quote:
Originally Posted by Private200
View Post
native SQL::GetStringEntry
native SQL::GetStringEntry2
native SQL::GetStringEntryEx
native SQL::GetStringEntryEx2
native SQL::GetIntEntryEx
native SQL::GetIntEntryEx2
native Float:SQL::GetFloatEntryEx
native Float:SQL::GetFloatEntryEx2
native SQL::GetIntEntry
native SQL::GetIntEntry2

Those are from your include and seems like the 10 functions you are using to get the data (supporting multiple columns as well), are all mushed up in 1 simple functions "MultiGet". I'm not willing to start a debate, but please point me out the part you are finding hard to understand and I'll explain it to you.

If you don't know the basics of English, then this might be hard for some to understand. You have the example script in the post as well that you can check out.
You see if you look good.

Code:
SQL::TREAD //Describes a table read handle
SQL::MREAD //Describes a multi read handle
SQL::MREAD2 //Describes a multi read handle
SQL::MTREAD //Describes a multi table read handle
Examples:

Code:
new handle = SQL::Open(SQL::TREAD, "hotel"); 
SQL::ReadInt(handle, "id", Hotel[i][id]); 
SQL::ReadFloat(handle, "h_posx", Hotel[i][h_posx]); 
SQL::ReadFloat(handle, "h_posy", Hotel[i][h_posy]); 
SQL::ReadFloat(handle, "h_posz", Hotel[i][h_posz]); 
SQL::Close(handle);//You must close the handle.
Code:
new handle = SQL::Open(SQL::MTREAD, "houses"); 
SQL_GetCallback(handle, i) 
{ 
    SQL::ReadInt(handle, "id", Houses[i][id], i); 
    SQL::ReadFloat(handle, "h_posx", Houses[i][h_posx], i); 
    SQL::ReadFloat(handle, "h_posy", Houses[i][h_posy], i); 
    SQL::ReadFloat(handle, "h_posz", Houses[i][h_posz], i); 
} 
SQL::Close(handle);//You must close the handle.
etc.
Reply
#10

Quote:
Originally Posted by eco1999
View Post
You see if you look good.

Code:
SQL::TREAD //Describes a table read handle
SQL::MREAD //Describes a multi read handle
SQL::MREAD2 //Describes a multi read handle
SQL::MTREAD //Describes a multi table read handle
Examples:

Code:
new handle = SQL::Open(SQL::TREAD, "hotel"); 
SQL::ReadInt(handle, "id", Hotel[i][id]); 
SQL::ReadFloat(handle, "h_posx", Hotel[i][h_posx]); 
SQL::ReadFloat(handle, "h_posy", Hotel[i][h_posy]); 
SQL::ReadFloat(handle, "h_posz", Hotel[i][h_posz]); 
SQL::Close(handle);//You must close the handle.
Code:
new handle = SQL::Open(SQL::MTREAD, "houses"); 
SQL_GetCallback(handle, i) 
{ 
    SQL::ReadInt(handle, "id", Houses[i][id], i); 
    SQL::ReadFloat(handle, "h_posx", Houses[i][h_posx], i); 
    SQL::ReadFloat(handle, "h_posy", Houses[i][h_posy], i); 
    SQL::ReadFloat(handle, "h_posz", Houses[i][h_posz], i); 
} 
SQL::Close(handle);//You must close the handle.
etc.
Good for you. Now, what's hard to understand in my script, please?
Reply


Forum Jump:


Users browsing this thread: 2 Guest(s)