[kamzaf]

How to make a MySQL Admin System

Introduction:
Hey guys I've decided to make a tutorial on how to make a MySQL admin system with a few basic admin commands. If you need any help in regards to this tutorial, feel free to PM me or post a reply in this topic.

Before we start:
Before we start the tutorial you must have the following includes in your pawno folder:
data - by me :P
MySQL include | MySQL Plugin - StrickenKid
ZCMD - Zeex
Sscanf - Y_Less
You may also use WAMPServer for your MySQL/PHPMyAdmin database.

Now once you have installed wampserver and have placed the includes in the appropriate folder we may now begin.

Lets start:
Now to begin you have to include the following lines on top of your script:

pawn Code:

#include <a_samp>
#include <mysql>
#include <zcmd>
#include <sscanf2>
#include <data>

Underneath that we have to tell the script what server and credentials are we going to connect to. So for that add the following lines underneath:

pawn Code:

#define mysql_host "localhost" //your destination server
#define mysql_user "root" //default user name of wampserver
#define mysql_password "" //wampserver has no default password unless you have set one.
#define mysql_database "sa-mpsql"//the name of your database

Now that you have defined the mysql database, we must make the tables in your server. Please follow the next pictures in order to make them.
Make sure this icon is green.


Left click on the icon and click on "PhpMyAdmin"


Then click on Database, enter the name of the database: "sa-mpsql" and click create.


Then click on sql and enter the following lines in the console:

pawn Code:

CREATE TABLE IF NOT EXISTS `playerdata` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `nick` varchar(24) NOT NULL,
  `password` varchar(68) NOT NULL,
  `admin` int(20) NOT NULL,
  `score` int(20) NOT NULL,
  `money` int(20) NOT NULL,
  `kills` int(20) NOT NULL,
  `deaths` int(20) NOT NULL,
  `ip` varchar(16) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1462;

Once you have entered those lines in the console click enter and the following screen should appear after you click on "playerdata".


Next:
Once we have our tables ready, we can now continue with the script.

Add the following lines underneath the defines:

pawn Code:

new MySQL:mysql;
new field[128];

#define mysql_fetch_float(%0,%1) mysql_fetch_field(%0,field); \
%1=floatstr(field)
#define mysql_fetch_string(%0,%1) mysql_fetch_field(%0,%1)
#define mysql_fetch_int(%0,%1) mysql_fetch_field(%0,field); \
%1=strval(field)

Next above OnGameModeInit we have to add our enums on the script:

pawn Code:

enum PlayerInfo
{
    ID, // id of the player
    Nick[24], // name of the player
    pAdmin, // admin level of the player
    pMoney, //money of the player
    pKills, // Kills of the player
    pDeaths, // deaths of the player
    pScore, // score of the player
    IP[16], // Storing the ip of the player
        Logged, // Players logged in or not variable
        IsRegistered //checks if the player is registered or not.
};
new pInfo[MAX_PLAYERS][PlayerInfo];

Next under OnGameModeInit include the following lines:

pawn Code:

public OnGameModeInit()
{
    mysql = mysql_init(LOG_ALL); // Tells sql to log all mysql features used in the script
    new Connection = mysql_connect(mysql_host, mysql_user, mysql_password, mysql_database, mysql); // connects with the mysql database
    if(Connection) //checks if the database is successfully connected
    {
        new dest[200];
        mysql_stat(dest); // display the mysql database statistics.
        printf(dest);
        printf(">> MySQL connection successfully initialized"); // if it is connected it will display this line, if not then it wont display anything.
    }
    return 1;
}

Next that we have successfully entered the credentials for our database and have the server connect to it we must log in or register the player under OnPlayerConnect.

pawn Code:

