SA-MP Forums Archive
mySQL saving - 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: mySQL saving (/showthread.php?tid=647260)


mySQL saving - NealPeteros - 02.01.2018

So I'm using this <--[click] tutorial for a headstart on a mysql saving system. I have everything needed, the database, the tables, and the rows, but at the end of the day, it still doesn't save anything. I even tried saving only my account, it still doesn't work. I will provide any function/callback/line needed.

Re: mySQL saving - MEW273 - 02.01.2018

Are you receiving any errors in the MySQL log?
Is the connection you are attempting to make being successful?

Re: mySQL saving - NealPeteros - 02.01.2018

The connection is successful, yes.

There are errors in mysql_log
Код:
[19:11:20] [DEBUG] mysql_connect - host: "localhost", user: "root", database: "deathmatch", password: "****", port: 3306, autoreconnect: true, pool_size: 2
[19:11:20] [DEBUG] CMySQLHandle::Create - creating new connection..
[19:11:20] [DEBUG] CMySQLHandle::CMySQLHandle - constructor called
[19:11:20] [DEBUG] CMySQLHandle::Create - connection created (id: 1)
[19:11:20] [DEBUG] CMySQLConnection::Connect - establishing connection to database...
[19:11:20] [DEBUG] CMySQLConnection::Connect - connection was successful
[19:11:20] [DEBUG] CMySQLConnection::Connect - auto-reconnect has been enabled
[19:11:20] [DEBUG] mysql_errno - connection: 1
[19:11:20] [DEBUG] CMySQLConnection::Connect - establishing connection to database...
[19:11:20] [DEBUG] CMySQLConnection::Connect - establishing connection to database...
[19:11:21] [DEBUG] cache_get_field_content_int - row: 0, field_name: "ID", connection: 1
[19:11:21] [WARNING] cache_get_field_content_int - no active cache
[19:11:21] [DEBUG] CMySQLConnection::Connect - connection was successful
[19:11:21] [DEBUG] CMySQLConnection::Connect - connection was successful
[19:11:21] [DEBUG] CMySQLConnection::Connect - connection was successful
[19:11:21] [DEBUG] CMySQLConnection::Connect - auto-reconnect has been enabled
[19:11:21] [DEBUG] cache_get_field_content_int - row: 0, field_name: "Members", connection: 1
[19:11:21] [WARNING] cache_get_field_content_int - no active cache
[19:11:21] [DEBUG] cache_get_field_content_int - row: 0, field_name: "Type", connection: 1
[19:11:21] [WARNING] cache_get_field_content_int - no active cache
[19:11:21] [DEBUG] cache_get_field_content - row: 0, field_name: "Name", connection: 1, max_len: 255
[19:11:21] [WARNING] cache_get_field_content - no active cache
[19:11:21] [DEBUG] cache_get_field_content - row: 0, field_name: "Rank1", connection: 1, max_len: 64
[19:11:21] [WARNING] cache_get_field_content - no active cache
[19:11:21] [DEBUG] cache_get_field_content - row: 0, field_name: "Rank2", connection: 1, max_len: 64
[19:11:21] [WARNING] cache_get_field_content - no active cache
[19:11:21] [DEBUG] cache_get_field_content - row: 0, field_name: "Rank3", connection: 1, max_len: 64
[19:11:21] [WARNING] cache_get_field_content - no active cache
[19:11:21] [DEBUG] cache_get_field_content - row: 0, field_name: "Rank4", connection: 1, max_len: 64
[19:11:21] [WARNING] cache_get_field_content - no active cache
[19:11:21] [DEBUG] cache_get_field_content - row: 0, field_name: "Leader", connection: 1, max_len: 24
[19:11:21] [WARNING] cache_get_field_content - no active cache
[19:11:21] [DEBUG] cache_get_field_content - row: 0, field_name: "Color", connection: 1, max_len: 7
[19:11:21] [WARNING] cache_get_field_content - no active cache
[19:11:30] [DEBUG] mysql_format - connection: 1, len: 128, format: "SELECT `Password`, `ID` FROM `accounts` WHERE `Name` = '%e' LIMIT 1"
[19:11:30] [DEBUG] mysql_tquery - connection: 1, query: "SELECT `Password`, `ID` FROM `accounts` WHERE `Name` = 'Dexter' ", callback: "OnAccountCheck", format: "i"
[19:11:30] [DEBUG] CMySQLQuery::Execute[OnAccountCheck] - starting query execution
[19:11:30] [DEBUG] CMySQLQuery::Execute[OnAccountCheck] - query was successfully executed within 0.809 milliseconds
[19:11:30] [DEBUG] CMySQLResult::CMySQLResult() - constructor called
[19:11:30] [DEBUG] Calling callback "OnAccountCheck"..
[19:11:30] [DEBUG] cache_get_data - connection: 1
[19:11:30] [DEBUG] CMySQLResult::~CMySQLResult() - deconstructor called
[19:11:33] [DEBUG] mysql_format - connection: 1, len: 100, format: "SELECT * FROM `accounts` WHERE `Name` = '%e' LIMIT 1"
[19:11:33] [DEBUG] mysql_tquery - connection: 1, query: "SELECT * FROM `accounts` WHERE `Name` = 'Dexter' LIMIT 1", callback: "OnAccountLoad", format: "i"
[19:11:33] [DEBUG] CMySQLQuery::Execute[OnAccountLoad] - starting query execution
[19:11:33] [DEBUG] CMySQLQuery::Execute[OnAccountLoad] - query was successfully executed within 0.538 milliseconds
[19:11:33] [DEBUG] CMySQLResult::CMySQLResult() - constructor called
[19:11:33] [DEBUG] Calling callback "OnAccountLoad"..
[19:11:33] [DEBUG] cache_get_field_content_int - row: 0, field_name: "Admin", connection: 1
[19:11:33] [ERROR] CMySQLResult::GetRowDataByName() - invalid row index ('0')
[19:11:33] [ERROR] cache_get_field_content_int - invalid datatype
[19:11:33] [DEBUG] cache_get_field_content_int - row: 0, field_name: "VIP", connection: 1
[19:11:33] [ERROR] CMySQLResult::GetRowDataByName() - invalid row index ('0')
[19:11:33] [ERROR] cache_get_field_content_int - invalid datatype
[19:11:33] [DEBUG] cache_get_field_content_int - row: 0, field_name: "Money", connection: 1
[19:11:33] [ERROR] CMySQLResult::GetRowDataByName() - invalid row index ('0')
[19:11:33] [ERROR] cache_get_field_content_int - invalid datatype
[19:11:33] [DEBUG] cache_get_field_content_int - row: 0, field_name: "Group", connection: 1
[19:11:33] [ERROR] CMySQLResult::GetRowDataByName() - invalid row index ('0')
[19:11:33] [ERROR] cache_get_field_content_int - invalid datatype
[19:11:33] [DEBUG] cache_get_field_content_int - row: 0, field_name: "GroupRank", connection: 1
[19:11:33] [ERROR] CMySQLResult::GetRowDataByName() - invalid row index ('0')
[19:11:33] [ERROR] cache_get_field_content_int - invalid datatype
[19:11:33] [DEBUG] cache_get_field_content_int - row: 0, field_name: "Kills", connection: 1
[19:11:33] [ERROR] CMySQLResult::GetRowDataByName() - invalid row index ('0')
[19:11:33] [ERROR] cache_get_field_content_int - invalid datatype
[19:11:33] [DEBUG] cache_get_field_content_int - row: 0, field_name: "Deaths", connection: 1
[19:11:33] [ERROR] CMySQLResult::GetRowDataByName() - invalid row index ('0')
[19:11:33] [ERROR] cache_get_field_content_int - invalid datatype
[19:11:34] [DEBUG] cache_get_field_content_int - row: 0, field_name: "Score", connection: 1
[19:11:34] [ERROR] CMySQLResult::GetRowDataByName() - invalid row index ('0')
[19:11:34] [ERROR] cache_get_field_content_int - invalid datatype
[19:11:34] [DEBUG] cache_get_field_content_int - row: 0, field_name: "HighKS", connection: 1
[19:11:34] [ERROR] CMySQLResult::GetRowDataByName() - invalid row index ('0')
[19:11:34] [ERROR] cache_get_field_content_int - invalid datatype
[19:11:34] [DEBUG] CMySQLResult::~CMySQLResult() - deconstructor called


