SA-MP Forums

Go Back   SA-MP Forums > SA-MP Scripting and Plugins > Scripting Help > Tutorials

Reply
 
Thread Tools Display Modes
Old 01/10/2019, 05:18 PM   #1
StevenLV
Big Clucker
 
StevenLV's Avatar
 
Join Date: Apr 2017
Location: Pakistan
Posts: 122
Reputation: 7
Default Fetch offline player's data (MySQL R41-4)

Hi, Its Steven here. I was looking for a offline player stats checker command but couldn't see anywhere. I wanted to create one, but couldn't see any tutorial to get info of a user from MySQL database. So yea, I am creating this tutorial for you guys who are stuck at fetching data from MySQL.

So lets start?
So, for this thing you must include MySQL plugin and MySQL Include (both r41-4) to make this work!
PHP Code:
#include <a_mysql>                 //we are including it to use mysql in our gamemode 
Once you include it, we can now connect MySQL Database with Gamemode!
Now how to connect MySQL with gamemode or about more how to use MySQL, i will refer this: https://forum.sa-mp.com/showthread.php?t=644891

After you know how to connect MySQL database, I would like to move to the part where we can get data of Offline user.
I am currently using ZCMD and SSCANF so for this we have to include them as well, but it is upto you if you want to do it with them.
PHP Code:
#include <sscanf2>
#include <zcmd> 
SO, lemme move towards the part where we make such a cmd which get us the data!
Lets make it oplayerstats ? I made this for my own gm, so yea!
PHP Code:
CMD:oplayerstats(playerid,params[]) 
{
      return 
1;

I want to make this cmd to be restricted to admin use only so:
PHP Code:
if(!IsPlayerAdmin(playerid)) 
return 
SendClientMessage(playeridCOLOR_RED"You are not authorized to use this command!"); //If player is not admin (RCON) so it will show him only this message and returns! (No further thing happens) 
Then, as I am using SSCANF, i want to make the cmd like /oplayerstats <PLAYER_NAME> so obv I gotta do this code to return an error to the player for not typing any name:
PHP Code:
if(sscanf(params,"s"targetname)) 
return 
SendClientMessage(playerid,COLOR_GREY,"[SYNTAX]:/oplayerstats [Player Name]"); 
Now obv we will get error that whats targetname? lol, machine cant know what my heart wants? OK... we gotta tell him whats happening!
We will add these variables:
PHP Code:
new targetname
Then there comes the real thing! We have to do the real code here!
Before going further we would like to learn, how to use these two functions!
  • mysql_format:

    PHP Code:
    USAGEmysql_format(MySQL:handleoutput[], lenformat[], {Float,_}:...)

    /* MySQL:handle - The connection handle this will be processed on. 
    output[] - The string to save the result to. 
    len - The size of the output. 
    format[] - The format string. 
    {Float,_}:... - Indefinite number of arguments. */ 
  • cache_get_value_index:

    PHP Code:
    USAGEcache_get_value_index(row_idxcolumn_idxdestination[], max_len sizeof(destination))

    /* row_idx    - The row index (starts at '0').
    column_idx - The column index (starts at '0').
    destination[] - The string to store the data into.
    max_len - The max. size of the destination string (optional). */ 
  • cache_get_value_index_int:

    PHP Code:
    USAGEcache_get_value_index_int(row_idxcolumn_idx, &destination)
    /* row_idx - The row index (starts at '0'). 
    column_idx - The column index (starts at '0'). 
    &destination - The variable to store the number into. */ 

These are the functions we are going to use to get data of a offline user!
Important NOTE! In this tutorial I have used Indexes instead Names, Names can be used and are more efficient thanks to cSHARP for this!
  • cache_get_value_name:
    Usage:
    PHP Code:
    cache_get_value_name(row_idx, const column_name[], destination[], max_len sizeof(destination))
    /* row_idx - The row index (starts at '0').
    const column_name[] - The column name.
    destination[] - The string to store the data into.
    max_len - The size of the destination string (optional). */ 
  • cache_get_value_name_int:
    Usage:
    PHP Code:
    cache_get_value_name_int(row_idx, const column_name[], &destination)
    /* row_idx - The row index (starts at '0').
    const column_name[] - The column name.
    &destination - The variable to store the number into. */ 

Now how to do it? Let me tell you a mysql query ?:
PHP Code:
mysql_format(MySQLquerysizeof(query), "SELECT * FROM `playerdata` WHERE (`Username` = '%e')"targetname); 
Now we dont know what the hell is this? This is to Select the player's data! In here, I will explain this query. I am selecting a player data stored in a table named "playerdata" where player's name is "targetname". SO SIMPLE?

But you are getting errors, aren't you?
What the hell is MySQL? what is query? lol ?
so we gotta tell machine what they are!
PHP Code:
new query[256];      // Ok its a variable to store but what is [256]? it is length of the string! how much should be stored and i know u know that 
Machine is ok with the query. but mysql ?
PHP Code:
MySQL mysql_connect(mysql_hostmysql_usermysql_passwordmysql_database); 
This is a MySQL Handle! so everything is self explainatory... I have defined this in OnGamemodeInit so it connects to MySQL and u must have aswell.

We are good until now but, but how will we get the data lol? Machine isnt super wise? NO, we gotta fetch em!
Like lets make something that can hold this all thing? The query we did (mysql_format) do you even remember it? Ye we have to store those things somewhere? we just got the result from the query and now? Lets do it.
PHP Code:
new Cache:result// we creating a cache variable to store the player's data from Query. 
We have a variable WOW, but how to store?
PHP Code:
result mysql_tquery(MySQLquery); // there you go! we stored al things in one thing! 
But what if no such player exists / no table exists ?
We got it?
PHP Code:
if(cache_num_rows()) // This counts rows and if they exists so we do things! 
We have rows, IF, so we have to get all results right? so lets do this, shouldn't we?
PHP Code:
    cache_get_value_index(01pAccountName128);
    
cache_get_value_index_int(04pAdmLvl);
    
cache_get_value_index_int(05pBanned);
    
cache_get_value_index(06pBanReason128);
    
cache_get_value_index_int(07pKicks);
    
cache_get_value_index_int(08pWarns);
    
cache_get_value_index_int(09pLvl);
    
cache_get_value_index_int(010pMoney);
    
cache_get_value_index_int(011pTeam); 
I don't think we have to explain here anything as I wrote about these functions up there?
Still, I want to tell you, I am using same functions and same things like row id and column id etc like that! But WHY WE getting errors we did nothing wrong?
We missing something boi! we have to tell machine about these variable as well
PHP Code:
new pAccountName[128], pAdmLvlpBannedpBanReason[128], new pKickspWarnspLvl, new pMoneypTeam
But what if we are using names instead of indexes?
PHP Code:
cache_get_value_name(0UsernamepAccountName128); // I have added Column's name instead of number
cache_get_value_name_int(0AdminLevelpAdmLvl); // I have added Colum's name 
And this goes for others as well.

So we actually did whatever we actually had to do! we saved info. we gotta show it now! lets do this!
PHP Code:
format(pdstrsizeof(pdstr), "|| Name: %s || Admin Level = %d || Banned = %d || BanReason = %s || Kicks = %d ||"pAccountNamepAdmLvlpBannedpBanReasonpKicks);
    
SendClientMessage(playerid, -1pdstr);
    
format(pstringsizeof(pstring), "|| Warns: %d || Level = %d || Money = %d || Team = %d"pWarnspLvlpMoneypTeam);
    
SendClientMessage(playerid, -1pstring); 
Again same thing, why not tell machine?
PHP Code:
new pdstr[256], pstring[256]; 
Ok so we told em everything, we got everything! Lets move on? ... But maybe we need caches later? so we gotta delete stored things as well!
PHP Code:
cache_delete(result); 
Now we even!

So, finally we made it, didnt we?
The final CMD must be like this:
PHP Code:
CMD:oplayerstats(playerid,params[])
{
    new 
query[512], Cache:resulttargetname;

    if(!
IsPlayerDAdmin(playerid1)) return SendError(playerid"You are not authorized to use this command!");
    if(
sscanf(params,"s"targetname)) return SendClientMessage(playerid,COLOR_GREY,"[SYNTAX]:/oplayerstats [Player Name]");

    
mysql_format(MySQLquerysizeof(query), "SELECT * FROM `playerdata` WHERE (`Username` = '%e')"targetname);
    
result mysql_tquery(MySQLquery);
    if(
cache_num_rows())
    {
    new 
pAccountName[128], pAdmLvlpdstr[512], pstring[512];
    new 
pBannedpBanReason[128];
    new 
pKickspWarnspLvl;
    new 
pMoneypTeam;
    
cache_get_value_index(01pAccountName128);
    
cache_get_value_index_int(04pAdmLvl);
    
cache_get_value_index_int(05pBanned);
    
cache_get_value_index(06pBanReason128);
    
cache_get_value_index_int(07pKicks);
    
cache_get_value_index_int(08pWarns);
    
cache_get_value_index_int(09pLvl);
    
cache_get_value_index_int(010pMoney);
    
cache_get_value_index_int(011pTeam);

    
format(pdstrsizeof(pdstr), "|| Name: %s || Admin Level = %d || Banned = %d || BanReason = %s || Kicks = %d ||"pAccountNamepAdmLvlpBannedpBanReasonpKicks);
    
SendClientMessage(playerid, -1pdstr);
    
format(pstringsizeof(pstring), "|| Warns: %d || Level = %d || Money = %d || Team = %d"pWarnspLvlpMoneypTeam);
    
SendClientMessage(playerid, -1pstring);
    }
    else 
SendClientMessage(playerid, -1"Nothing found");
    
cache_delete(result);
    return 
1;

Thats what I am using on my own GM.

And, If you think this is not well-explained tell me i can modify it if needed. Also this isn't rip off of some old script etc I made it myself so plz refrain from it!
Thanks,
Regards.. Steven LV!

Thanks cSHARP for this help

Last edited by StevenLV; 01/10/2019 at 06:02 PM. Reason: Added name istead index, Thanks cSharp
StevenLV is offline   Reply With Quote
Old 01/10/2019, 05:41 PM   #2
cSharp
Big Clucker
 
Join Date: Oct 2018
Posts: 82
Reputation: 27
Default Re: Fetch offline player's data (MySQL R41-4)

Why would you ever uses indexes over names? Say you in teh future modify your table containing the player data, e.g. going from single-used salts to new salts per password/per player. You'd have to add a salt field, right? Say you add the column after the password one. Now your whole script is fucked up.


Use cache_get_value_name_* over cache_get_value_index_*.



E:
What a waste of cells, "pdstr[512]", a message cannot exceed 144 characters, that is MAX_PLAYER_NAME (24) + 129 (SAMP chatbox max field input + PAWN cell placeholder) = 144; unless you're using another function to divide messages into several when they go over 144. Either way, you can calculate the maximum size of your strings. Using "512" is laziness.
cSharp is offline   Reply With Quote
Old 01/10/2019, 05:44 PM   #3
StevenLV
Big Clucker
 
StevenLV's Avatar
 
Join Date: Apr 2017
Location: Pakistan
Posts: 122
Reputation: 7
Default Re: Fetch offline player's data (MySQL R41-4)

Actually I am defining the Password's row and column is this so it will fetch it? what is wrong in this? And If i add some other column so Obv i gotta set where the hell they exist?
__________________
Quote:
Originally Posted by Milton
/* Website developing and Designing at cheap rates, PM ME! */

/* Currently looking for staff / scripting helper! Hit me with a dm on Discord: Milton#0939 */
Quote:
Originally Posted by !damo!spiderman View Post
You're pathetic. Stand for something that's going to help the world rather than fill it with more hate. Give someone a hug, tell someone you care... Do something, do anything but do it with love not hate.
StevenLV is offline   Reply With Quote
Old 01/10/2019, 05:47 PM   #4
StevenLV
Big Clucker
 
StevenLV's Avatar
 
Join Date: Apr 2017
Location: Pakistan
Posts: 122
Reputation: 7
Default Re: Fetch offline player's data (MySQL R41-4)

By the way, what you are saying is also more efficient! I dont have to change this once I use name instead index! Thats the point
__________________
Quote:
Originally Posted by Milton
/* Website developing and Designing at cheap rates, PM ME! */

/* Currently looking for staff / scripting helper! Hit me with a dm on Discord: Milton#0939 */
Quote:
Originally Posted by !damo!spiderman View Post
You're pathetic. Stand for something that's going to help the world rather than fill it with more hate. Give someone a hug, tell someone you care... Do something, do anything but do it with love not hate.
StevenLV is offline   Reply With Quote
Old 01/10/2019, 05:50 PM   #5
cSharp
Big Clucker
 
Join Date: Oct 2018
Posts: 82
Reputation: 27
Default Re: Fetch offline player's data (MySQL R41-4)

cache_get_value_index(0, 1, pAccountName, 12;
cache_get_value_index_int(0, 4, pAdmLvl);
cache_get_value_index_int(0, 5, pBanned);
cache_get_value_index(0, 6, pBanReason, 12;
cache_get_value_index_int(0, 7, pKicks);
cache_get_value_index_int(0, 8, pWarns);
cache_get_value_index_int(0, 9, pLvl);
cache_get_value_index_int(0, 10, pMoney);
cache_get_value_index_int(0, 11, pTeam);


Say I add a new field next to money called skin in the database. What now? The skin will be loaded as the team instead, because that's index 11 while team would become index 12.

Never rely on indexes.

E: what the fuck?

"mysql_query", use threaded queries!

PSA for everyone: do not follow this tutorial unless you want a totally tick deciding script which is gonna screw you over once your database times out for a single half second.

https://forum.sa-mp.com/showthread.php?t=647850
cSharp is offline   Reply With Quote
Old 01/10/2019, 06:04 PM   #6
StevenLV
Big Clucker
 
StevenLV's Avatar
 
Join Date: Apr 2017
Location: Pakistan
Posts: 122
Reputation: 7
Default Re: Fetch offline player's data (MySQL R41-4)

I actually thank you for your help, and yet This tutorial doesnt screw you... You change your database so You must change the index as well! I explained everything to my knowledge, There wasn't any tutorial so i wanted to help others... Also there are mistakes, i know, I corrected them... What seniors are for? They are here to help so you obv need to correct me BUT this doesnt screw my db, it never does
__________________
Quote:
Originally Posted by Milton
/* Website developing and Designing at cheap rates, PM ME! */

/* Currently looking for staff / scripting helper! Hit me with a dm on Discord: Milton#0939 */
Quote:
Originally Posted by !damo!spiderman View Post
You're pathetic. Stand for something that's going to help the world rather than fill it with more hate. Give someone a hug, tell someone you care... Do something, do anything but do it with love not hate.
StevenLV is offline   Reply With Quote
Old 01/10/2019, 06:42 PM   #7
cSharp
Big Clucker
 
Join Date: Oct 2018
Posts: 82
Reputation: 27
Default Re: Fetch offline player's data (MySQL R41-4)

"me BUT this doesnt screw my db, it never does"

oh little do you know that in real-time performance servers there's gonna be delays because of the massive amount of data being managed, that's however not an issue as long as there's a 0ms ping interval. That changes once you get 50 players constantly performing actions that results in database queries being sent out.
cSharp is offline   Reply With Quote
Reply

Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Help with Offline Player Data Loading in Mysql (/oplayerstats) StevenLV Scripting Help 3 16/09/2019 08:04 PM
loading offline player data with mysql penaut Scripting Help 3 18/08/2019 04:28 PM
mysql can't fetch data or ? venomlivno8 Scripting Help 2 03/09/2016 01:38 AM
Loading offline player Data (MySQL System) Juvanii Scripting Help 3 30/07/2016 02:34 PM


All times are GMT. The time now is 09:49 AM.


Powered by vBulletin® Version 3.8.6
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.