public OnPlayerConnect(playerid)
{
    new Query[500];
    GetPlayerName(playerid, pInfo[playerid][Nick], 24); //gets the player's name and stores it to to your enum pInfo[playerid][Nick]
    GetPlayerIp(playerid, pInfo[playerid][IP], 16); //Gets the IP of the player and stores it to pInfo[playerid][IP]
    mysql_real_escape_string(pInfo[playerid][Nick], pInfo[playerid][Nick]); // now we have to escape the name inorder to escape any mysql injections. (http://en.wikipedia.org/wiki/SQL_injection)
    format(Query, 500, "SELECT `nick` FROM `playerdata` WHERE `nick` COLLATE latin1_general_cs = '%s' LIMIT 1", pInfo[playerid][Nick]); // here we are selecting the name of the player who logged in from the database.
    mysql_query(Query); // we query the statement above
    mysql_store_result(); // next we store the result inorder for it to be used further ahead.
    if(mysql_num_rows() > 0) // if the  database has more then one table with the given name
    {//then
        ShowPlayerDialog(playerid, 1, DIALOG_STYLE_PASSWORD, "Login", "This account is registered! Please log in:", "Login", ""); // show the login dialog and tell the player to login.
    }
    else // else if the database found no tables with the given name
    { // then
        ShowPlayerDialog(playerid, 2, DIALOG_STYLE_PASSWORD, "Register", "This account not registered yet! Please register:", "Register", "");// show the register dialog and tell the player to register
    }
    mysql_free_result(); // here we free the result we stored in the beginning as we do not need it anymore.
    //You must always free the mysql result to avoid
    //there being massive memory usage.
    return 1;
}

Now that we have completed our login details, we must move on to the dialogs.

pawn Code:

public OnDialogResponse(playerid, dialogid, response, listitem, inputtext[])
{
    if(dialogid == 2) //If Dialog is our register dialog
    {
        if(response) //If they click the button register
        {
            if(!strlen(inputtext) || strlen(inputtext) > 68)  //checks if password is more then 68 characters or nothing.
            {
                SendClientMessage(playerid, 0xFF0000, "You must insert a password between 1-68 characters!");
                ShowPlayerDialog(playerid, 2, DIALOG_STYLE_PASSWORD, "Register", "This account not registered yet! Please register:", "Register", "");// show the register dialog again.
            }
            else if(strlen(inputtext) > 0 && strlen(inputtext) < 68) // if the password is in between 1 - 68 characters
            {
                new escpass[100];
                mysql_real_escape_string(inputtext, escpass); // here we escape the data again to avoid any mysql injection,
                //remember to always to do this when SELECT'ing or INSERT'ing any data in the database
                MySQL_Register(playerid, escpass); // Here we are going to another function to register the player.
            }
        }
        if(!response)
        {
            SendClientMessage(playerid, 0xFF0000, "You must register before you can login!");
            ShowPlayerDialog(playerid, 2, DIALOG_STYLE_PASSWORD, "Register", "This account not registered yet! Please register:", "Register", "");// show the register dialog again.
        }
    }
    if(dialogid == 1) //Dialog login
    {
        if(!response) //If they click the cancel button
        {
                SendClientMessage(playerid, 0xFF0000, "You must login before you spawn!"); //Sends the client a error message
                Kick(playerid); // and kicks him. ( you can change it to show the player the login dialog again by uncommenting the bottem line and commenting this one.
                //ShowPlayerDialog(playerid, 1, DIALOG_STYLE_PASSWORD, "Login", "This account is registered! Please log in:", "Login", "");
        }
        if(response) //If the player clicked login
        {
            if(!strlen(inputtext) || strlen(inputtext) > 68)  //if the password is not 1 to 100 characters
            {
                SendClientMessage(playerid, 0xFF0000, "You must insert a password between 1-68 characters!"); //Sends the client a error message
                ShowPlayerDialog(playerid, 1, DIALOG_STYLE_PASSWORD, "Login","Your user is registered! Please login with your password below!\n{FF0000} Please enter a password between 0 and 68 characters","Login","Cancel");
                return 1;
            }
            mysql_real_escape_string(inputtext, inputtext); //Here we escape the inputtext to avoid SQL injections as stated above.
            mysql_real_escape_string(pInfo[playerid][Nick], pInfo[playerid][Nick]); // escapeing ^^
            new Query[500];
            format(Query, 500, "SELECT * FROM `playerdata` WHERE `nick` COLLATE latin1_general_cs = '%s' AND `password` = '%d'", pInfo[playerid][Nick], udb_hash(inputtext)); // now here check the database if the player has given the proper password.HTTP
            mysql_query(Query);
            mysql_store_result();
            if(mysql_num_rows() > 0) { // if the password the player provided is correct and matches the database
                MySQL_Login(playerid); // we will call this function and log the player in.
            } else {
                //other wise this means that the password that the player
                //typed was incorrect and we will resend the dialog.
                ShowPlayerDialog(playerid, 1, DIALOG_STYLE_PASSWORD, "Login","Your user is registered! Please login with your password below!\n The password you typed was incorrect!","Login","Cancel"); //Shows our login dialog again.
            }
            mysql_free_result(); //Remember to always free a result if you stored one!
        }
    }
    return 1;
}