Re: mySQL saving - NealPeteros - 02.01.2018

12 hour bump(not exavtly 12 hours)

would using mySQL r39 on said tutorial be an issue

Re: mySQL saving - GTLS - 03.01.2018

Can you show the code? probably something is wrong there.

Re: mySQL saving - NealPeteros - 03.01.2018

PHP код:
#define SQL_HOST "localhost"
#define SQL_USER "root"
#define SQL_DB "deathmatch"
#define SQL_PASS ""

new    mysql;

native WP_Hash(buffer[],len,const str[]);

enum pInfo
{
    ID,
    Name[MAX_PLAYER_NAME],
    Password[129],
    IP[16],
    Admin,
    VIP,
    Money,
    Group,
    GroupRank,
    Kills,
    Deaths,
    Score,
    HighKS
}
new PlayerInfo[MAX_PLAYERS][pInfo];

public OnGameModeInit()
{
    // Don't use these lines if it's a filterscript
    DisableInteriorEnterExits();
    SetGameModeText(""SV_MODE" "SV_VERSION"");
    mysql_log(LOG_ALL);
    mysql = mysql_connect(SQL_HOST, SQL_USER, SQL_DB, SQL_PASS); 
    if(mysql_errno()!=0)
    {
        printf("MySQL Connection failed");
    }
    else
    {
        printf("MySQL Connection connected");
    }
    LoadGroups();
    return 1;
}

