[StuffBoy]

Mysql System Tutorial

This is my first tutorial that I'm making on sa-mp about how to make a secure Mysql system with encrypted passwords.
Hope it will help novices to Mysql.

Requirements:

1. Have already some pawn knowledge;
2. Download StrickenKid Mysql Plugin for this tutorial here.
3. Download Sccanf2 plugin by ****** here.
4. Download XAMP or Wamp Server and install it.

First Steps:

• Open phpmyadmin and create a new database named "tutorial".

• Create a table named playerdb with 5 fields and fill the fields like in the image below.
  
  
• Add this on the top of your script.
  
  pawn Код:

  #include <a_samp>
  #include <mysql>
  #include <sscanf2>
  
  main()
  {
      print("\n----------------------------------");
      print(" Tutorial by : Stuffboy");
      print("----------------------------------\n");
  }
  
  // Colors
  #define COLOR_GRAD 0x6E76ADFF
  #define COLOR_WHITE 0xFFFFFFFF
  #define COLOR_BLUE 0x18A4FFFF
  
  // Starting out we need to config our Mysql connection details defining it.
  #define MYSQL_HOST "localhost" // Here is the host, you could have your internal IP instead of it.
  #define MYSQL_USER "DBuser" // This is the user for the mysql connection.
  #define MYSQL_PASS "1234" // This is the user password keep it blank.
  #define MYSQL_DB "tutorial" // The database where all will be stored.
  
  // This is used for the Register and Login part.
  #define LOGIN_DIALOG 1
  #define REGISTER_DIALOG 2
  
  // Player enumeration where all player information will be stored for e.g the money.
  enum pdb
  {
  username[MAX_PLAYER_NAME],
  password[32], // He use 32 cells because Md5 is based 32 cell encriptation,
  score,
  money,
  admin,
  loged,
  }
  new playerdb[MAX_PLAYERS][pdb];

• Add this stocks at the end of your script, also I'm going to explain how they work.
  
  pawn Код:

  stock IsUsernameRegistered(playerid, usernm[])
  {
      new query[50];
      format(query, sizeof(query),"SELECT * FROM playerdb WHERE username = '%s'", usernm);
      mysql_query(query);
      mysql_store_result();
      if(mysql_num_rows() != 0)
      {
          ShowPlayerDialog(playerid,LOGIN_DIALOG,DIALOG_STYLE_PASSWORD,"Login to Account.","Enter your password below:","Login","Cancel");
      }
      else
      {
          ShowPlayerDialog(playerid,REGISTER_DIALOG,DIALOG_STYLE_PASSWORD,"Register Account.","Enter your password below:","Register","Cancel");
      }
      mysql_free_result();
      return 1;
  }
  
  stock LoginPlayer(playerid, pass[])
  {
      new query[1000];
      format(query, sizeof(query),"SELECT * FROM playerdb WHERE username = '%s' AND password = md5('%s')",playerdb[playerid][username],pass);
      mysql_query(query);
      mysql_store_result();
      if(mysql_num_rows() != 0)
      {
          if(mysql_fetch_row(query, "|"))
          {
              sscanf(query,"e<p<|>s[24]s[32]iii>", playerdb[playerid]);
              new str[80];
              format(str, sizeof(str),"Welcome %s, you have been loged to your account",playerdb[playerid][username]);
              SendClientMessage(playerid, COLOR_BLUE, str);
              SetPlayerInterior(playerid, 0);
              SetPlayerVirtualWorld(playerid, 0);
              SetPlayerScore(playerid, playerdb[playerid][score]);
              GivePlayerMoney(playerid, playerdb[playerid][money]);
              SpawnPlayer(playerid);
              playerdb[playerid][loged] = 1; // set player loged variable true
          }
      }
      else
      {
          ShowPlayerDialog(playerid,LOGIN_DIALOG,DIALOG_STYLE_PASSWORD,"Login to Account.","Wrong password:","Login","Cancel");
      }
  }
  
  // The syntax of the mysql query of this one os, Update destination, fields and where to update in this case "username".
  stock SavePlayerStats(playerid) // save player stats
  {
      if(playerdb[playerid][loged] == 1)
      {
          new squery[200];
          new pmoney[MAX_PLAYERS];
          pmoney[playerid] = GetPlayerMoney(playerid);
          format(squery, sizeof(squery),"UPDATE playerdb SET score = %d, money = %d, admin = %d  WHERE username='%s'",
          playerdb[playerid][score],playerdb[playerid][money],playerdb[playerid][admin],playerdb[playerid][username]);
          mysql_query(squery);
          mysql_free_result();
          playerdb[playerid][loged] = 0;
      }
  }

  Код:

  Explanation of IsUsernameRegistered stock:
  Checking if a player is registered is easy with a sample stock.
  We start out with our query variable with 50 cells;
  The format as it says it selects the table "tutorial" and searches the username with the WHERE function.
  mysql_query send the actions we wan't, to the mysql server and returns values and we store it
  with mysql_store_result.
  The mysql_num_rows counts the rows returned from the query and if it is not zero the player is registered.
  After all this we Show the Dialogs to Register And Login.
  
  Explanation LoginPlayer stock:
  Note on This SELECT query the password uses the md5() function because of the encriptation,
  also the mysql strings uses always the ' '.
  Again we check if there are rows and we use the function mysql_fetch_row to fetch a whole
  row query. The "|" is a spliter to separete all field in the row.
  The sscanf paths the query values to the player enumeration,
  the characters s[24],s[32] for strings and "i" for intregers.

Now you just need to put this callbacks
on your code, remember to delete older callbacks if you have.

pawn Код:

public OnGameModeInit()
{
    mysql_init(); // To initialize.
    mysql_connect(MYSQL_HOST, MYSQL_USER, MYSQL_PASS, MYSQL_DB); // Our defines are used here to make the connection
    SetGameModeText("Mysql Tutorial");
    AddPlayerClass(0, 1958.3783, 1343.1572, 15.3746, 269.1425, 0, 0, 0, 0, 0, 0);
    return 1;
}
public OnGameModeExit()
{
    mysql_close();
    return 1;
}
public OnPlayerConnect(playerid)
{
    GetPlayerName(playerid, playerdb[playerid][username], 24); // username is sa-mp defined 24 cells
    IsUsernameRegistered(playerid, playerdb[playerid][username]);
    return 1;
}
public OnPlayerDisconnect(playerid, reason)
{
    SavePlayerStats(playerid);
    return 1;
}
public OnDialogResponse(playerid, dialogid, response, listitem, inputtext[])
{
    if(dialogid == REGISTER_DIALOG)
    {
        if(!response)
        {
            SendClientMessage(playerid, COLOR_GRAD,"You have been kicked duo the cancelation.");
            Kick(playerid);
        }
        new query[100]; 
        // The register syntax is INSERT INTO destination, the names of the fields and it's values.
        format(query, sizeof(query),"INSERT INTO playerdb (username,password,score,money) VALUES ('%s',md5('%s'),%d,%d)",playerdb[playerid][username],inputtext,1,5000);
        mysql_query(query);
        ShowPlayerDialog(playerid,LOGIN_DIALOG,DIALOG_STYLE_PASSWORD,"Login to Account.","Enter your password below:","Login","Cancel");
    }
    if(dialogid == LOGIN_DIALOG)
    {
        if(!response)
        {
            SendClientMessage(playerid, COLOR_GRAD,"You have been kicked duo the cancelation.");
            Kick(playerid);
        }
        LoginPlayer(playerid, inputtext);
    }
}

End of Toturial if you have any questions, problems relate it here.