MySQL problem upon loading the user's data

MySQL problem upon loading the user's data - 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 problem upon loading the user's data (/showthread.php?tid=460908)

MySQL problem upon loading the user's data - DanishHaq - 30.08.2013

Hi, recently, I started learning MySQL, and I have developed an understanding of how to use queries etc.

Unfortunately, I have a slight problem whilst loading a user's data once they log in. Basically, it will log them in and it will be all good, but the I can't use PlayerData[playerid][variable] variables when I want to, it's bugging me a little bit.. even though that it should work as you will see in the code below. I'll give you all the MySQL stuff that I've put in, so even if I've missed anything, then you could possibly help me resolve the problem.

Here's my OnPlayerConnect(playerid):

pawn Код:

public OnPlayerConnect(playerid)
{
    new string[158];
    reporttimer[playerid] = 0;
    helpmetimer[playerid] = 0;
    gRegistered[playerid] = 0;
    GetPlayerName(playerid, sendername, sizeof(sendername));
    format(string,sizeof(string),"SELECT * FROM `users` WHERE `username`='%s' LIMIT 1", sendername);
    mysql_query(string);
    mysql_store_result();
    new rows = mysql_num_rows();
    if(!rows)
    {
        gRegistered[playerid] = 0;
        GetPlayerName(playerid, sendername, sizeof(sendername));
        format(string, sizeof(string), "Welcome %s\n\nYour nickname is not registered on this server, please register an account by typing a password in below.", sendername);
        ShowPlayerDialog(playerid, DIALOG_REGISTER, DIALOG_STYLE_PASSWORD, "ACCOUNT: Registration", string, "Register", "Quit");
        SetPlayerPos(playerid, -720.3318,1515.9364,76.9307);
        SetPlayerCameraPos(playerid, -721.6722,1515.1118,74.3855);
        SetPlayerCameraLookAt(playerid, -791.6120,1503.1776,40.6432);
    }
    else
    {
        gRegistered[playerid] = 1;
        GetPlayerName(playerid, sendername, sizeof(sendername));
        format(string, sizeof(string), "Welcome back %s\n\nPlease login with your password in the box below.", sendername);
        ShowPlayerDialog(playerid, DIALOG_LOGIN, DIALOG_STYLE_PASSWORD, "ACCOUNT: Login", string, "Login", "Quit");
        SetPlayerPos(playerid, -720.3318,1515.9364,76.9307);
        SetPlayerCameraPos(playerid, -721.6722,1515.1118,74.3855);
        SetPlayerCameraLookAt(playerid, -791.6120,1503.1776,40.6432);
    }
    mysql_free_result();
    return 1;
}

That all works fine, all the dialog's work as they are suppose to, and it will detect whether or not that player is registered or not perfectly.

Here's my OnDialogResponse(playerid, dialogid, response, listitem, inputtext[]):

pawn Код:

public OnDialogResponse(playerid, dialogid, response, listitem, inputtext[])
{
    switch(dialogid)
    {
        case DIALOG_REGISTER:
        {
            if(response)
            {
                if(strlen(inputtext) < 4 && strlen(inputtext) > 100)
                {
                    ShowPlayerDialog(playerid, DIALOG_REGISTER, DIALOG_STYLE_PASSWORD, "ACCOUNT: Registration", "You entered an invalid password (less than 4 characters or more than 100 characters.\n\nPlease enter a password below to register an account.", "Register", "Quit");
                }
                else if(strlen(inputtext) > 4 && strlen(inputtext) < 100)
                {
                    new escpass[100];
                    mysql_real_escape_string(inputtext, escpass);
                    MySQL_Register(playerid, escpass);
                }
            }
            if(!response)
            {
                Kick(playerid);
            }
        }
        case DIALOG_LOGIN:
        {
            if(!response)
            {
                Kick(playerid);
            }
            if(response)
            {
                new query[200], pname[24], escapepass[100];
                GetPlayerName(playerid, pname, 24);
                mysql_real_escape_string(inputtext, escapepass);
                format(query, sizeof(query), "SELECT `username` FROM `users` WHERE `username` = '%s' AND `password` = SHA1('%s')", pname, escapepass);
                mysql_query(query);
                mysql_store_result();
                new numrows = mysql_num_rows();
                if(numrows == 2)
                {
                    MySQL_Login(playerid);
                }
                else if(!numrows)
                {
                    ShowPlayerDialog(playerid, DIALOG_LOGIN, DIALOG_STYLE_PASSWORD, "ACCOUNT: Login" ,"You entered an incorrect password, please enter your password below to continue.", "Login", "Quit");
                }
                mysql_free_result();
            }
        }
    }
    return 1;
}