public OnPlayerConnect(playerid)
{

    TogglePlayerSpectating(playerid, true);
    new
        query[128];

    mysql_format(mysql, query, sizeof(query), "SELECT `Password`, `ID` FROM `accounts` WHERE `Name` = '%e' LIMIT 1", GetName(playerid));
    mysql_tquery(mysql, query, "OnAccountCheck", "i", playerid);
    return 1;
}

forward OnAccountCheck(playerid);
public OnAccountCheck(playerid)
{
    new
        rows,
        fields,
        string[120];
    cache_get_data(rows,fields,mysql);

    format(string, 120, "Welcome, %s!\nPlease type in your password to continue.", GetName(playerid));

    if(rows)
    {
        cache_get_field_content(0, "Password",PlayerInfo[playerid][Password],mysql,129);
        PlayerInfo[playerid][ID] = cache_get_field_content_int(0, "ID");
        ShowPlayerDialog(playerid, DIALOG_LOGIN, DIALOG_STYLE_INPUT, "Login", string, "Login" , "Quit");
    }
    else
    {
        ShowPlayerDialog(playerid, DIALOG_LOGIN, DIALOG_STYLE_INPUT, "Register", string, "Register" , "Quit");
    }
    return 1;
}

public OnDialogResponse(playerid, dialogid, response, listitem, inputtext[])
{

    switch(dialogid)
    {
        case DIALOG_LOGIN:
        {
            if(!response) Kick(playerid);

            new
                hashpass[129],
                query[100],
                string[120];

            WP_Hash(hashpass, sizeof(hashpass),inputtext);

            if(!strcmp(hashpass, PlayerInfo[playerid][Password]))
            {
                mysql_format(mysql, query, sizeof(query), "SELECT * FROM `accounts` WHERE `Name` = '%e' LIMIT 1",GetName(playerid));
                mysql_tquery(mysql, query, "OnAccountLoad", "i", playerid);
            }
            else
            {
                format(string, 120, "Welcome, %s!\nPlease type in your password to continue.", GetName(playerid));
                SendClientMessage(playerid, -1, "You have specified an incorrect password");
                ShowPlayerDialog(playerid, DIALOG_LOGIN, DIALOG_STYLE_INPUT, "Login", string, "Login" , "Quit");
            }
        }
        case DIALOG_REGISTER:
        {
            if(!response) Kick(playerid);

            if(strlen(inputtext) < 5)
            {
                new string[120];
                format(string, 120, "Welcome, %s!\nPlease type in your password to continue.", GetName(playerid));
                SendClientMessage(playerid, -1, "Your password must at least contain more than 4 characters");
                return ShowPlayerDialog(playerid, DIALOG_REGISTER, DIALOG_STYLE_INPUT, "Register", string, "Register", "Quit");
            }
            new
                query[512],
                playerip[16];

            GetPlayerIp(playerid, playerip, 16);
            WP_Hash(PlayerInfo[playerid][Password],129,inputtext);
            mysql_format(mysql, query, sizeof(query), "INSERT INTO `accounts` (`Name`, `Password`, `IP`, `Admin`, `VIP`, `Money`, `Group`, `GroupRank`, `Kills`, `Deaths`, `Score`, `HighKS`) VALUES('%e','%e','%e',0,0,0,0,0)", GetName(playerid), PlayerInfo[playerid][Password],playerip,PlayerInfo[playerid][Kills],PlayerInfo[playerid][Deaths],
                PlayerInfo[playerid][Score],PlayerInfo[playerid][HighKS]);
            mysql_tquery(mysql, query, "OnAccountRegister", "i", playerid);
        }
       }
}

