[Carlton]

Information

Hi there, since i've been contacted several times about C-MySQL. Most users are confused on how to use this. Well, I decided to make a tutorial on how to use this system. Below are the necessary files needed for this tutorial.

C-MySQL - Click Me!
A MySQL plugin, click me!

Well before we start, we need to include the script. If you're using StrickenKids put this:

pawn Код:

#include <C-MySQL-S>

if BlueG's put this:

pawn Код:

#include <C-MySQL>

- Note: I did not put mysql_real_escape_string purposely, because C-MySQL already protects itself from injections.

Registration

Lets start with the registration, that's the first part of a registration system. We're going to create a function called AddAccount. I've decided to make it a function since I have no clue where users would want their registration put.

pawn Код:

stock AddAccount(Username[], Password[]) {
     CMySQL_Create(Username, "Accounts", "Name");
     CMySQL_Password(Username, "Accounts", "Name", Password);
}

There we go, two functions, nothing difficult. Oh, wait, maybe some more. We forgot to check if the account exists. Here's a simple function to check if a account exists in this tutorial wise.

pawn Код:

stock DoesAccountExists(Username[]) {
     new string[88];
     format(string, 88, "SELECT Name FROM Accounts WHERE Name = '%s'", Username);
     mysql_query(string); mysql_store_result();
     if(mysql_num_rows() > 0) {
          mysql_free_result();
          return 1;
     }
     mysql_free_result();
     return 0;
}

Now lets try this again, with adding the function above.

pawn Код:

stock AddAccount(Username[], Password[]) {
     if(DoesAccountExists(Username)) return 1;
     CMySQL_Create(Username, "Accounts", "Name");
     CMySQL_Password(Username, "Accounts", "Name", Password);
     return 1;
}

There we have three functions now. Simple and easy so far?

Login

Now, we can do this in two ways, using CMySQL_Int, and CMySQL_Get or using CMySQL_LoadAccount. Since the new release, CMySQL_LoadAccount would be faster, this is because it allows threading opportunity, which is really suggested for faster account loading. I'll show you both ways. We're going to create a function called LoginPlayer.

pawn Код:

#define MySQL_LOAD_ACCOUNT (0)

stock LoginPlayer(playerid, password[]) {
     if(!DoesAccountExists(Username)) return 1;
     new name[24];
     GetPlayerName(playerid, name, 24);
     if(!strcmp(CMySQL_GetPassword(name, "Accounts", "Name", "Password"), password)) {
         CMySQL_LoadAccount(name, "Accounts", "Name", MySQL_LOAD_ACCOUNT , playerid);
     }
}

Simple so far? We just checked if the account existed or not, then we checked if the password was correct and finally then began to process to the thread callback. Now lets continue.

pawn Код:

public OnQueryFinish( query[], resultid, extraid, connectionHandle ) {
     if(resultid == MySQL_LOAD_ACCOUNT ) {
          mysql_store_result();
          new MySQLData[128], MyStats[3];
          mysql_fetch_row_format(MySQLData, "|");
          sscanf(MySQLData, "p<|>iii", MyStats[0], MyStats[1], MyStats[2]);
          format(MySQLData, 128, "This account stores %i kills & %i deaths G_G", MyStats[0], MyStats[1]);
          print(MySQLData);
          GivePlayerMoney(playerid, MyStats[2]);
          mysql_free_result();
     }
     return 1;
}

That could of been a bit confusing to you, but that's Ok, you always have the other way of loading accounts. But, i'll explain what I did anyway. I stored the selecting query result, fetched the account, and printed out the stats. Lets try the more simpler way but a bit slower. Here is where we will use the known functions.

pawn Код:

stock LoginPlayer(playerid) {
     new name[24], MyStats[3];
     GetPlayerName(playerid, name, 24);
     if(!DoesAccountExists(name)) return 1;
     if(!strcmp(CMySQL_GetPassword(name, "Accounts", "Name", "Password"), password)) {
            MyStats[0] = CMySQL_Int(name, "Accounts", "Name", "Kills");
            MyStats[1] = CMySQL_Int(name, "Accounts", "Name", "Deaths");
            MyStats[2] = CMySQL_Int(name, "Accounts", "Name", "Money");

            new MySQLData[80];
            format(MySQLData, MySQLData, "This account stores %i kills & %i deaths G_G", MyStats[0], MyStats[1]);
            print(MySQLData);
            GivePlayerMoney(playerid, MyStats[2]);
     }
}