It detects whether or not the password is correct or not, I've tested that with multiple passwords that don't match the user accounts and it works perfectly. I've been using stocks for the MySQL_Login(playerid); and the MySQL_Register(playerid, escpass);.

Here's the MySQL_Register stock:

pawn Код:

stock MySQL_Register(playerid, escpass[])
{
    new query[200], pname[24];
    GetPlayerName(playerid, pname, 24);
    format(query, sizeof(query), "INSERT INTO `users` (`username`, `password`, `level`, `respectneeded`, `cash`, `bank`) VALUES('%s', SHA1('%s'), 1, 4, 25000, 25000)", pname, escpass);
    mysql_query(query);
    ShowPlayerDialog(playerid, DIALOG_LOGIN, DIALOG_STYLE_PASSWORD, "ACCOUNT: Login", "Thank you for registering, you can now log into your account by typing your password below.", "Login", "Quit");
    return 1;
}

This works perfectly, it will insert all the data that I want it to and it will hash out the passwords as I want it to, and it will give the cash and the bank, and the level all as I wanted to...

Here's the MySQL_Login(playerid):

pawn Код:

stock MySQL_Login(playerid)
{
    new query[300], result[100];
    GetPlayerName(playerid, sendername, sizeof(sendername));
    format(query, sizeof(query), "SELECT * FROM `users` WHERE `username` = '%s'", sendername);
    mysql_query(query);
    mysql_store_result();
    while(mysql_fetch_row_format(query,"|"))
    {
        mysql_fetch_field_row(result,"level"); PlayerData[playerid][pLevel] = strval(result);
        mysql_fetch_field_row(result,"respect"); PlayerData[playerid][pRespect] = strval( result );
        mysql_fetch_field_row(result,"respectneeded"); PlayerData[playerid][pRespectNeeded] = strval( result );
        mysql_fetch_field_row(result,"warns"); PlayerData[playerid][pWarns] = strval( result );
        mysql_fetch_field_row(result,"cash"); PlayerData[playerid][pCash] = strval( result );
        mysql_fetch_field_row(result,"bank"); PlayerData[playerid][pBank] = strval( result );
        mysql_fetch_field_row(result,"admin"); PlayerData[playerid][pAdmin] = strval( result );
        mysql_fetch_field_row(result,"helper"); PlayerData[playerid][pHelper] = strval( result );
        mysql_fetch_field_row(result,"member"); PlayerData[playerid][pMember] = strval( result );
        mysql_fetch_field_row(result,"rank"); PlayerData[playerid][pRank] = strval( result );
        mysql_fetch_field_row(result,"leader"); PlayerData[playerid][pLeader] = strval( result );
        mysql_fetch_field_row(result,"premium"); PlayerData[playerid][pPremium] = strval( result );
        mysql_fetch_field_row(result,"job"); PlayerData[playerid][pJob] = strval( result );
        mysql_fetch_field_row(result,"sex"); PlayerData[playerid][pSex] = strval( result );
        mysql_fetch_field_row(result,"skin"); PlayerData[playerid][pSkin] = strval( result );
        mysql_fetch_field_row(result,"phonenumber"); PlayerData[playerid][pPhoneNumber] = strval( result );
        mysql_fetch_field_row(result,"hoursplayed"); PlayerData[playerid][pHoursPlayed] = strval( result );
        mysql_fetch_field_row(result,"crimescommited"); PlayerData[playerid][pCrimesCommited] = strval( result );
        mysql_fetch_field_row(result,"wanteddeaths"); PlayerData[playerid][pWantedDeaths] = strval( result );
        mysql_fetch_field_row(result,"timesarrested"); PlayerData[playerid][pTimesArrested] = strval( result );
        mysql_fetch_field_row(result,"wantedlevel"); PlayerData[playerid][pWantedLevel] = strval( result );
        mysql_fetch_field_row(result,"jailed"); PlayerData[playerid][pJailed] = strval( result );
        mysql_fetch_field_row(result,"muted"); PlayerData[playerid][pMuted] = strval( result );
        mysql_fetch_field_row(result,"lotto"); PlayerData[playerid][pLotto] = strval( result );
        mysql_fetch_field_row(result,"std"); PlayerData[playerid][pSTD] = strval( result );
        mysql_fetch_field_row(result,"drugs"); PlayerData[playerid][pDrugs] = strval( result );
        mysql_fetch_field_row(result,"materials"); PlayerData[playerid][pMaterials] = strval( result );
        mysql_fetch_field_row(result,"factionpunish"); PlayerData[playerid][pFPunish] = strval( result );
        mysql_fetch_field_row(result,"factionwarns"); PlayerData[playerid][pFWarns] = strval( result );
    }
    mysql_free_result();
    Logged[playerid] = 1;
    return 1;
}

