SA-MP Forums

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

Reply
 
Thread Tools Display Modes
Old 27/09/2015, 10:55 PM   #1
ThePhenix
Gangsta
 
ThePhenix's Avatar
 
Join Date: May 2012
Posts: 822
Reputation: 157
Default Easy MySQL 3.5 - Simplifying the usage of MySQL queries!

Easy - MySQL [V3.5]



NEW THREAD by Max_Andolini




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_INT11true); //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.5

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


If you have any questions or suggestions, comment below.

Last edited by ThePhenix; 26/11/2017 at 08:14 PM.
ThePhenix is offline   Reply With Quote
Old 27/09/2015, 11:24 PM   #2
Face9000
Banned
 
Join Date: Jan 2010
Location: Pigland
Posts: 3,444
Reputation: 190
Default Re: Easy MySQL 1.0 - Simplifying the usage of MySQL queries!

Awesome! This system is perfect for MySQL newbies and for who is studyng this system from like 1 years and started to learn just now (like me )
Face9000 is offline   Reply With Quote
Old 27/09/2015, 11:24 PM   #3
ExPLORE
Huge Clucker
 
ExPLORE's Avatar
 
Join Date: Nov 2013
Posts: 397
Reputation: 45
Default Re: Easy MySQL 1.0 - Simplifying the usage of MySQL queries!

OMG

Good Job!!!
__________________
#LosAbreOPCRPG

ExPLORE is offline   Reply With Quote
Old 28/09/2015, 12:04 AM   #4
Kaperstone
Banned
 
Join Date: May 2011
Location: Russia
Posts: 3,336
Reputation: 824
Default Re: Easy MySQL 1.0 - Simplifying the usage of MySQL queries!

Would be more understandable in my opinion to call the query launch "SQL::Launch" or "SQL::Fire" instead of "close"

Quote:
Originally Posted by ThePhenix View Post
- No speed reductions in comparison to normal queries.
and it should actually slow down at least in a few milimiliseconds, because you're adding a layer to the MySQL functions in PAWN.
Kaperstone is offline   Reply With Quote
Old 28/09/2015, 12:06 AM   #5
ThePhenix
Gangsta
 
ThePhenix's Avatar
 
Join Date: May 2012
Posts: 822
Reputation: 157
Default Re: Easy MySQL 1.0 - Simplifying the usage of MySQL queries!

Quote:
Originally Posted by Kaperstone View Post
Would be more understandable in my opinion to call the query launch "SQL::Launch" or "SQL::Fire" instead of "close"
You are not acually opening a query using "SQL::Close", in fact you are closing it.

Example:

pawn Code:
new handle = SQL::CreateTable("players");
        SQL::AddTableEntry(handle, "p_id", SQL_TYPE_INT, 11, true);
        SQL::AddTableEntry(handle, "p_name", SQL_TYPE_VCHAR, 24);
        SQL::AddTableEntry(handle, "p_password", SQL_TYPE_VCHAR, 64);
        SQL::AddTableEntry(handle, "p_score", SQL_TYPE_INT);
        SQL::AddTableEntry(handle, "p_posx", SQL_TYPE_FLOAT);
        SQL::AddTableEntry(handle, "p_posy", SQL_TYPE_FLOAT);
        SQL::AddTableEntry(handle, "p_posz", SQL_TYPE_FLOAT);
        SQL::Close(handle);//This part just closes the handle you just opened with SQL::CreateTable

If you ever used YINI or some system like it, this will result alike.
ThePhenix is offline   Reply With Quote
Old 28/09/2015, 12:11 AM   #6
Kaperstone
Banned
 
Join Date: May 2011
Location: Russia
Posts: 3,336
Reputation: 824
Default Re: Easy MySQL 1.0 - Simplifying the usage of MySQL queries!

Quote:
Originally Posted by ThePhenix View Post
You are not acually opening a query using "SQL::Close", in fact you are closing it
Yah, I understood that you're actually closing the query string, but I meant that at the same time you launch the query, just that by the user-scripter side you see that you create a table, and the end, you close/launch the query.
Kaperstone is offline   Reply With Quote
Old 28/09/2015, 12:12 AM   #7
ThePhenix
Gangsta
 
ThePhenix's Avatar
 
Join Date: May 2012
Posts: 822
Reputation: 157
Default Re: Easy MySQL 1.0 - Simplifying the usage of MySQL queries!

Quote:
Originally Posted by Kaperstone View Post
Yah, I understood that you're actually closing the query string, but I meant that at the same time you launch the query, just that by the user-scripter side you see that you create a table, and the end, you close/launch the query.


Yeah, nonetheless, I thought SQL::Close might be a good name as I'm actually closing the handle I opened before (not acually the query itself, you are right in SQL::Close the query gets executed)
ThePhenix is offline   Reply With Quote
Old 28/09/2015, 12:40 PM   #8
SecretBoss
Gangsta
 
SecretBoss's Avatar
 
Join Date: Dec 2014
Location: Greece
Posts: 818
Reputation: 103
Default Re: Easy MySQL 1.0 - Simplifying the usage of MySQL queries!

Amazing job, +REP
__________________
SecretBoss is offline   Reply With Quote
Old 28/09/2015, 01:10 PM   #9
Max_Andolini
Huge Clucker
 
Max_Andolini's Avatar
 
Join Date: Oct 2013
Posts: 327
Reputation: 37
Default Re: Easy MySQL 1.0 - Simplifying the usage of MySQL queries!

Good job
Max_Andolini is offline   Reply With Quote
Old 28/09/2015, 01:12 PM   #10
LazyB0y
Huge Clucker
 
LazyB0y's Avatar
 
Join Date: Feb 2014
Location: Pakistan
Posts: 277
Reputation: 15
Default Re: Easy MySQL 1.0 - Simplifying the usage of MySQL queries!

Amazing LOve IT
__________________
LazyB0y 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
MySQL Queries Terrorizt Scripting Help 3 05/07/2015 04:42 PM
[Tutorial] MySQL-queries iRaiDeN Tutorials 2 21/03/2014 04:20 PM
Many mySQL queries MP2 Scripting Help 9 09/04/2013 01:49 AM
Help with MySQL queries LiamM Scripting Help 4 20/03/2013 11:37 AM


All times are GMT. The time now is 08:52 PM.


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