[Include] Easy MySQL - Simplifying the usage of MySQL queries!

[Include] Easy MySQL - Simplifying the usage of MySQL queries! - 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: Filterscripts (https://sampforum.blast.hk/forumdisplay.php?fid=17)
+---- Forum: Includes (https://sampforum.blast.hk/forumdisplay.php?fid=83)
+---- Thread: [Include] Easy MySQL - Simplifying the usage of MySQL queries! (/showthread.php?tid=606930)

Pages: 1 2 3 4

Easy MySQL - Simplifying the usage of MySQL queries! - Max_Andolini - 11.05.2016

Easy - MySQL [V3.8]

Originally, created by ThePhenix (ThreeKingz), currently maintaining and developing this include.

Old thread:

Overview:

This include allows you to create and manage several MySQL queries in a very simplified way, it means that you won't require to write queries on your own for the most part.

Login/Register example:

PHP Code:



#include <a_samp>

#include <easy-mysql>


/*

 *  Simple register/login system using easy_mysql.inc!

*/


#define mysql_host    "localhost"

#define mysql_user    "root"

#define mysql_db        "server"

#define mysql_pass         ""

#define mysql_debugging_enabled             (true)

#define SQL_PLAYERS_TABLE                    "players"

#define DIALOG_LOGIN 0

#define DIALOG_REGISTER 1


enum p_info

{

    p_id,

    p_name[24],

    p_password[64],

    p_score,

    Float:p_posx,

    Float:p_posy,

    Float:p_posz,

    p_loggedin

};

new UserInfo[MAX_PLAYERS][p_info];

    

stock ret_pName(playerid)

{

    new name[24];

    GetPlayerName(playerid, name, sizeof(name));

    return name;

}



#define IsPlayerRegistered(%0)    (SQL::RowExistsEx(SQL_PLAYERS_TABLE, "p_name", ret_pName(%0)))



main()

{

    

}

public OnGameModeInit()

{

    //Connecting to the MySQL database, default connection handle.

    SQL::Connect(mysql_host, mysql_user, mysql_pass, mysql_db);

    //Checking if the table 'players' exists

    

    //Checking if the table 'players' exists

    if(!SQL::ExistsTable(SQL_PLAYERS_TABLE))

    {

        //If not, then create a table called 'players'.

        new handle = SQL::Open(SQL::CREATE, SQL_PLAYERS_TABLE); //Opening a valid handle to create a table called 'players'

        SQL::AddTableColumn(handle, "p_id", SQL_TYPE_INT, 11, false, true); //Setting auto-increment for this field.

        SQL::AddTableColumn(handle, "p_name", SQL_TYPE_VCHAR, 24);

        SQL::AddTableColumn(handle, "p_password", SQL_TYPE_VCHAR, 64);

        SQL::AddTableColumn(handle, "p_score", SQL_TYPE_INT);

        SQL::AddTableColumn(handle, "p_posx", SQL_TYPE_FLOAT);

        SQL::AddTableColumn(handle, "p_posy", SQL_TYPE_FLOAT);

        SQL::AddTableColumn(handle, "p_posz", SQL_TYPE_FLOAT);

        SQL::Close(handle);//Closing the previous opened handle.

    }

    return 1;

}


public OnPlayerConnect(playerid)

{

    UserInfo[playerid][p_loggedin] = 0; UserInfo[playerid][p_score] = 0;  UserInfo[playerid][p_posx] = 1958.3783;

    UserInfo[playerid][p_posy] = 1343.1572; UserInfo[playerid][p_posz] = 15.3746; 



    if(SQL::RowExistsEx(SQL_PLAYERS_TABLE, "p_name", ret_pName(playerid))) //Check if the name is registered in the database

    {

        //Get the player password and unique ID.

        new handle = SQL::OpenEx(SQL::READ, "players", "p_name", ret_pName(playerid));

        SQL::ReadString(handle, "p_password", UserInfo[playerid][p_password], 64);

        SQL::ReadInt(handle, "p_id", UserInfo[playerid][p_id]);

        SQL::Close(handle);

        //Show the login dialog

        ShowPlayerDialog(playerid, DIALOG_LOGIN, DIALOG_STYLE_PASSWORD, "{0080FF}Login", "Please input your password below to log in.", "Login", "Exit");

    }

    else

    {

        //If not registered, then show the register DIALOG.

        ShowPlayerDialog(playerid, DIALOG_REGISTER, DIALOG_STYLE_PASSWORD, "{0080FF}Register", "Please input a password below to register in.", "Login", "Exit");

    }

    return 1;

}


public OnPlayerSpawn(playerid)

{

    SetPlayerPos(playerid, UserInfo[playerid][p_posx], UserInfo[playerid][p_posy], UserInfo[playerid][p_posz]);

    return 1;

}


public OnPlayerDisconnect(playerid, reason)

{

    if(UserInfo[playerid][p_loggedin] == 1)

    {

        //Save the player data.

        GetPlayerPos(playerid, UserInfo[playerid][p_posx], UserInfo[playerid][p_posy], UserInfo[playerid][p_posz]);

        new handle = SQL::Open(SQL::UPDATE, SQL_PLAYERS_TABLE, "p_id", UserInfo[playerid][p_id]); //update WHERE p_id = UserInfo[playerid][p_id]

        SQL::WriteInt(handle, "p_score", GetPlayerScore(playerid));

        SQL::WriteFloat(handle, "p_posx", UserInfo[playerid][p_posx]);

        SQL::WriteFloat(handle, "p_posy", UserInfo[playerid][p_posy]);

        SQL::WriteFloat(handle, "p_posz", UserInfo[playerid][p_posz]);

        SQL::Close(handle);

    }

    return 1;

}


public OnDialogResponse(playerid, dialogid, response, listitem, inputtext[])

{

    switch(dialogid)

    {

        case DIALOG_REGISTER:

        {

            if(!response) return Kick(playerid);

            if(strlen(inputtext) < 5)

            {

                ShowPlayerDialog(playerid, DIALOG_REGISTER, DIALOG_STYLE_PASSWORD, "{0080FF}Register", "Please input a password below to register in.", "Login", "Exit");

                return 1;

            }

            SHA256_PassHash(inputtext, "", UserInfo[playerid][p_password], 64);

            new handle = SQL::Open(SQL::INSERT, SQL_PLAYERS_TABLE);

            SQL::ToggleAutoIncrement(handle, true);//Toggles auto increment, SQL::Close will return cache_insert_id();

            SQL::WriteString(handle, "p_name", ret_pName(playerid));

            SQL::WriteString(handle, "p_password", UserInfo[playerid][p_password]);

            SQL::WriteInt(handle, "p_score", 0);

            SQL::WriteFloat(handle, "p_posx", 0.0);

            SQL::WriteFloat(handle, "p_posy", 0.0);

            SQL::WriteFloat(handle, "p_posz", 0.0);

            UserInfo[playerid][p_id] = SQL::Close(handle); 

            SendClientMessage(playerid, -1, "Successfully registered in!");

            UserInfo[playerid][p_loggedin] = 1;

        }

        case DIALOG_LOGIN:

        {

            if(!response) Kick(playerid); 

            new hash[64];

            SHA256_PassHash(inputtext, "", hash, 64);

            if(!strcmp(hash, UserInfo[playerid][p_password]))

            { 

                //Load player data

                new handle = SQL::Open(SQL::READ, SQL_PLAYERS_TABLE, "p_id", UserInfo[playerid][p_id]);

                SQL::ReadInt(handle, "p_score", UserInfo[playerid][p_score]);

                SQL::ReadFloat(handle, "p_posx", UserInfo[playerid][p_posx]);

                SQL::ReadFloat(handle, "p_posy", UserInfo[playerid][p_posy]);

                SQL::ReadFloat(handle, "p_posz", UserInfo[playerid][p_posz]);

                SQL::Close(handle);//You must close the handle.

                SetPlayerScore(playerid, UserInfo[playerid][p_score]);

                UserInfo[playerid][p_loggedin] = 1;

                SendClientMessage(playerid, -1, "Successfully logged in!");

                

            }

            else 

            {

                ShowPlayerDialog(playerid, DIALOG_LOGIN, DIALOG_STYLE_PASSWORD, "{0080FF}Login", "Please input your password below to log in.", "Login", "Exit");

            }

        }

    }

    return 1;

}

Credits:

- ThePhenix (ThreeKingz) (initial developer)
- Max_Andolini (latest developer)

----------------------------------------------------------------------------------------------------------------------
Download:

Download V3.8

The latest MySQL plugin (R41-4) is required to use this include.

If you have any questions or suggestions, comment below.

Re: Easy MySQL - Simplifying the usage of MySQL queries! - Evoturk - 11.05.2016

Thanks for this

Re: Easy MySQL - Simplifying the usage of MySQL queries! - Unrea1 - 11.05.2016

new things have version 3?

Re: Easy MySQL - Simplifying the usage of MySQL queries! - Max_Andolini - 11.05.2016

Quote:

Originally Posted by LatinZ

new things have version 3?

Table Read, Multi Table Read, Multi Read, Multi ReadEx etc.

Examine the code for more.

Re: Easy MySQL - Simplifying the usage of MySQL queries! - Sensation - 11.05.2016

Зok gьzel, bцyle devam kardeş..

Re: Easy MySQL - Simplifying the usage of MySQL queries! - Max_Andolini - 11.05.2016

Quote:

Originally Posted by Sensation

Зok gьzel, bцyle devam kardeş..

I do not know who you are but thank you krdş

Re: Easy MySQL - Simplifying the usage of MySQL queries! - Sensation - 11.05.2016

Quote:

Originally Posted by eco1999

Kimsin krdş bilmiyorumda teşekkьr ederim

What is your gtaturkish nickname bro?

Re: Easy MySQL - Simplifying the usage of MySQL queries! - Sensation - 11.05.2016

<removed>

Re: Easy MySQL - Simplifying the usage of MySQL queries! - Luicy. - 19.06.2016

Not really a bump but, Great work buddy.
Finally I'll move over to MySQL. I'm gone from INI now.
You just received a rep mate. x3

Re: Easy MySQL - Simplifying the usage of MySQL queries! - Max_Andolini - 19.06.2016

Quote:

Originally Posted by Meller

Not really a bump but, Great work buddy.
Finally I'll move over to MySQL. I'm gone from INI now.
You just received a rep mate. x3

Thank you.

The new version was released.

Description:

Bug fixes and improvements.

https://github.com/MaxAndolini/easy-...easy-mysqlv3.1

Re: Easy MySQL - Simplifying the usage of MySQL queries! - pawnuser - 21.06.2016

Russian characters are replaced with "

??". How to fix?

Re: Easy MySQL - Simplifying the usage of MySQL queries! - Max_Andolini - 21.06.2016

Quote:

Originally Posted by pawnuser

Russian characters are replaced with "

??". How to fix?

Use MySQL Charset.

Re: Easy MySQL - Simplifying the usage of MySQL queries! - pawnuser - 21.06.2016

I will perform this function, it recognizes the name on the account number in the database. (encoding declared at the beginning of fashion after the database connection)

stock GetAccountName(account)
{
new name[MAX_PLAYER_NAME];
if(SQL::RowExists("accounts", "ID", account))
{
new handle = SQL::Open(SQL::READ, "accounts", "ID", account);
SQL::ReadString(handle, "Name", name, MAX_PLAYER_NAME);
SQL::Close(handle);
}
return name;
}

The result of "return" is recognized in the "

??"

Re: Easy MySQL - Simplifying the usage of MySQL queries! - Max_Andolini - 21.06.2016

Quote:

Originally Posted by pawnuser

I will perform this function, it recognizes the name on the account number in the database. (encoding declared at the beginning of fashion after the database connection)

stock GetAccountName(account)
{
new name[MAX_PLAYER_NAME];
if(SQL::RowExists("accounts", "ID", account))
{
new handle = SQL::Open(SQL::READ, "accounts", "ID", account);
SQL::ReadString(handle, "Name", name, MAX_PLAYER_NAME);
SQL::Close(handle);
}
return name;
}

The result of "return" is recognized in the "

??"

Use This

Code:

stock GetAccountName(account)
{
    new name[MAX_PLAYER_NAME];
    if(SQL::RowExists("accounts", "ID", account))
    {
        SQL::GetStringEntry("accounts", "Name", "ID", account, name, MAX_PLAYER_NAME);
        return name;
    }
    return 0;
}

Re: Easy MySQL - Simplifying the usage of MySQL queries! - pawnuser - 21.06.2016

LOL what?

I have mysql encoding settings(last):
"mysql_tquery(mysqlConnection, "SET NAMES cp1251", "", "");"
"mysql_tquery(mysqlConnection, "SET CHARACTER SET cp1251", "", "");"
And now I have:
" mysql_tquery(mysqlConnection, "SET NAMES cp1251", "", "");
mysql_tquery(mysqlConnection, "SET CHARACTER SET cp1251", "", "");
mysql_query(mysqlConnection, "SET NAMES cp1251");
mysql_query(mysqlConnection, "SET CHARACTER SET cp1251"); "
And it works!
It turns out, we specify the encoding for different queries, functions?
For mysql_tquery and mysql_query separately!
Problem was solved, thanks for a simplified version of my function!

-----------------------------FIX-------------
I've used for all requests this "mysql_tquery" function and so I have the encoding for the use of this function. "mysql_tquery" The script uses this function to query "mysql_query" means and coding should be for this function

Re: Easy MySQL - Simplifying the usage of MySQL queries! - Max_Andolini - 21.06.2016

Quote:

Originally Posted by pawnuser

LOL what?

I have mysql encoding settings(last):
"mysql_tquery(mysqlConnection, "SET NAMES cp1251", "", "");"
"mysql_tquery(mysqlConnection, "SET CHARACTER SET cp1251", "", "");"
And now I have:
" mysql_tquery(mysqlConnection, "SET NAMES cp1251", "", "");
mysql_tquery(mysqlConnection, "SET CHARACTER SET cp1251", "", "");
mysql_query(mysqlConnection, "SET NAMES cp1251");
mysql_query(mysqlConnection, "SET CHARACTER SET cp1251"); "
And it works!
It turns out, we specify the encoding for different queries, functions?
For mysql_tquery and mysql_query separately!
Problem was solved, thanks for a simplified version of my function!

-----------------------------FIX-------------
I've used for all requests this "mysql_tquery" function and so I have the encoding for the use of this function. "mysql_tquery" The script uses this function to query "mysql_query" means and coding should be for this function

mysql_set_charset("cp1251");

Put this after connect.

Re: Easy MySQL - Simplifying the usage of MySQL queries! - pawnuser - 21.06.2016

Well, I have a different, and works well and good))

Re: Easy MySQL - Simplifying the usage of MySQL queries! - Stones - 21.06.2016

Thank you very much for this!

Re: Easy MySQL - Simplifying the usage of MySQL queries! - Max_Andolini - 05.09.2016

New version released.

Changes:

MySQL R40 compatibility.

Re: Easy MySQL - Simplifying the usage of MySQL queries! - morris91 - 06.09.2016

Im trying to make it so on attachment edit, it adds the floats etc.. to the row that correctly corresponds to the Username & Slot.

Code:

public OnPlayerEditAttachedObject(playerid, response, index, modelid, boneid, Float:fOffsetX, Float:fOffsetY, Float:fOffsetZ, Float:fRotX, Float:fRotY, Float:fRotZ, Float:fScaleX, Float:fScaleY, Float:fScaleZ)
{
      new handle = SQL::OpenEx(SQL::UPDATE2, ""ATTACHMENTS"", "Username", pNick(playerid), "Slot", index);

Getting a argument 6 so guessing im missing something out.
Anyone can help? il be greatfull