forward OnAccountLoad(playerid);
public OnAccountLoad(playerid)
{
    PlayerInfo[playerid][Admin] = cache_get_field_content_int(0,"Admin");
    PlayerInfo[playerid][VIP] = cache_get_field_content_int(0,"VIP");
    PlayerInfo[playerid][Money] = cache_get_field_content_int(0,"Money");
    PlayerInfo[playerid][Group] = cache_get_field_content_int(0,"Group");
    PlayerInfo[playerid][GroupRank] = cache_get_field_content_int(0,"GroupRank");
    PlayerInfo[playerid][Kills] = cache_get_field_content_int(0,"Kills");
    PlayerInfo[playerid][Deaths] = cache_get_field_content_int(0,"Deaths");
    PlayerInfo[playerid][Score] = cache_get_field_content_int(0,"Score");
    PlayerInfo[playerid][HighKS] = cache_get_field_content_int(0,"HighKS");

    TogglePlayerSpectating(playerid, false);

    GivePlayerMoney(playerid, PlayerInfo[playerid][Money]);

    SetSpawnInfo(playerid, 0, 299, SPAWN_X, SPAWN_Y, SPAWN_Z, 0.0, 0, 0, 0, 0, 0, 0);
    SetPlayerInterior(playerid, SPAWN_INT);
    SetPlayerVirtualWorld(playerid, SPAWN_VW);
    SpawnPlayer(playerid);

    SendClientMessage(playerid, -1, "Welcome to "SV_NAME" "SV_MODE_ABREV"!");

    SetPlayerHealth(playerid, 999999);
    SetPlayerArmour(playerid, 999999);
    return 1;
}

forward OnAccountRegister(playerid);
public OnAccountRegister(playerid)
{
    PlayerInfo[playerid][ID] = cache_insert_id();
    printf("[SYS] New account registered. Database ID: [%d]", PlayerInfo[playerid][ID]);

    TogglePlayerSpectating(playerid, false);

    SetSpawnInfo(playerid, 0, 299, SPAWN_X, SPAWN_Y, SPAWN_Z, 0.0, 0, 0, 0, 0, 0, 0);
    SpawnPlayer(playerid);

    SendClientMessage(playerid, -1, "Welcome to "SV_NAME" "SV_MODE_ABREV"!");

    SetPlayerHealth(playerid, 999999);
    SetPlayerArmour(playerid, 999999);
    return 1;
}

public OnPlayerDisconnect(playerid, reason)
{
    new
        query[128];

    mysql_format(mysql, query, sizeof(query), "UPDATE `accounts` SET `Money` = %d, `VIP` = %d, `Admin` = %d, `Group` = %d, `GroupRank` = %d, `Kills` = %d, `Deaths` = %d, `Score` = %d, `HighKS` = %d,  WHERE `ID` = %d", 
        GetPlayerMoney(playerid), PlayerInfo[playerid][VIP], PlayerInfo[playerid][Admin], PlayerInfo[playerid][Group], PlayerInfo[playerid][GroupRank], PlayerInfo[playerid][Kills], PlayerInfo[playerid][Deaths], PlayerInfo[playerid][Score],
        PlayerInfo[playerid][HighKS]);
    mysql_tquery(mysql, query,"","");
    FloodByPlayer[playerid] = 0;
    KillTimer(FloodTimer[playerid]);

    if(PlayerNeedsHelp[playerid] == 1)
    {
        for(new i=0; i<MAX_PLAYERS; i++)
        {
            if(PlayerInfo[i][Admin] > 0)
            {
                SendClientMessage(i, 0x00FF00FF, "The player who made the report has left the server.");
            }
        }
        PlayerNeedsHelp[playerid] = 0;
    }
    for(new i = 0; i < MAX_REPORTS; i ++)
    {
        if(ReportInfo[i][rExists] && ReportInfo[i][rReporter] == playerid)
        {
            ReportInfo[i][rExists] = 0;
        }
    }
    return 1;
} 
SaveStats

