MYSQL Getting and inserting - Printable Version

MYSQL Getting and inserting - Printable Version

+- SA-MP Forums Archive (https://sampforum.blast.hk)
+-- Forum: SA-MP Scripting and Plugins (https://sampforum.blast.hk/forumdisplay.php?fid=8)
+--- Forum: Scripting Help (https://sampforum.blast.hk/forumdisplay.php?fid=12)
+---- Forum: Help Archive (https://sampforum.blast.hk/forumdisplay.php?fid=89)
+---- Thread: MYSQL Getting and inserting (/showthread.php?tid=228832)

MYSQL Getting and inserting - AlExAlExAlEx - 20.02.2011

Okay so this MYSQL thing is so complicated.

pawn Code:

public OnPlayerConnect
ResetPVars(playerid);
InitConnection(playerid);

public OnPlayerDisconnect
SavePInfo(playerid);

public OnDialogResponse(playerid, dialogid, response, listitem, inputtext[])
{
    switch(dialogid)
    {
        case REGISTER:
        {
            if(!response)
            {
                format(Msg, sizeof(Msg), ""#CBLUE"Player: "#CORANGE"%s(%d) "#CBLUE"has been kicked - Reason: Not registering!", pName(playerid), playerid);
                SendClientMessageToAll(COLOR_GREY, Msg);
                Kick(playerid);
            }
            else
            {
                if(!strlen(inputtext)) DialogInput(playerid, REGISTER, ""#CCADET"Account Registration", ""CYELLOW"You must enter a password below to continue!", "Register", "Leave");

                new
                    Query[100],
                    EscPass[30],
                    EscName[MAX_PLAYER_NAME];

                mysql_real_escape_string(pName(playerid), EscName);
                mysql_real_escape_string(inputtext, EscPass);

                format(Query, sizeof(Query), "INSERT INTO `playerinfo` (`user`, `password`) VALUES ('%s', '%s')", EscName, EscPass);
                mysql_query(Query);

                // Player has registered

                SendClientMessage(playerid, COLOR_CADETBLUE, "You are now registered!");
                GivePlayerMoney(playerid, 5000);
                
                SetPVarInt(playerid, "LoggedIN", 1); // I use this for cross script
            }
        }
        case LOGIN:
        {
            if(!response)
            {
                format(Msg, sizeof(Msg), ""#CBLUE"Player: "#CORANGE"%s(%d) "#CBLUE"has been kicked - Reason: Not loggin in!", pName(playerid), playerid);
                SendClientMessageToAll(COLOR_GREY, Msg);
                Kick(playerid);
            }
            else
            {
                if(!strlen(inputtext)) DialogInput(playerid, LOGIN, ""#CCADET"Account Login", ""#CYELLOW"You must enter a password below to continue!", "Login", "Leave");

                new
                    EscPass[38],
                    Query[128];

                mysql_real_escape_string(inputtext, EscPass);

                format(Query, sizeof(Query), "SELECT * FROM `playerinfo` WHERE `user` = '%s' AND `password` = '%s'", pName(playerid), EscPass);

                mysql_query(Query);
                mysql_store_result();

                if(mysql_num_rows() > 0)
                {
                    LoadPlayerInfo(playerid);
                    SetPVarInt(playerid, "LoggedIN", 1);

                    // Player Has logged in

                    SendClientMessage(playerid, LIGHTBLUE2, "SERVER: Welcome, You successfully logged in."),
                    SendClientMessage(playerid, orange, "Enjoy your stay here, press left shift to spawn.");
                    
                }
                else
                {
                    SendClientMessage(playerid, COLOR_RED, "You have entered an incorrect password, try again!");
                    DialogInput(playerid, LOGIN, ""#CCADET"Account Login", ""#CYELLOW"You have 3 chances to enter a correct password..", "Login", "Leave");
                }
            }
        }
    }
    return 1;
}

stock LoadPlayerInfo(iPlayer)
{
    new
        Query[700];

    if(mysql_fetch_row(Query))
    {
        sscanf(Query, "e<p<|>s[24]s[35]ddddfffd>", PVar[iPlayer]); // Remember to update this if you add more info...
        mysql_free_result();
    }
    return 1;
}

stock ResetPVars(playerid)
{
    PVar[playerid][pLevel]      = 0;
    PVar[playerid][pKills]      = 0;
    PVar[playerid][pDeaths]     = 0;
    PVar[playerid][pMoney]      = 0;
    PVar[playerid][pLastX]      = 0.0;
    PVar[playerid][pLastY]      = 0.0;
    PVar[playerid][pLastZ]      = 0.0;
    PVar[playerid][pInt]        = 0;
}

stock InitConnection(playerid)
{
    new
        Query[90],
        EscName[MAX_PLAYER_NAME];

    mysql_real_escape_string(pName(playerid), EscName);

    format(Query, sizeof(Query), "SELECT * FROM `playerinfo` WHERE `user` = '%s'", EscName);

    mysql_query(Query);
    mysql_store_result();

    if(mysql_num_rows() != 0)
    {
        format(Msg, sizeof(Msg), ""#CYELLOW"Welcome Back: "#CORANGE"%s(%d)\n"#CYELLOW"Please enter your password below to log onto your account..:", pName(playerid), playerid);
        DialogInput(playerid, LOGIN, ""#CCADET"Account Login", Msg, "Login", "Leave");
    }
    else
    {
        format(Msg, sizeof(Msg), ""#CYELLOW"Hello: "#CORANGE"%s(%d)\n"#CYELLOW"This account is not registered! Enter a password below to continue..:", pName(playerid), playerid);
        DialogInput(playerid, REGISTER, ""#CCADET"Account Registration", Msg, "Register", "Leave");
    }
    mysql_free_result();
}

stock SavePInfo(playerid)
{
    if(GetPVarInt(playerid, "LoggedIN") == 1)
    {
        new Query[600];

        format(Query, sizeof(Query), "UPDATE `playerinfo` SET `kills` = %d, `deaths` = %d, `money` = %d, `Level` = %d, `Last Pos X` = %f, `Last Pos Y` = %f, `Last Pos Z` = %f, `Interior` = %d WHERE `user` = '%s'", // Also remember to update this...

        PVar[playerid][pKills],
        PVar[playerid][pDeaths],
        GetPlayerMoney(playerid),
        PVar[playerid][pLevel],
        PVar[playerid][pLastX],
        PVar[playerid][pLastY],
        PVar[playerid][pLastZ],
        GetPlayerInterior(playerid),
        pName(playerid));

        mysql_query(Query);
        mysql_free_result();
        return 1;
    }
    else return 0;
}

So, looking at this "mountain" of codes, i really can't figure out how to add 1 more field(i did in the MYSQL table), so much coding..
And don't get me wrong, i don't want someone to just paste me the right code, i want to learn how to do it.So can someone please point me the right steps in doing it ?

Thanks.

Re: MYSQL Getting and inserting - JaTochNietDan - 21.02.2011

Well FIRST of all you need to actually add an extra column in the MySQL table, did you add that yet? You generally use some sort of MySQL manager to sort things like that out, it makes things a lot easier and quicker, something like PhpMyAdmin or my favorite, Navicat.

Then you need to update your queries, for example if you added a new column to your table called "cow", you would update this query like so:

pawn Code:

format(Query, sizeof(Query), "UPDATE `playerinfo` SET `kills` = %d, `deaths` = %d, `money` = %d, `Level` = %d, `Last Pos X` = %f, `Last Pos Y` = %f, `Last Pos Z` = %f, `Interior` = %d, `cow` = %d WHERE `user` = '%s'", // Also remember to update this...

        PVar[playerid][pKills],
        PVar[playerid][pDeaths],
        GetPlayerMoney(playerid),
        PVar[playerid][pLevel],
        PVar[playerid][pLastX],
        PVar[playerid][pLastY],
        PVar[playerid][pLastZ],
        GetPlayerInterior(playerid),
        PVar[playerid][cow],
        pName(playerid));

Additionally you need to create that extra variable in the enumeration.

Finally you need to modify your sscanf structure a bit for stripping the code and storing them in the assorted variables, so using the same example, it would need to be:

pawn Code:

sscanf(Query, "e<p<|>s[24]s[35]ddddfffdd>", PVar[iPlayer]);

It even happens to tell you in the code you pasted what you need to update, we add an extra d to that sscanf function to ensure that the new field you added is split from the query string and stored.

It really isn't difficult to understand, I recommend you read the official Pawn language documentation on the CompuPhase website, and some MySQL documentation on their official website. Having simple knowledge of what the functions are doing will make it very easy for you to dynamically write your own code and understand it.

Re: MYSQL Getting and inserting - AlExAlExAlEx - 21.02.2011

Hello,

Thanks for helping me, so pointing out my "didn't understood" things, first.

pawn Code:

sscanf(Query, "e<p<|>s[24]s[35]ddddfffdd>", PVar[iPlayer]);

so d stands for ? like why some of them are f and s and some d?

Also i noticed that you left the

pawn Code:

WHERE `user` = '%s'",

Is that mandatory ?

Re: MYSQL Getting and inserting - JaTochNietDan - 21.02.2011

Yes, it specifies what row to update in the MySQL table. In this case it's updating the rows that where user is, Alex, for example.

Also the d is for inserting integers, so this is assuming that all you're going to be putting into that is an integer. Otherwise it will not function as intended for strings, you would need to use s for that. A full list of the identifiers in sscanf are available in the official sscanf topic.

Re: MYSQL Getting and inserting - AlExAlExAlEx - 21.02.2011

Hmmm, just tried it out, it dosen't work..at least the first thing i tried to test.

pawn Code:

//----------/setvip-------------------------
//------------------------------------------
CMD:setvip(playerid, params[])
{
    if(PVar[playerid][pLevel] >= 5)
    {
        new pID, Level;
        if(sscanf(params, "ud", pID, Level)) return SendClientMessage(playerid, COLOR_RED, "ERROR: Use /setvip [1/0]");
        if(!IsPlayerConnected(pID)) return SendClientMessage(playerid, COLOR_RED, "* That user is not connected!");

        if(GetPVarInt(pID, "LoggedIN") == 1)
        {
        SendFMessage(playerid, COLOR_RED, "You have given %s VIP status", pName(playerid));
        SendFMessageToAll(COLOR_LIGHTBLUE, "Lead Admin %s(%d) has given %s(%d) VIP status!", pName(playerid), playerid, pName(pID), pID);
    
        PVar[pID][pLevel] = Level;
        }
        else return SendClientMessage(playerid, COLOR_RED, "* That user does not have an account!");
    }
    else return SendClientMessage(playerid, COLOR_RED, "Only admin level 5 commands!");
    return 1;
}
//=========================

stock LoadPlayerInfo(iPlayer)
{
    new Query[700];

    if(mysql_fetch_row(Query))
    {
        //added dsssd
        sscanf(Query, "e<p<|>s[24]s[35]ddddfffddsssd>", PVar[iPlayer]); // Remember to update this if you add more info...
        mysql_free_result();
    }
    return 1;
}

=====================

stock SavePInfo(playerid)
{
    if(GetPVarInt(playerid, "LoggedIN") == 1)
    {
        new Query[600];

        format(Query, sizeof(Query), "UPDATE `playerinfo` SET `kills` = %d, `deaths` = %d, `money` = %d, `Level` = %d, `Last Pos X` = %f, `Last Pos Y` = %f, `Last Pos Z` = %f, `Interior` = %d, `warns` = %d, `warn1` = %f, `warn2` = %f, `warn3` = %f, `vip` =%d, WHERE `user` = '%s'", // Also remember to update this...

        PVar[playerid][pKills],
        PVar[playerid][pDeaths],
        GetPlayerMoney(playerid),
        PVar[playerid][pLevel],
        PVar[playerid][pLastX],
        PVar[playerid][pLastY],
        PVar[playerid][pLastZ],
        GetPlayerInterior(playerid),
        PVar[playerid][pWarns],
        PVar[playerid][pVip],
        pName(playerid));

        mysql_query(Query);
        mysql_free_result();
        return 1;
    }
    else return 0;
}

=================

stock ResetPVars(playerid)
{
    PVar[playerid][pLevel]      = 0;
    PVar[playerid][pKills]      = 0;
    PVar[playerid][pDeaths]     = 0;
    PVar[playerid][pMoney]      = 0;
    PVar[playerid][pLastX]      = 0.0;
    PVar[playerid][pLastY]      = 0.0;
    PVar[playerid][pLastZ]      = 0.0;
    PVar[playerid][pInt]        = 0;
    PVar[playerid][pWarns]      = 0;
    PVar[playerid][pVip]        = 0;
}

The VIP is always 0 in the database.
So it inserts it(it was nothing in it at the first) just it ain't doing it properly.

Re: MYSQL Getting and inserting - JaTochNietDan - 21.02.2011

You never set this variable "PVar[playerid][pVip]" to anything other than 0 in this code you displayed?

The only variable you set was PVar[playerid][pLevel], but that is not the one you are writing into the database, so of course if you're not setting the variable "PVar[playerid][pVip]", it will initialize as 0 and stay as 0. Then it will be 0 when you insert it into the database.

Re: MYSQL Getting and inserting - AlExAlExAlEx - 21.02.2011

OH! **** I'm tired, i confused the pLevel with pVip, il test again.

EDIT:

Okay i just tried with that line fixed and with

pawn Code:

//------------------------------------------
CMD:vtest(playerid, params[])
{
    if(PVar[playerid][pVip] == 1)
    {
        SendClientMessage(playerid, orange, "You are vip level 1");
    }
    else return SendClientMessage(playerid, orange, "You are not an VIP");
    return 1;
}

So here's the thing, it works while im ingame, but if i /q and re-login it dosen't anymore, so it dosen't save it ?

Re: MYSQL Getting and inserting - JaTochNietDan - 21.02.2011

Did you check in the MySQL table if it was saved as 1 after you left the server? Then we'll know if it saved correctly or not.

Re: MYSQL Getting and inserting - AlExAlExAlEx - 21.02.2011

Yes i checked, its 0 in the database.

Re: MYSQL Getting and inserting - JaTochNietDan - 21.02.2011

Do you have SavePInfo(playerid); in OnPlayerDisconnect?

Also are you sure that you're setting the right variable this time?