[Deal-or-die]

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. )

[mkr]

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.

[Deal-or-die]

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.

[mkr]

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.

[Deal-or-die]

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...?

[mkr]

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.

[Deal-or-die]

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]

[Deal-or-die]

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

[mkr]

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;
}

[Deal-or-die]

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?