PHP код:
SaveStats(playerid)
{
    new query[128], name[MAX_PLAYER_NAME], i = PlayerInfo[playerid][Group];
    GetPlayerName(playerid, name, MAX_PLAYER_NAME);
    if(Iter_Contains(Gangs, i) && i != -1)
        mysql_format(mysql, query, sizeof(query), "UPDATE `accounts` SET `Admin` = %d, `VIP` = %d, `Money` = %d, `Kills` = %d, `Deaths` = %d, `Score` = %d, `HighKS` = %d, `Group` = %d, `Rank` = %d WHERE `ID` = '%d'",
            PlayerInfo[playerid][Admin], PlayerInfo[playerid][VIP], PlayerInfo[playerid][Money], PlayerInfo[playerid][Kills], PlayerInfo[playerid][Deaths], PlayerInfo[playerid][Score], PlayerInfo[playerid][HighKS], PlayerInfo[playerid][Group], PlayerInfo[playerid][GroupRank], PlayerInfo[playerid][ID]);
    else
        mysql_format(mysql, query, sizeof(query), "UPDATE `accounts` SET `Admin` = %d, `VIP` = %d, `Money` = %d, `Kills` = %d, `Deaths` = %d, `Score` = %d, `HighKS` = %d, `Group` = -1 WHERE `ID` = '%d'",
            PlayerInfo[playerid][Admin], PlayerInfo[playerid][VIP], PlayerInfo[playerid][Money], PlayerInfo[playerid][Kills], PlayerInfo[playerid][Deaths], PlayerInfo[playerid][Score], PlayerInfo[playerid][HighKS], PlayerInfo[playerid][ID]);
    mysql_tquery(mysql, query,"","");
    return 1;
} 


Re: mySQL saving - NealPeteros - 05.01.2018

Bump

I realized that during OnAccountRegister

Called On
PHP код:
case DIALOG_REGISTER:
        {
            if(!response) Kick(playerid);
            if(strlen(inputtext) < 5)
            {
                new string[120];
                format(string, 120, "Welcome, %s!\nPlease type in your password to continue.", GetName(playerid));
                SendClientMessage(playerid, -1, "Your password must at least contain more than 4 characters");
                return ShowPlayerDialog(playerid, DIALOG_REGISTER, DIALOG_STYLE_INPUT, "Register", string, "Register", "Quit");
            }
            new
                query[512],
                playerip[16];
            GetPlayerIp(playerid, playerip, 16);
            WP_Hash(PlayerInfo[playerid][Password],129,inputtext);
            mysql_format(mysql, query, sizeof(query), "INSERT INTO `accounts` (`Name`, `Password`, `IP`, `Admin`, `VIP`, `Money`, `Group`, `GroupRank`, `Kills`, `Deaths`, `Score`, `HighKS`) VALUES('%e','%e','%e',0,0,0,0,0)", GetName(playerid), PlayerInfo[playerid][Password],playerip,PlayerInfo[playerid][Kills],PlayerInfo[playerid][Deaths],
                PlayerInfo[playerid][Score],PlayerInfo[playerid][HighKS]);
            mysql_tquery(mysql, query, "OnAccountRegister", "i", playerid);
        } 
