[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