Now, the problem must be in here because when I come to use the PlayerData variables, it simply doesn't work at all. I've enumerator'd them all and it should work in an ideal world...

There are no errors whatsoever when it comes to compile, and my Debug.txt is shown below:

Код:

[14:28:58]  

[14:28:58] ---------------------------

[14:28:58] MySQL Debugging activated (08/30/13)

[14:28:58] ---------------------------

[14:28:58]  

[14:30:00] >> mysql_query( Connection handle: 1 )

[14:30:00] CMySQLHandler::Query(SELECT * FROM `users` WHERE `username`='testuserid3' LIMIT 1) - Successfully executed.

[14:30:00] >> mysql_store_result( Connection handle: 1 )

[14:30:00] CMySQLHandler::StoreResult() - Result was stored.

[14:30:00] >> mysql_num_rows( Connection handle: 1 )

[14:30:00] CMySQLHandler::NumRows() - Returned 0 row(s)

[14:30:00] >> mysql_free_result( Connection handle: 1 )

[14:30:00] CMySQLHandler::FreeResult() - Result was successfully free'd.

[14:30:09] >> mysql_real_escape_string( Connection handle: 1 )

[14:30:09] CMySQLHandler::EscapeString(password12); - Escaped 10 characters to password12.

[14:30:09] >> mysql_query( Connection handle: 1 )

[14:30:09] CMySQLHandler::Query(INSERT INTO `users` (`username`, `password`, `level`, `respectneeded`, `cash`, `bank`) VALUES('testuserid3', SHA1('password12'), 1, 4, 25000, 25000)) - Successfully executed.

[14:30:11] >> mysql_real_escape_string( Connection handle: 1 )

[14:30:11] CMySQLHandler::EscapeString(password12); - Escaped 10 characters to password12.

[14:30:11] >> mysql_query( Connection handle: 1 )

[14:30:11] CMySQLHandler::Query(SELECT `username` FROM `users` WHERE `username` = 'testuserid3' AND `password` = SHA1('password12')) - Successfully executed.

[14:30:11] >> mysql_store_result( Connection handle: 1 )

[14:30:11] CMySQLHandler::StoreResult() - Result was stored.

[14:30:11] >> mysql_num_rows( Connection handle: 1 )

[14:30:11] CMySQLHandler::NumRows() - Returned 1 row(s)

[14:30:11] >> mysql_free_result( Connection handle: 1 )

[14:30:11] CMySQLHandler::FreeResult() - Result was successfully free'd.

I'd appreciate any help, thanks.

Re: MySQL problem upon loading the user's data - DanishHaq - 30.08.2013

Nevermind, I have fixed the problem.