Function:
PHP код:
forward OnAccountRegister(playerid);
public OnAccountRegister(playerid)
{
    PlayerInfo[playerid][ID] = cache_insert_id();
    printf("[SYS] New account registered. Database ID: [%d]", PlayerInfo[playerid][ID]);
    TogglePlayerSpectating(playerid, false);
    SetSpawnInfo(playerid, 0, 299, SPAWN_X, SPAWN_Y, SPAWN_Z, 0.0, 0, 0, 0, 0, 0, 0);
    SpawnPlayer(playerid);
    SendClientMessage(playerid, -1, "Welcome to "SV_NAME" "SV_MODE_ABREV"!");
    SetPlayerHealth(playerid, 999999);
    SetPlayerArmour(playerid, 999999);
    return 1;
} 
Код:
printf("[SYS] New account registered. Database ID: [%d]", PlayerInfo[playerid][ID]);
is not called when a player registers, but the code below it does

Re: mySQL saving - NealPeteros - 06.01.2018

Bump

I just found out that OnAccountRegister is not being called. After the register dialog, nothing happens

DIALOG_REGISTER
PHP код:
case DIALOG_REGISTER:
        {
            if(response)
            {
                if(strlen(inputtext) < 5)
                {
                    new string[120];
                    format(string, 120, "Welcome, %s!\nPlease type in your password to continue.", GetName(playerid));
                    SendClientMessage(playerid, -1, "Your password must at least contain more than 4 characters");
                    ShowPlayerDialog(playerid, DIALOG_REGISTER, DIALOG_STYLE_INPUT, "Register", string, "Register", "Quit");
                }
                else
                {
                    print("1");
                    new
                        query[512],
                        playerip[16];

                    GetPlayerIp(playerid, playerip, 16);
                    print("2");
                    WP_Hash(PlayerInfo[playerid][Password],129,inputtext);
                    print("3");
                    mysql_format(mysql, query, sizeof(query), "INSERT INTO accounts (Name, Password, IP, Admin, VIP, Money, Group, GroupRank, Kills, Deaths, Score, HighKS) VALUES('%e','%e','%e',0,0,0,0,0)", GetName(playerid), PlayerInfo[playerid][Password],playerip,PlayerInfo[playerid][Kills],PlayerInfo[playerid][Deaths],
                        PlayerInfo[playerid][Score],PlayerInfo[playerid][HighKS]);
                    print("4");
                    mysql_tquery(mysql, query, "OnAccountRegister", "d", playerid);
                    print("5");
                }
            }
            else return Kick(playerid);
        } 
OnAccountRegister
PHP код:
forward OnAccountRegister(playerid);
public OnAccountRegister(playerid)
{
    print("1");
    new id = cache_insert_id();
    printf("[SYS] New account registered. Database ID: [%i]", id);

    new
        query[128];

    if(id)
    {
           mysql_format(mysql, query, sizeof(query), "SELECT * FROM accounts WHERE ID = %i", id);
           mysql_tquery(mysql, query, "OnAccountLoad", "d", playerid);
    }
    return 1;
} 
Debug 'print' results are as follows
Код:
1
2
3
4
5
Take note that it stops at 5. If OnAccountRegister would be called, 1 would be printed after 5, but it doesn't.(see the first line inside OnAccountRegister)

Re: mySQL saving - NealPeteros - 06.01.2018

Also saw this error
Код:
[16:47:09] [ERROR] CMySQLQuery::Execute[OnAccountRegister(d)] - (error #1064) You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'Group, GroupRank, Kills, Deaths, Score, HighKS) VALUES('sda','6B5A8C360F490026' at line 1


Re: mySQL saving - MEW273 - 06.01.2018

There is a problem with the query to insert the account, see this line:
Код:
 mysql_format(mysql, query, sizeof(query), "INSERT INTO accounts (Name, Password, IP, Admin, VIP, Money, Group, GroupRank, Kills, Deaths, Score, HighKS) VALUES('%e','%e','%e',0,0,0,0,0)", GetName(playerid), PlayerInfo[playerid][Password],playerip,PlayerInfo[playerid][Kills],PlayerInfo[playerid][Deaths],
                        PlayerInfo[playerid][Score],PlayerInfo[playerid][HighKS]);
It will be giving you an error in the MySQL log. You are inserting into 12 columns (Name, Password, IP, Admin, VIP, Money, Group, GroupRank, Kills, Deaths, Score, HighKS) but only providing 8 values (VALUES('%e','%e','%e',0,0,0,0,0)). Thus the query is not successful and OnAccountRegister will never be called.