Saving to DB from Player enum. Possible cause of fault?

Saving to DB from Player enum. Possible cause of fault? - 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)
+--- Thread: Saving to DB from Player enum. Possible cause of fault? (/showthread.php?tid=375506)

Saving to DB from Player enum. Possible cause of fault? - Deal-or-die - 07.09.2012

The Saving Stock

pawn Код:

stock SavePlayerAccount(playerid)
{
    if(IsPlayerConnected(playerid))
    {
        new EscapedName[MAX_PLAYER_NAME];
        mysql_real_escape_string(GetName(playerid), EscapedName);
        //UpdatePlayerVariables(playerid);
        if(CheckExist(playerid) == 0)
        {
            format(query, sizeof(query), "INSERT INTO `Accounts` (Username, Password) VALUES('%s', '%s')",EscapedName, Player[playerid][pPassword]);
            mysql_query(query);
        }
        format(query, sizeof(query), "UPDATE Accounts SET AccountID=%d, Username='%s', Password='%s', Money=%d, Level=%d, Skin=%d, Health=%f, Armour=%f, PosX=%f, PosY=%f, PosZ=%f, Angle=%f, Ethnicity=%d, Gender=%d, Age=%d, DefaultSpawn=%d, PhoneType=%d, PhoneNumber=%d, World=%d, Interior=%d, VehSlots=%d, Admin=%d WHERE playerid=%d",
        Player[playerid][pAccountID],Player[playerid][pUsername],Player[playerid][pPassword],Player[playerid][pMoney],Player[playerid][pLevel],Player[playerid][pSkin],Player[playerid][pHealth],Player[playerid][pArmour],
        Player[playerid][pPosX],Player[playerid][pPosY],Player[playerid][pPosZ],Player[playerid][pAngle],Player[playerid][pEthnicity],Player[playerid][pGender],Player[playerid][pAge],Player[playerid][pDefaultSpawn],Player[playerid][pPhoneType],
        Player[playerid][pPhoneNumber],Player[playerid][pWorld], Player[playerid][pInterior],Player[playerid][pVehSlots],Player[playerid][pAdmin],Player[playerid][pAccountID]);
        mysql_query(query);
        SaveContacts(playerid);
    }
}

I also tried this

pawn Код:

format(query, sizeof(query), "UPDATE `Accounts` SET `Username` = '%s', `Password` = '%s', `Money` = '%d', `Level` = '%d', `Skin` = '%d' WHERE `AccountID` = '%d'", Player[playerid][pUsername], Player[playerid][pPassword], Player[playerid][pMoney], Player[playerid][pLevel], Player[playerid][pSkin], Player[playerid][pAccountID]);
        mysql_query(query);
        format(query,sizeof(query), "UPDATE `Accounts` SET `Health` = '%f', `Armour` = '%f', `PositionX` = '%f', `PositionY` = '%f', `PositionZ` = '%f', `Angle` = '%f' WHERE `AccountID` = '%d'", Player[playerid][pHealth],Player[playerid][pArmour],Player[playerid][pPosX],Player[playerid][pPosY],Player[playerid][pPosZ],Player[playerid][pAngle],Player[playerid][pAccountID]);
        mysql_query(query);
        format(query,sizeof(query), "UPDATE `Accounts` SET `Ethnicity` = '%d', `Gender` = '%d', `Age` = '%d', `Locked` = '%d', `DefaultSpawn` = '%d', `PhoneType` = '%d', `PhoneNumber` = '%d' WHERE `AccountID` = '%d'", Player[playerid][pEthnicity],Player[playerid][pEthnicity],Player[playerid][pAge],Player[playerid][pDefaultSpawn],Player[playerid][pPhoneType],Player[playerid][pPhoneNumber],Player[playerid][pAccountID]);
        mysql_query(query);
        format(query,sizeof(query), "UPDATE `Accounts` SET `World` = '%d', `Interior` = '%d', `VehSlots` = '%d', `Admin` = '%d', `AdminDuty` = '%d', WHERE `AccountID` = '%d'", Player[playerid][pWorld],Player[playerid][pInterior],Player[playerid][pVehSlots],Player[playerid][pAdmin],Player[playerid][pAdminDuty],Player[playerid][pAccountID]);
        mysql_query(query);
        SaveContacts(playerid);

The Player Enum

pawn Код:

enum PlayerData
{
    pAccountID,
    pUsername[23],
    pPassword[146],
    pMoney,
    pLevel,
    pSkin,
    Float:pHealth,
    Float:pArmour,
    Float:pPosX,
    Float:pPosY,
    Float:pPosZ,
    Float:pAngle,
    pEthnicity,
    pGender,
    pAge,
    pDefaultSpawn,
    pPhoneType,
    pPhoneNumber,
    pWorld,
    pInterior,
    pVehSlots,
    pAdmin,
    pAdminDuty,
    pJob,
    pFaction
};

Database Structure (http://www.vs-rp.com/db.png)

Database Players (http://www.vs-rp.com/dbacc.png)

The problem? I have no idea... :/
Everything is being saved as default.

(I have called 'SavePlayerAccount(playerid);' on the player disconnect. )

Re: Saving to DB from Player enum. Possible cause of fault? - mkr - 07.09.2012

It seems to me that the problem is you aren't updating pAccountID with the ID that they're assigned in the database. AccountID needs to be the primary key in your table, and after your insert statement you should set Player[playerid][pAccountID] to mysql_insert_row(), or whatever the equivalent function is in the plugin you're using. (Ideally you should be threading these queries but that can wait for later.)

The first query isn't correct though, it should be WHERE AccountID = %d, not WHERE playerid = %d. The second set of queries, where you update in pieces, looks correct but unless I'm missing something, each one of those queries is trying to update WHERE AccountID = 0, which isn't going to do anything.

You should echo those queries to the console just to be sure. Having phpmyadmin is nice because you can just feed them into it and it'll cough out any error messages that the queries generate.

Re: Saving to DB from Player enum. Possible cause of fault? - Deal-or-die - 07.09.2012

Quote:

Originally Posted by mkr

It seems to me that the problem is you aren't updating pAccountID with the ID that they're assigned in the database.

Indeed.

Quote:

Originally Posted by mkr

AccountID needs to be the primary key in your table

Is now.

Quote:

Originally Posted by mkr

and after your insert statement you should set Player[playerid][pAccountID] to mysql_insert_row()

Hmm.. possible if I could grab an example off ya? I'm not sure what plugin I am using

Quote:

Originally Posted by mkr

each one of those queries is trying to update WHERE AccountID = 0, which isn't going to do anything.

Any ideas how to get the AccountId for the player then?
Maybe like this?

pawn Код:

stock SavePlayerAccount(playerid)
{
    if(IsPlayerConnected(playerid))
    {
    new x;
    new EscapedName[MAX_PLAYER_NAME];
    mysql_real_escape_string(GetName(playerid), EscapedName);
    //UpdatePlayerVariables(playerid);
    while(mysql_retrieve_row())
    {
        mysql_get_field("AccountID", DataResult);
        x = strval(DataResult);
    }
        if(CheckExist(playerid) == 0)
        {
            format(query, sizeof(query), "INSERT INTO `Accounts` (Username, Password) VALUES('%s', '%s')",EscapedName, Player[playerid][pPassword]);
            mysql_query(query);
        }
        format(query, sizeof(query), "UPDATE Accounts SET AccountID=%d, Username='%s', Password='%s', Money=%d, Level=%d, Skin=%d, Health=%f, Armour=%f, PosX=%f, PosY=%f, PosZ=%f, Angle=%f, Ethnicity=%d, Gender=%d, Age=%d, DefaultSpawn=%d, PhoneType=%d, PhoneNumber=%d, World=%d, Interior=%d, VehSlots=%d, Admin=%d WHERE AccountID=%d",
        Player[playerid][pAccountID],Player[playerid][pUsername],Player[playerid][pPassword],Player[playerid][pMoney],Player[playerid][pLevel],Player[playerid][pSkin],Player[playerid][pHealth],Player[playerid][pArmour],
        Player[playerid][pPosX],Player[playerid][pPosY],Player[playerid][pPosZ],Player[playerid][pAngle],Player[playerid][pEthnicity],Player[playerid][pGender],Player[playerid][pAge],Player[playerid][pDefaultSpawn],Player[playerid][pPhoneType],
        Player[playerid][pPhoneNumber],Player[playerid][pWorld], Player[playerid][pInterior],Player[playerid][pVehSlots],Player[playerid][pAdmin],x);
        mysql_query(query);
        SaveContacts(playerid);
    }
}

Quote:

Originally Posted by mkr

You should echo those queries to the console just to be sure. Having phpmyadmin is nice because you can just feed them into it and it'll cough out any error messages that the queries generate.

Running my Server through putty and doesn't seem to show the server logs (Linux Server)

Thanks for your help so far.

Re: Saving to DB from Player enum. Possible cause of fault? - mkr - 07.09.2012

I messed up, it's mysql_insert_id(). There's an extremely simple example here that should help. I would do something like this:

pawn Код:

stock SavePlayerAccount(playerid)
{
    if(IsPlayerConnected(playerid))
    {
        new EscapedName[MAX_PLAYER_NAME];
        mysql_real_escape_string(GetName(playerid), EscapedName);
        //UpdatePlayerVariables(playerid);
        if(CheckExist(playerid) == 0)
        {
            format(query, sizeof(query), "INSERT INTO `Accounts` (Username, Password) VALUES('%s', '%s')",EscapedName, Player[playerid][pPassword]);
            mysql_query(query);
            Player[playerid][pAccountID] = mysql_insert_id();
        }
        format(query, sizeof(query), "UPDATE Accounts SET AccountID=%d, Username='%s', Password='%s', Money=%d, Level=%d, Skin=%d, Health=%f, Armour=%f, PosX=%f, PosY=%f, PosZ=%f, Angle=%f, Ethnicity=%d, Gender=%d, Age=%d, DefaultSpawn=%d, PhoneType=%d, PhoneNumber=%d, World=%d, Interior=%d, VehSlots=%d, Admin=%d WHERE AccountID=%d",
        Player[playerid][pAccountID],Player[playerid][pUsername],Player[playerid][pPassword],Player[playerid][pMoney],Player[playerid][pLevel],Player[playerid][pSkin],Player[playerid][pHealth],Player[playerid][pArmour],
        Player[playerid][pPosX],Player[playerid][pPosY],Player[playerid][pPosZ],Player[playerid][pAngle],Player[playerid][pEthnicity],Player[playerid][pGender],Player[playerid][pAge],Player[playerid][pDefaultSpawn],Player[playerid][pPhoneType],
        Player[playerid][pPhoneNumber],Player[playerid][pWorld], Player[playerid][pInterior],Player[playerid][pVehSlots],Player[playerid][pAdmin],Player[playerid][pAccountID]);
        mysql_query(query);
        SaveContacts(playerid);
    }
}

Disclaimer: doing unthreaded MySQL queries will make your gamemode hang while it waits for your mysql server to respond. This is not the most optimal way of using MySQL.

Also, about the server log, on Linux I use this command:

Код:

tail -f server_log.txt

to monitor mine.

Re: Saving to DB from Player enum. Possible cause of fault? - Deal-or-die - 08.09.2012

Shit....
This MySQL crap is doing my head in... The database row 'AccountID' is already set to 'AUTO_INCREMENT' so it already sets that automatically so basically all I want is for the script to get the AccountID from the database...

If you know what i mean...?

Re: Saving to DB from Player enum. Possible cause of fault? - mkr - 08.09.2012

Yeah, I know what you mean. Try the code I suggested, particularly this part:

pawn Код:

Player[playerid][pAccountID] = mysql_insert_id();

after the INSERT statement.

Re: Saving to DB from Player enum. Possible cause of fault? - Deal-or-die - 10.09.2012

Everything is still getting reset to default.

pawn Код:

stock SavePlayerAccount(playerid)
{
    if(IsPlayerConnected(playerid))
    {
        new EscapedName[MAX_PLAYER_NAME];
        mysql_real_escape_string(GetName(playerid), EscapedName);
        //UpdatePlayerVariables(playerid);
        if(CheckExist(playerid) == 0)
        {
            format(query, sizeof(query), "INSERT INTO `Accounts` (Username, Password) VALUES('%s', '%s')",EscapedName, Player[playerid][pPassword]);
            mysql_query(query);
            Player[playerid][pAccountID] = mysql_insert_id();
        }
        format(query, sizeof(query), "UPDATE Accounts SET AccountID=%d, Username='%s', Password='%s', Money=%d, Level=%d, Skin=%d, Health=%f, Armour=%f, PosX=%f, PosY=%f, PosZ=%f, Angle=%f, Ethnicity=%d, Gender=%d, Age=%d, DefaultSpawn=%d, PhoneType=%d, PhoneNumber=%d, World=%d, Interior=%d, VehSlots=%d, Admin=%d WHERE AccountID=%d",
        Player[playerid][pAccountID],Player[playerid][pUsername],Player[playerid][pPassword],Player[playerid][pMoney],Player[playerid][pLevel],Player[playerid][pSkin],Player[playerid][pHealth],Player[playerid][pArmour],
        Player[playerid][pPosX],Player[playerid][pPosY],Player[playerid][pPosZ],Player[playerid][pAngle],Player[playerid][pEthnicity],Player[playerid][pGender],Player[playerid][pAge],Player[playerid][pDefaultSpawn],Player[playerid][pPhoneType],
        Player[playerid][pPhoneNumber],Player[playerid][pWorld], Player[playerid][pInterior],Player[playerid][pVehSlots],Player[playerid][pAdmin],Player[playerid][pAccountID]);
        mysql_query(query);
        SaveContacts(playerid);
    }
    return 1;
}

Database - [www.vs-rp.com/account.png]

Re: Saving to DB from Player enum. Possible cause of fault? - Deal-or-die - 13.09.2012

That also would only save for accounts that do not exist. So any possible fix to save the existing accounts?

Would using 'mysql_insert_id' after my 'UPDATE' query work?

Example:

pawn Код:

stock SavePlayerAccount(playerid)
{
    if(IsPlayerConnected(playerid))
    {
        new EscapedName[MAX_PLAYER_NAME];
        mysql_real_escape_string(GetName(playerid), EscapedName);
        //UpdatePlayerVariables(playerid);
        if(CheckExist(playerid) == 0)
        {
            format(query, sizeof(query), "INSERT INTO `Accounts` (Username, Password) VALUES('%s', '%s')",EscapedName, Player[playerid][pPassword]);
            mysql_query(query);
            Player[playerid][pAccountID] = mysql_insert_id();
        }
        format(query, sizeof(query), "UPDATE Accounts SET AccountID=%d, Username='%s', Password='%s', Money=%d, Level=%d, Skin=%d, Health=%f, Armour=%f, PosX=%f, PosY=%f, PosZ=%f, Angle=%f, Ethnicity=%d, Gender=%d, Age=%d, DefaultSpawn=%d, PhoneType=%d, PhoneNumber=%d, World=%d, Interior=%d, VehSlots=%d, Admin=%d WHERE AccountID=%d",
        Player[playerid][pAccountID],Player[playerid][pUsername],Player[playerid][pPassword],Player[playerid][pMoney],Player[playerid][pLevel],Player[playerid][pSkin],Player[playerid][pHealth],Player[playerid][pArmour],
        Player[playerid][pPosX],Player[playerid][pPosY],Player[playerid][pPosZ],Player[playerid][pAngle],Player[playerid][pEthnicity],Player[playerid][pGender],Player[playerid][pAge],Player[playerid][pDefaultSpawn],Player[playerid][pPhoneType],
        Player[playerid][pPhoneNumber],Player[playerid][pWorld], Player[playerid][pInterior],Player[playerid][pVehSlots],Player[playerid][pAdmin],Player[playerid][pAccountID]);
        mysql_query(query);
        Player[playerid][pAccountID] = mysql_insert_id();
        SaveContacts(playerid);
    }
    return 1;
}

or would I have to use something like this.

pawn Код:

stock SavePlayerAccount(playerid)
{
    if(IsPlayerConnected(playerid))
    {
        new EscapedName[MAX_PLAYER_NAME];
        mysql_real_escape_string(GetName(playerid), EscapedName);
        //UpdatePlayerVariables(playerid);
        if(CheckExist(playerid) == 0)
        {
            format(query, sizeof(query), "INSERT INTO `Accounts` (Username, Password) VALUES('%s', '%s')",EscapedName, Player[playerid][pPassword]);
            mysql_query(query);
            Player[playerid][pAccountID] = mysql_insert_id();
        }
        mysql_query("SELECT AccountID FROM Accounts where Username = '%s'", EscapedName);
        mysql_store_result();
        Player[playerid][pAccountID] = mysql_fetch_int();
        format(query, sizeof(query), "UPDATE Accounts SET AccountID=%d, Username='%s', Password='%s', Money=%d, Level=%d, Skin=%d, Health=%f, Armour=%f, PosX=%f, PosY=%f, PosZ=%f, Angle=%f, Ethnicity=%d, Gender=%d, Age=%d, DefaultSpawn=%d, PhoneType=%d, PhoneNumber=%d, World=%d, Interior=%d, VehSlots=%d, Admin=%d WHERE AccountID=%d",
        Player[playerid][pAccountID],Player[playerid][pUsername],Player[playerid][pPassword],Player[playerid][pMoney],Player[playerid][pLevel],Player[playerid][pSkin],Player[playerid][pHealth],Player[playerid][pArmour],
        Player[playerid][pPosX],Player[playerid][pPosY],Player[playerid][pPosZ],Player[playerid][pAngle],Player[playerid][pEthnicity],Player[playerid][pGender],Player[playerid][pAge],Player[playerid][pDefaultSpawn],Player[playerid][pPhoneType],
        Player[playerid][pPhoneNumber],Player[playerid][pWorld], Player[playerid][pInterior],Player[playerid][pVehSlots],Player[playerid][pAdmin],Player[playerid][pAccountID]);
        mysql_query(query);
        mysql_free_result();
        SaveContacts(playerid);
    }
    return 1;
}

All other suggestions will be greatly appreciated, I really need to get this fixed.

Cheers

Re: Saving to DB from Player enum. Possible cause of fault? - mkr - 13.09.2012

Yeah, you need to do something like the second bit. There's no insert ID associated with an UPDATE statement (as far as I know), and that query wouldn't do anything significant anyway because you're still trying to use pAccountID (which is 0) in it.

This is my suggestion for that second function, and it just checks to see if pAccountID is 0 before fetching the player's account ID. In general--especially if your SQL queries aren't threaded--you want to make as few SQL calls as possible.

pawn Код:

stock SavePlayerAccount(playerid)
{
    if(IsPlayerConnected(playerid))
    {
        new EscapedName[MAX_PLAYER_NAME];
        mysql_real_escape_string(GetName(playerid), EscapedName);
        //UpdatePlayerVariables(playerid);
        if(CheckExist(playerid) == 0)
        {
            format(query, sizeof(query), "INSERT INTO `Accounts` (Username, Password) VALUES('%s', '%s')",EscapedName, Player[playerid][pPassword]);
            mysql_query(query);
            Player[playerid][pAccountID] = mysql_insert_id();
        }
        if (Player[playerid][pAccountID] == 0) // don't do an extra query if you don't have to
        {
                    mysql_query("SELECT AccountID FROM Accounts where Username = '%s'", EscapedName);
                    mysql_store_result();
                    Player[playerid][pAccountID] = mysql_fetch_int();
        }
        format(query, sizeof(query), "UPDATE Accounts SET AccountID=%d, Username='%s', Password='%s', Money=%d, Level=%d, Skin=%d, Health=%f, Armour=%f, PosX=%f, PosY=%f, PosZ=%f, Angle=%f, Ethnicity=%d, Gender=%d, Age=%d, DefaultSpawn=%d, PhoneType=%d, PhoneNumber=%d, World=%d, Interior=%d, VehSlots=%d, Admin=%d WHERE AccountID=%d",
        Player[playerid][pAccountID],Player[playerid][pUsername],Player[playerid][pPassword],Player[playerid][pMoney],Player[playerid][pLevel],Player[playerid][pSkin],Player[playerid][pHealth],Player[playerid][pArmour],
        Player[playerid][pPosX],Player[playerid][pPosY],Player[playerid][pPosZ],Player[playerid][pAngle],Player[playerid][pEthnicity],Player[playerid][pGender],Player[playerid][pAge],Player[playerid][pDefaultSpawn],Player[playerid][pPhoneType],
        Player[playerid][pPhoneNumber],Player[playerid][pWorld], Player[playerid][pInterior],Player[playerid][pVehSlots],Player[playerid][pAdmin],Player[playerid][pAccountID]);
        mysql_query(query);
        mysql_free_result();
        SaveContacts(playerid);
    }
    return 1;
}

Re: Saving to DB from Player enum. Possible cause of fault? - Deal-or-die - 13.09.2012

Alright sweet... and what do you mean by threading something, Sorry it's probably a stupid question but to be honest I never have really known about threading it...

EDIT: Mind for a few examples? I read a few tuts and kinda got the concept but not really workin'. Know of any filterscripts and Gamemodes that have threaded queries I am able to have a look at?