Now compile your script and the following errors should appear on your screen:

Quote:

error 017: undefined symbol "MySQL_Register" error 017: undefined symbol "MySQL_Login" warning 203: symbol is never used: "field"

Don't worry about them we will fix them in the following steps.

pawn Code:

stock MySQL_Register(playerid, passwordstring[])
{
    new Query[300];
    mysql_real_escape_string(pInfo[playerid][Nick], pInfo[playerid][Nick]);
    // escaping the name of the player to avoid sql_injections.
    mysql_real_escape_string(pInfo[playerid][IP], pInfo[playerid][IP]);
    // escaping the IP of the player to avoid sql_injections.
    // as you might've seen we haven't escaped the password here because it was already escaped in our register dialog
    format(Query, sizeof(Query), "INSERT INTO `playerdata` (`nick`, `password`, `ip`) VALUES('%s', '%d', '%s')", pInfo[playerid][Nick], udb_hash(passwordstring), pInfo[playerid][IP]); // Here we use the INSERT option and insert the name, password, and ip of the player in the database.
    // we don't insert the score, admin, or any other variable because its automatically 0.
    mysql_query(Query);
    // here we do not need to mysql_store_result or mysql_free_result
    // because we are only inserting data in the database not selecting it
    //next we set the players logged variable to 1.
    //and the isregistered variable to 1 aswell.
    SendClientMessage(playerid, -1, "You have now been  successfully registered on this server!");
    pInfo[playerid][Logged] = 1; //Sets the login variable to 1, meaning logged in.
    pInfo[playerid][IsRegistered] = 1; // sets the registered variable to 1. meaning registered.
    return 1;
}

stock MySQL_Login(playerid)
{
    new Query[500];
    mysql_real_escape_string(pInfo[playerid][Nick], pInfo[playerid][Nick]); // escaping the name of the player to avoid sql_injections.
    format(Query, sizeof(Query), "SELECT * FROM `playerdata` WHERE `nick` COLLATE latin1_general_cs = '%s' LIMIT 1", pInfo[playerid][Nick]);
    mysql_query(Query);
    // here we select all of the user's data in the database and store it
    mysql_store_result();
    while(mysql_fetch_row(Query))
    // here after the server has selected the user
    //from the database and stored its data we extract that data onto our enums.
    {
        mysql_fetch_int("id", pInfo[playerid][ID]);
        // the special identifier of a user called "id"
        mysql_fetch_int("admin", pInfo[playerid][pAdmin]);
        // the admin level of the player
        mysql_fetch_int("score", pInfo[playerid][pScore]); SetPlayerScore(playerid, pInfo[playerid][pScore]);
        // here we fetch the score and save it to the enum and also save it to the server by using setplayerscore
        mysql_fetch_int("money", pInfo[playerid][pMoney]); GivePlayerMoney(playerid, pInfo[playerid][pMoney]);
        // here we fetch the score and save it to the enum and also save it to the server by using setplayerscore
        mysql_fetch_int("kills", pInfo[playerid][pKills]);
        // the amount of kills a player has
        mysql_fetch_int("deaths", pInfo[playerid][pDeaths]);
        // the amount of deaths a player has
        //
        // the way to fetch a users stats from the database is:
        //mysql_fetch_int("table_name", variable_to_store_in);  remember the "table_name" is case sensitive!
    }
    mysql_free_result();
    // here we free our result and end the SELECT process.  Remember this is very important otherwise you may receive high amount of lag in your server!
    pInfo[playerid][Logged] = 1; // sets the logged variable to 1 meaning logged in.
    return 1;
}

