[Knappen]

Introduction

I've been on these forums for a while, and I keep seeing people struggling to use the MySQL plugin by G-Stylez/BlueG. MySQL is hard to use, if you don't know HOW to use it. That's why I decided to create a small include with functions for loading and saving playerdata. I've made it as simple as possible. I haven't tested it with several players yet, but it should work pretty good.

This include uses the latest version of MySQL with cache functions (I believe we're up to R20 now, if I'm not mistaken).

The reason I created this was because I want to give people a small "hint" on how to use MySQL. You can either use the include as it is, or read it, to actually learn how to use MySQL with the original MySQL include.

I hope someone can put this to good use.

Functions

Quote:

stock DB_Connect(const MySQLHost[], const MySQLUser[], const MySQLDatabase[], const MySQLPassword[], &ConnectionHandle) Connects to a database. This returns the ConnectionHandlers ID stock DB_SetAccountTable(const Table[30], const Column[30]) Set the table to fetch player data from stock DB_CheckPlayer(playerid, ConnectionHandle = 1) Check if player exists and puts it into a variable (AccountsExists[MAX_PLAYERS]) stock DB_GetPlayerData(playerid, Password[], ConnectionHandle = 1) Prepares data for loading stock DB_LoadPlayerData(playerid, ConnectionHandle = 1) Activates the loading stock DB_Finish(playerid, ConnectionHandle = 1) Has to be used every time you are done loading player data. This deletes the cache stock DB_GetInt(const Column[], ConnectionHandle = 1) stock DB_GetString(const Column[], StringLength = 24, ConnectionHandle = 1) stock DB_GetFloat(const Column[], ConnectionHandle = 1) stock DB_SaveData(ConnectionHandle, Format[], {Float, _}: ...) Used to save data to the database. Example provided further down the post. Specifiers i or d - Integer s - String f - Float l (logical) - Bool There's also 2 bools included AccountExists[MAX_PLAYERS] and PlayerLoggedIn[MAX_PLAYERS] AccountExists gets set when you call DB_CheckPlayer, and PlayerLoggedIn gets set when you call DB_GetPlayerData

Usage Example

pawn Code:

new MySQLCon;

public OnGameModeInit()
{
    DB_Connect("localhost", "root", "samp", "12345", MySQLCon); // Connect to the database. MySQLCon will be our connection handler
    DB_SetAccountTable("playerdb", "Name");                     // Set the table for players
}

public OnPlayerConnect(playerid)
{
    DB_CheckPlayer(playerid, MySQLCon);                         // Check if player exists
    return 1;
}

public OnPlayerRequestClass(playerid, classid)
{
    if(AccountExists[playerid])
        SendClientMessage(playerid, Color, "/login to login.");
    else
        SendClientMessage(playerid, Color, "/register to register.");
}

YCMD:login(playerid, params[], help)
{
    if(help) SendClientMessage(playerid, Color, "Used to log in.")
    else
    {
        new Password[30];
        if(sscanf(params, "s", Password))
        {
            SendClientMessage(playerid, Color, "* Usage: /login [Password]");
            return 1;
        }
        else
        {
            // Encrypt password before you pass it on to DB_GetPlayerData
            DB_GetPlayerData(playerid, Password, MySQLCon);         
            LoadPlayer(playerid);
        }
    }
}

enum pdata
{
    PlayerLevel,
    Float:X,
    SomeString[20]
}
new pData[MAX_PLAYERS][pdata];

stock LoadPlayer(playerid)
{
    if(DB_LoadPlayerData(playerid, MySQLCon))
    {
        pData[playerid][PlayerLevel]    = DB_GetInt("Level", MySQLCon);
        pData[playerid][X]              = DB_GetFloat("SpawnX", MySQLCon);
        pData[playerid][SomeString]     = DB_GetString("SomeString", MySQLCon);
        DB_Finish(playerid, MySQLCon);
        return 1;       
    }
    else SendClientMessage(playerid, 000000, "Wrong Password");
    return 0;
        
}

