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

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(playeridnamesizeof(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_hostmysql_usermysql_passmysql_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::CREATESQL_PLAYERS_TABLE); //Opening a valid handle to create a table called 'players'
        
SQL::AddTableColumn(handle"p_id"SQL_TYPE_INT11falsetrue); //Setting auto-increment for this field.
        
SQL::AddTableColumn(handle"p_name"SQL_TYPE_VCHAR24);
        
SQL::AddTableColumn(handle"p_password"SQL_TYPE_VCHAR64);
        
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] = 0UserInfo[playerid][p_score] = 0;  UserInfo[playerid][p_posx] = 1958.3783;
    
UserInfo[playerid][p_posy] = 1343.1572UserInfo[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(playeridDIALOG_LOGINDIALOG_STYLE_PASSWORD"{0080FF}Login""Please input your password below to log in.""Login""Exit");
    }
    else
    {
        
//If not registered, then show the register DIALOG.
        
ShowPlayerDialog(playeridDIALOG_REGISTERDIALOG_STYLE_PASSWORD"{0080FF}Register""Please input a password below to register in.""Login""Exit");
    }
    return 
1;
}
public 
OnPlayerSpawn(playerid)
{
    
SetPlayerPos(playeridUserInfo[playerid][p_posx], UserInfo[playerid][p_posy], UserInfo[playerid][p_posz]);
    return 
1;
}
public 
OnPlayerDisconnect(playeridreason)
{
    if(
UserInfo[playerid][p_loggedin] == 1)
    {
        
//Save the player data.
        
GetPlayerPos(playeridUserInfo[playerid][p_posx], UserInfo[playerid][p_posy], UserInfo[playerid][p_posz]);
        new 
handle SQL::Open(SQL::UPDATESQL_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(playeriddialogidresponselistiteminputtext[])
{
    switch(
dialogid)
    {
        case 
DIALOG_REGISTER:
        {
            if(!
response) return Kick(playerid);
            if(
strlen(inputtext) < 5)
            {
                
ShowPlayerDialog(playeridDIALOG_REGISTERDIALOG_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::INSERTSQL_PLAYERS_TABLE);
            
SQL::ToggleAutoIncrement(handletrue);//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(!
responseKick(playerid); 
            new 
hash[64];
            
SHA256_PassHash(inputtext""hash64);
            if(!
strcmp(hashUserInfo[playerid][p_password]))
            { 
                
//Load player data
                
new handle SQL::Open(SQL::READSQL_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(playeridUserInfo[playerid][p_score]);
                
UserInfo[playerid][p_loggedin] = 1;
                
SendClientMessage(playerid, -1"Successfully logged in!");
                
            }
            else 
            {
                
ShowPlayerDialog(playeridDIALOG_LOGINDIALOG_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.
Reply
#2

Thanks for this
Reply
#3

new things have version 3?
Reply
#4

Quote:
Originally Posted by LatinZ
View Post
new things have version 3?
Table Read, Multi Table Read, Multi Read, Multi ReadEx etc.

Examine the code for more.
Reply
#5

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

Quote:
Originally Posted by Sensation
View Post
Зok gьzel, bцyle devam kardeş..
I do not know who you are but thank you krdş
Reply
#7

Quote:
Originally Posted by eco1999
View Post
Kimsin krdş bilmiyorumda teşekkьr ederim
What is your gtaturkish nickname bro?
Reply
#8

<removed>
Reply
#9

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
Reply
#10

Quote:
Originally Posted by Meller
View Post
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
Reply
#11

Russian characters are replaced with "??". How to fix?
Reply
#12

Quote:
Originally Posted by pawnuser
View Post
Russian characters are replaced with "??". How to fix?
Use MySQL Charset.
Reply
#13

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 "??"
Reply
#14

Quote:
Originally Posted by pawnuser
View Post
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;
}
Reply
#15

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
Reply
#16

Quote:
Originally Posted by pawnuser
View Post
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.
Reply
#17

Well, I have a different, and works well and good))
Reply
#18

Thank you very much for this!
Reply
#19

New version released.

Changes:

MySQL R40 compatibility.
Reply
#20

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
Reply


Forum Jump:


Users browsing this thread: 1 Guest(s)