Now that we have created our login and register loading/saving system we can move on to the saving part of the tutorial of when the player disconnects. For this all you have to include is the following line under OnPlayerDisconnect.

pawn Code:

public OnPlayerDisconnect(playerid, reason)
{
    SavePlayer(playerid);
    return 1;
}

This will forward the data to another function which we will create. Put the following function somewhere in your script.

pawn Code:

SavePlayer(playerid)
{
    if(pInfo[playerid][Logged] == 1)
    // checks if the player is logged
    {
        new Query[500];
        format(Query, 500, "UPDATE `playerdata` SET `admin` = '%d', `score` = '%d', `money` = '%d', `kills` = '%d', `deaths` = '%d' WHERE `id` = '%d' LIMIT 1",
        pInfo[playerid][pAdmin],
        pInfo[playerid][pScore],
        pInfo[playerid][pMoney],
        pInfo[playerid][pKills],
        pInfo[playerid][pDeaths],
        pInfo[playerid][ID]);
        mysql_query(Query);
//this basically gets the variables and stores it to the players special identifier called "ID".
    }
}

Part 1 finished!
Next:

Now I'm going to make a few simple admin commands. The first command i'm going to make is...:

pawn Code:

CMD:setlevel(playerid, params[])
{

Basically this command will set the admin level of the player specified.
Before we start include the following lines above OnGameModeInit

pawn Code:

#define MAX_ADMIN_LEVEL 7
#define SETLEVEL_ADMIN 5
//you may change the level of this according to your own choice

pawn Code:

CMD:setlevel(playerid, params[])
{
    if(IsPlayerAdmin(playerid) || pInfo[playerid][pAdmin] >= SETLEVEL_ADMIN)
    // if the player is an rcon admin or the players admin level is greater or equal
    // to the defined admin level of that command
    {//then
        new level, target;
        //here we create 2 variable which we will use
        if(sscanf(params, "ui", target, level)) return SendClientMessage(playerid, red, "[*] Usage: /setadmin [playerid/name] [level]");
        //if the player hasnt entered a target id or level then it will return that msg
        if(target == INVALID_PLAYER_ID) return SendClientMessage(playerid, red, "[*] Enter a valid player ID/name!");
        // if the target player is not a valid player then it will return that msg
        new string[128], Query[500], str2[50];
        if(level < 0 || level > MAX_ADMIN_LEVEL) // here we check if the level the player entered is more then the max admin level defined or 0
        {
            format(string, sizeof(string), "Enter a level between 0 and %d!", MAX_ADMIN_LEVEL);
            SendClientMessage(playerid, red,string); // if it is then we send the message and stop the command from processing
            return 1;
        }
        format(Query, 500, "UPDATE `playerdata` SET `admin` = '%d' WHERE `id` = '%d' LIMIT 1", level, pInfo[target][ID]); //Format the query
        mysql_query(Query);
        // here we use the UPDATE option again and tell the database to update the player we specified's admin level to what we have set.
        pInfo[target][pAdmin] = level;
        // here we set the target's var to what we defined
        format(string, 256, "You have set %s[%d]'s admin level to %d.", pInfo[target][Nick], target, level);
        SendClientMessage(playerid, green, string);
        format(string, 256, "Admin %s has set your admin level to %d.", pInfo[playerid][Nick], level);
        SendClientMessage(target, yellow, string);
        for (new i=0; i<MAX_PLAYERS; i++)
        {
            if(pInfo[i][Logged] == 1 && pInfo[i][pAdmin])
            {
                format(str2, sizeof(str2), "Admin %s[%d] has used the following command: Setlevel", pInfo[playerid][Nick], playerid);
                SendClientMessage(i,COLOR_SPRINGGREEN,string);
            }
        }
        // here we send the message to the admins
        PlayerPlaySound(target,1057,0.0,0.0,0.0);
        // play the sound for the target notifying him of this command.
    }
    else return ErrorMessage(playerid); // if it wasnt a rcon admin or his admin level wasnt greater then the max level
    //then we send the error message
    return 1;
}

And your finished!

Extras: If you did not understand any part of my tutorial feel free to PM me or reply to this topic with what you needed help with.
If for some reason you could not follow through my tutorial then you may download the files here.

UPCOMING: Making a User CP with MySQL

[RajatPawar]

Nice, better if you had used threaded queries!

[Vince]

Quote:

Originally Posted by Rajat_Pawar Nice, better if you had used threaded queries!

Exactly what I was about to say when I read this thread.

[kamzaf]

Quote:

Originally Posted by Rajat_Pawar Nice, better if you had used threaded queries!

Hm that's true but i find this more easier to work with and plus I haven't received any problems with this so ya...

[MiGu3X]

Very good tutorial im gonna use it maybe . How does wampserver works? As a thing to save stuff or wth?.. Regards

[RajatPawar]

Quote:

Originally Posted by kamzaf Hm that's true but i find this more easier to work with and plus I haven't received any problems with this so ya...

I am sure you'll find many posts and threads discussing why unthreaded queries shouldn't be used. Now, since you are making a tutorial, many people will follow in your footsteps and using threaded queries is a good practice, so to avoid getting people having to use bad ways, I suggested this ! Feel free to use unthreaded queries for personal use..

[Bakr]

This is not a tutorial, but a "copy this code" thread.

You never explain how to actually use MySQL, why you need to do what you did, or how you got the results you did.

[daddyblake]

--------------------------------------

Edit: Removed, dumb question I figured out very quickly myself.

[kamzaf]

Quote:

Originally Posted by Bakr This is not a tutorial, but a "copy this code" thread. You never explain how to actually use MySQL, why you need to do what you did, or how you got the results you did.

Well I've explained each and every line in the tutorial thoroughly and if there is still something you don't understand then you can always post here.

[M0nSt3r]

Thanks alot kamzaf! This helped me very much in my new GM. I would recommend it to any newbie mysql scripter! THANKSSS

[Scenario]

You really should be using threaded queries. I don't quite understand why people continue to use this style of MySQL coding, especially since the R7 release of BlueG's plugin.

Nevertheless, people still use dcmd and strcmp for commands, instead of something faster like ZCMD or YCMD.

[kamzaf]

The main reason why most people still use R6 is because most people find R7 something very hard to transfer to. They don't understand the main concept behind it. And people are correct that we should switch to R7 to save the lag times + memory usage etc. So i'll prob make a R7 tutorial soon.

[Scenario]

You're correct- but what if people STARTED by using R7? Then they'd be like "well I don't know how to use R6" which, if you ask me, is better than not knowing how to use R7.

When I first started scripting, I looked at strcmp commands with a dropped jaw. I then looked at sscanf with a dropped jaw, but after reading, it made more sense to me than using strtok. After continuing to read, I found out that is was WAY more efficient and WAY better in general.

To this day I don't know how to write an strcmp with parameters (like /kick [nick/id]).

Same with saving/loading data- I never learned a file-based system simply because MySQL/SQLite (IMHO, again) provides more expansion.

Nonetheless, my point is: if people don't know the outdated ways of programming, they are less likely to make a bad decision in terms of "choosing a system."

[PabloDiCostanzo]

I have this errors

Code:

C:\Documents and Settings\Administrador\Escritorio\Gang Wars\filterscripts\MySQL.pwn(117) : error 017: undefined symbol "Query"
C:\Documents and Settings\Administrador\Escritorio\Gang Wars\filterscripts\MySQL.pwn(118) : error 017: undefined symbol "Query"

[kamzaf]

Quote:

Originally Posted by PabloDiCostanzo I have this errors Code: C:\Documents and Settings\Administrador\Escritorio\Gang Wars\filterscripts\MySQL.pwn(117) : error 017: undefined symbol "Query" C:\Documents and Settings\Administrador\Escritorio\Gang Wars\filterscripts\MySQL.pwn(118) : error 017: undefined symbol "Query"

Add new Query[500]; above the line.

[PabloDiCostanzo]

Code:

C:\Documents and Settings\Administrador\Escritorio\Gang Wars\filterscripts\MySQL.pwn(118) : error 017: undefined symbol "SOQ"

And now I get that

[ReneG]

Quote:

Originally Posted by kamzaf most people find R7 something very hard to transfer to. They don't understand the main concept behind it.

Then they're just being ignorant, if you know how ShowPlayerDialog works with OnDialogResponse, then you should know how to use threaded queries. It's literally the same concept. The only difference is that BlueG's plugin allows you to pass arguments.

[LarzI]

Quote:

Originally Posted by VincentDunn Then they're just being ignorant, if you know how ShowPlayerDialog works with OnDialogResponse, then you should know how to use threaded queries. It's literally the same concept. The only difference is that BlueG's plugin allows you to pass arguments.

Would you say there's a difference between using one single callback and give each thread its unique ID, and having a callback for each thread? If so: What difference? What would you recommend? What's most/least efficient? I personally find using separate callbacks for each thread to be more tidy, but if it's very inefficient in comparison, then I'd like to switch ASAP before I get to comfy with it.

[ReneG]

I would recommend separate callbacks, but it really is just personal preference.

For example with one callback

pawn Code:

#define RESULT_LOAD (1)
#define RESULT_SAVE (2)

public OnQueryExecute(resultid, playerid)
{
    new rows, fields;
    cache_get_data(rows, fields);

    switch (resultid) {
        case RESULT_LOAD: 
            // code here
        case RESULT_SAVE: 
            // code here
    }

    return 1;
}

This can get pretty hard to work with the more queries you script in, but it gets the job done. You can argue that the switch statement will slow it down, but the difference would practically be zero.

with single callbacks

pawn Code:

LoadPlayer(playerid)
{
    // code here
}

public OnPlayerLoad(playerid)
{
    new rows, fields;
    cache_get_data(rows, fields);

    // code here
    return 1;
}
 
SavePlayer(playerid)
{
    // code here
}

public OnPlayerSave(playerid)
{
    printf("Player %d saved.", playerid);
    return 1;
}

It may seem like a lot more lines, but it's a lot easier to handle this way IMO. You don't have to deal with different result ids, there are less mistakes to be made, and you can safely say that your query will only execute what you put into its according callback.

tl;dr - it doesn't really matter, as long as you take efficient advantage of the plugin, then you're fine

[Konstantinos]

Quote:

Originally Posted by RealCop228 but what if people STARTED by using R7?

That's me!

I tried to learn MySQL when the version was R6 but I found it difficult and I also had few problems with phpMyAdmin so I stopped reading tutorials etc and I started learning SQLite. I do agree that MySQL/SQLite provides more expansion and that's why I used/use them.

I started few days ago reading AndreT's tutorial about MySQL R7 and cache and it was pretty easy. I downloaded the gamemode with simple register/login system AndreT suggested and I read it again and again. I found it very easy and I can say that R7 is a lot of easier than the older versions, atleast for me. It took 2 days to see how it works and what to do and I made a simple register/login system from scratch in less than 1 hour!