SA-MP Forums

Go Back   SA-MP Forums > SA-MP Scripting and Plugins > Filterscripts > Includes

Reply
 
Thread Tools Display Modes
Old 18/06/2016, 05:54 PM   #1
Private200
High-roller
 
Private200's Avatar
 
Join Date: May 2012
Posts: 1,176
Reputation: 211
Post Easy MySQL (stop writing long queries)

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

Last edited by Private200; 18/06/2016 at 07:16 PM.
Private200 is offline   Reply With Quote
Old 18/06/2016, 06:04 PM   #2
Gammix
High-roller
 
Gammix's Avatar
 
Join Date: Jan 2015
Location: Canada
Posts: 1,773
Reputation: 858
Default Re: Easy MySQL (stop writing long queries)

No "Get" functions ?
__________________

Follow me on Github: Donate on Paypal:

Gammix is offline   Reply With Quote
Old 18/06/2016, 06:15 PM   #3
Private200
High-roller
 
Private200's Avatar
 
Join Date: May 2012
Posts: 1,176
Reputation: 211
Default Re: Easy MySQL (stop writing long queries)

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.
Private200 is offline   Reply With Quote
Old 18/06/2016, 06:19 PM   #4
Gammix
High-roller
 
Gammix's Avatar
 
Join Date: Jan 2015
Location: Canada
Posts: 1,773
Reputation: 858
Default Re: Easy MySQL (stop writing long queries)

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

Follow me on Github: Donate on Paypal:

Gammix is offline   Reply With Quote
Old 18/06/2016, 06:23 PM   #5
Private200
High-roller
 
Private200's Avatar
 
Join Date: May 2012
Posts: 1,176
Reputation: 211
Default Re: Easy MySQL (stop writing long queries)

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.
Private200 is offline   Reply With Quote
Old 19/06/2016, 01:22 AM   #6
Gasman
Little Clucker
 
Gasman's Avatar
 
Join Date: May 2015
Location: VietNam
Posts: 33
Reputation: 73
Default Re: Easy MySQL (stop writing long queries)

Nice Include. Very Useful but why you not use Github.
Gasman is offline   Reply With Quote
Old 19/06/2016, 01:39 AM   #7
Max_Andolini
Huge Clucker
 
Max_Andolini's Avatar
 
Join Date: Oct 2013
Posts: 318
Reputation: 37
Default Re: Easy MySQL (stop writing long queries)

http://forum.sa-mp.com/showthread.php?t=606930

I am already develop and your include isnt easy.
Max_Andolini is offline   Reply With Quote
Old 19/06/2016, 06:47 AM   #8
Private200
High-roller
 
Private200's Avatar
 
Join Date: May 2012
Posts: 1,176
Reputation: 211
Default Re: Easy MySQL (stop writing long queries)

Quote:
Originally Posted by eco1999 View Post
http://forum.sa-mp.com/showthread.php?t=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.
Private200 is offline   Reply With Quote
Old 19/06/2016, 10:07 AM   #9
Max_Andolini
Huge Clucker
 
Max_Andolini's Avatar
 
Join Date: Oct 2013
Posts: 318
Reputation: 37
Default Re: Easy MySQL (stop writing long queries)

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.
Max_Andolini is offline   Reply With Quote
Old 19/06/2016, 12:04 PM   #10
Private200
High-roller
 
Private200's Avatar
 
Join Date: May 2012
Posts: 1,176
Reputation: 211
Default Re: Easy MySQL (stop writing long queries)

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?
Private200 is offline   Reply With Quote
Reply

Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
[Include] Easy MySQL - Simplifying the usage of MySQL queries! Max_Andolini Includes 118 23/06/2020 10:30 PM
[Include] Easy SQLite: Simplyfing the usage of SQLite queries! ThePhenix Includes 8 16/03/2019 03:22 AM
[Include] Easy MySQL 1.0 - Simplifying the usage of MySQL queries! ThePhenix Includes 92 08/01/2017 04:04 AM
Bug with long queries Banditukas Scripting Help 0 01/03/2015 11:50 AM


All times are GMT. The time now is 10:07 AM.


Powered by vBulletin® Version 3.8.6
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.