That was more simple now was it? Now, lets wrap this up with Saving, and a overall.

Saving

This should be the most easy part. We're going to create a function that's named SaveAccount. Sounds fair?

pawn Код:

stock SaveAccount(playerid) {
    new name[24], MyStats[3];
    GetPlayerName(playerid, name, 24);
    if(!DoesAccountExists(name)) return 1;
    MySQL_SetInt(name, "Accounts", "Name", "Kills", MyStats[0]);
    MySQL_SetInt(name, "Accounts", "Name", "Deaths", MyStats[1]);
    MySQL_SetInt(name, "Accounts", "Name", "Money", MyStats[2]);
}

Ok, simple functions in a matter of seconds. We pretty much just saved and loaded kills, deaths, and money in SaveAccount and LoginPlayer. Now, lets roll over to the overview.

Overview

Your script should look like this, well at least.

pawn Код:

#include <a_samp>
#include <C-MySQL>

new 
       MyStats[3];

stock DoesAccountExists(Username[]) {
     new string[88];
     format(string, 55, "SELECT Name FROM Accounts WHERE Name = '%s'", Username);
     mysql_query(string); mysql_store_result();
     if(mysql_num_rows() > 0) {
          mysql_free_result();
          return 1;
     }
     mysql_free_result();
     return 0;
}

stock AddAccount(Username[], Password[]) {
     if(DoesAccountExists(Username)) return 1;
     CMySQL_Create(Username, "Accounts", "Name");
     CMySQL_Password(Username, "Accounts", "Name", Password);
     return 1;
}

stock LoginPlayer(playerid) {
     new name[24];
     GetPlayerName(playerid, name, 24);
     if(!DoesAccountExists(name)) return 1;
     if(!strcmp(CMySQL_GetPassword(name, "Accounts", "Name", "Password"), password)) {
            MyStats[0] = CMySQL_Int(name, "Accounts", "Name", "Kills");
            MyStats[1] = CMySQL_Int(name, "Accounts", "Name", "Deaths");
            MyStats[2] = CMySQL_Int(name, "Accounts", "Name", "Money");

            new MySQLData[80];
            format(MySQLData, MySQLData, "This account stores %i kills & %i deaths G_G", MyStats[0], MyStats[1]);
            print(MySQLData);
            GivePlayerMoney(playerid, MyStats[2]);
     }
}

stock SaveAccount(playerid) {
    new name[24], MyStats[3];
    GetPlayerName(playerid, name, 24);
    if(!DoesAccountExists(name)) return 1;
    MySQL_SetInt(name, "Accounts", "Name", "Kills", MyStats[0]);
    MySQL_SetInt(name, "Accounts", "Name", "Deaths", MyStats[1]);
    MySQL_SetInt(name, "Accounts", "Name", "Money", MyStats[2]);
}

Insert any of those functions wherever you want someone to perform the actions we covered, thanks for reading, if you have any questions please post the question.

[Hiddos]

Looks nice, I'll try this as well.

[Flake.]

another great tut thanks for this

[[03]Garsino]

Nice tutorial, maybe also show the noobs how to prevent injection in for example the login/register part

[Carlton]

Quote:

Originally Posted by [03]Garsino Nice tutorial, maybe also show the noobs how to prevent injection in for example the login/register part

Hi, C-MySQL comes with MySQL injection protection already.

[[03]Garsino]

O, cool, thanks for telling me (I guess I should've opened the include though).
I've always planned to use MySql for my account system, but didn't get it working last time, so I continued to use dini :X

But since you released this very handy include, I shall switch right away!

[Jay_]

I don't think this is a good idea. You're performing a query for every single update to the database. Performing MySQL queries from the SA-MP server are really slow. Especially if they're not threaded.

[Carlton]

Quote:

Originally Posted by Jay_ I don't think this is a good idea. You're performing a query for every single update to the database. Performing MySQL queries from the SA-MP server are really slow. Especially if they're not threaded.

I prefer to use custom MySQL querys instead of this. I made the include for users who don't know MySQL the greatest, and for the work to be faster. It's querys aren't that slow, it will take at least a second longer, and I know if they're not threaded it's slower, that's why I pointed out CMySQL_LoadAccount.

[Kar]

this looks harder than normal mysql D:

[Darnell]

Simple & awesome.