stock SavePlayer(playerid)
{
    if(IsPlayerConnected(playerid))
    {
        new PlayerName[24];
        GetPlayerName(playerid, PlayerName, sizeof(PlayerName));
        new Test[10] = "John", Testvar = 19;
        DB_SaveData(MySQLCon,
        "ssdds",                    // String x2, Integer x2, String
        "Name", "Knappen",          // `Name`='Knappen',
        "Friend", Test,             // `Friend`='John',
        "Money", 1,                 // `Money`=1,
        "PlayerLevel", Testvar,     // `Playerlevel`=19
        "Name", PlayerName);       // WHERE `Name`='Eirik' or whatever my name is.
        return 1;
    }
    return 0;
}

Note: The query size is defined in the top of the include. The default is left at 300 cells, if you want it to be bigger than that, you can change it.

Download

Download Here

Note: This can only be used with the newest MySQL plugin, which can be found Here



If you find bugs or find something that can be improved, please notify me by either a post here or a PM : )
And again, this is for learning purposes. It's not thorougly tested (Though it should work).
And in case anyone wondered, it uses the functions cache_save and cache_set_active to load the information through functions.

Changelog

Version 1.1

• DB_SaveData Added. You can now save data to your database with one line.
• sizeof parameter was added to DB_GetString. This was done to save up a few cells. Default size is 24 if you don't write it. You can still use it without the sizeof.
• Security taken into measure. It escapes all strings that gets saved to the database. Also password and playername gets escaped when checking an account.

Version 1.0

• First version of the include. Most of the things needed to load player data is in this version.

If you want me to add more specifiers or add other features to it, don't hesitate to ask. All feedback is appreciated.

[SuperViper]

Did you actually test your little DB_CheckPlayer snippet you got there in your example? I'm almost certain it won't work because MySQL is run on a separate thread and while the query is running, the other code will be executed.

[Knappen]

Yeah, I did actually. It works fine for me at least!
Working on a few bugs right now, appearently, there were more than I thought!

EDIT: All known issues have been fixed : ) Works great for me at least

[tiernantheman]

Well done mate, it's really easy to understand and have no problems with it, good job!

[Knappen]

I'll work a little bit on that : )

Edit: It now escapes all the strings that contains the password and username (Assuming that everyone encrypts their password, it shouldn't have actual impact on the SQL server, but it should now be safe for use). Totally forgot about it. Usually would've added that! Thanks for reminding me, ******!

[DeathKing]

i would love this is this can have the save function already

[Knappen]

Working on it. It will probably be done soon.

Edit: Saving part is added.

[DeathKing]

Sorry net problems it double posted..... so i changed the text cause if i don't it might get reported as spam

[DeathKing]

Quote:

Originally Posted by Knappen Working on it. It will probably be done soon. Edit: Saving part is added.

good work rep+

SUGGESTION:
- how about not making the scripter not create the column name and password
- a something like #define Use_WhirlPool with this enabled this should hash the pass automatically
- how about something like a stock function that would make columns even if you don't go PhpmyAdmin but u still need to create db in Phpmyadmin
- have multiple loading like your saving........

I would love to see this in your script

Question: your save in 1 line function.... does it have a limit like Ex.50 per save??

[Knappen]

Quote:

Originally Posted by DeathKing good work rep+ SUGGESTION: - how about not making the scripter not create the column name and password - a something like #define Use_WhirlPool with this enabled this should hash the pass automatically - how about something like a stock function that would make columns even if you don't go PhpmyAdmin but u still need to create db in Phpmyadmin - have multiple loading like your saving........ I would love to see this in your script Question: your save in 1 line function.... does it have a limit like Ex.50 per save??

I'm not sure I understood all your suggestions. What do you mean by your first suggestion?

I though about implementing encryption, but due to the fact that people use different encryptions, I did it like it is not. I believe this is the most convenient way to do it.

A stock that creates columns isn't really needed. At least in my opinion. Mostly because when you create new columns, you want a default value, as well as define what type of data it will hold. This is used to save and load player data. It's currently not made out to administer you database.

I could probably make it so that the loading part is the same way as the saving part, but in my opinion that would just be a waste, because it would look almost the same as it does now, and it would only require more resources.

As for the saving part, it limits to the size of the query. Default is 300 cells, but it can be changed inside the include. I'm not sure if there's a limit to how many parameteres you can pass on to a function, but even if there is, I don't think you will go over it.