[iGetty]

I have a problem in my SQL.


Here is the SQL things that I have now:

pawn Код:

public OnPlayerConnect(playerid)
{
    new Query[80], string[164];
    format(Query,sizeof(Query),"SELECT `Username` FROM `Users` WHERE `Username` = '%s' LIMIT 1",pName(playerid));
    mysql_query(Query);
    mysql_store_result();
    if(mysql_num_rows() != 0)
    {
        format(string,sizeof(string),"Welcome back to 'Server Name' %s\nYou have got an account here\nPlease enter the password to login!",pName(playerid));
        ShowPlayerDialog(playerid,1,DIALOG_STYLE_INPUT,"Login",string,"Login","Exit");
    }
    else
    {
        format(string,sizeof(string),"Welcome to 'Server Name' %s\nIt seems that your account isn't registered.\nPlease enter a password to register.",pName(playerid));
        ShowPlayerDialog(playerid,0,DIALOG_STYLE_INPUT,"Register",string,"Register","Exit");
    }
    mysql_free_result();
    return 1;
}



public OnDialogResponse(playerid, dialogid, response, listitem, inputtext[])
{
    switch(dialogid)
    {
        case 0:
        {
            if(!response) Kick(playerid);
            else
            if(strlen(inputtext) == 0)
            {
                ShowPlayerDialog(playerid, 0, DIALOG_STYLE_INPUT,"Register","Welcome to 'Server Name' %s\nIt seems that your account isn't registered.\nPlease enter a password to register.","Register","Exit");
            }
            else
            {
                new EscapedText[60];
                new Query[256];
                mysql_real_escape_string(inputtext, EscapedText);
                format(Query,sizeof(Query),"INSERT INTO `Users` (Username,Password,Money,AdminLevel,Banned,Score) VALUES ('%s','%s','0','0','0','0')",pName(playerid),EscapedText);
                mysql_query(Query);
                SendClientMessage(playerid,WHITE,"You have been successfully registered!");
            }
        }
        case 1:
        {
            if(!response) Kick(playerid);
            if(strlen(inputtext) == 0)
            {
                ShowPlayerDialog(playerid, 1, DIALOG_STYLE_INPUT,"Login","Welcome back to 'Server Name' %s\nYou have got an account here\nPlease enter the password to login!","Login","Exit");
            }
            else
            {
                LoginPlayer(playerid,inputtext);
            }
        }
    }
}

stock LoginPlayer(playerid,const password[])
{
    new EscapedText[60], Query[256];
    mysql_real_escape_string(password, EscapedText);
    format(Query,sizeof(Query),"SELECT * FROM `Users` WHERE `Username` = '%s' AND `Password` = '%s'",pName(playerid),EscapedText);
    mysql_query(Query);
    mysql_store_result();
    if(mysql_num_rows() != 0)
    {
        LoadStats(playerid);
    }
    else
    {
        SendClientMessage(playerid,WHITE,"You have entered an incorrect password and have been kicked as a result.");
        Kick(playerid);
    }
    mysql_free_result();
    return 1;
}

stock LoadStats(playerid)
{
    new Query[256];
    format(Query, sizeof(Query), "SELECT * FROM `Users` WHERE `Username` = '%s' ", pName(playerid));
    mysql_query(Query);
    mysql_store_result();
    mysql_fetch_row_format(Query, "|");
    sscanf(Query, "e<p<|>s[24]s[23]iiii>", Player[playerid]);
    mysql_free_result();
    GivePlayerMoney(playerid, Player[playerid][Money]);
    Player[playerid][AdminLevel] = Player[playerid][AdminLevel];
    SavePInfo(playerid);
    if(Player[playerid][Banned] == 1)
    {
        SendClientMessage(playerid, WHITE, "You have been banned from this server! Please make a ban appeal on "FORUM".");
        SavePInfo(playerid);
        Kick(playerid);
        return 0;
    }
    return 1;
}

stock SavePInfo(playerid)
{
    new Query[256];
    format(Query, sizeof(Query), "UPDATE `Users` SET `Money` = %d, `AdminLevel` = %d, `Banned` = %d, `Score` = %d  WHERE `user` = '%s'",
    Player[playerid][Money],
    Player[playerid][AdminLevel],
    Player[playerid][Banned],
    Player[playerid][Score],
    pName(playerid));
    mysql_query(Query);
}

Thanks!

[Ballu Miaa]

So what is the problem in the SQL?

[iGetty]

It isn't saving to the database.

[Mandrakke]

Use OnQueryError callback to debug it and tell us which error occurs.

https://sampwiki.blast.hk/wiki/MySQL#OnQueryError

[iGetty]

How do I use it?

Do I just add this:

pawn Код:

public OnQueryError(errorid, error[], resultid, extraid, callback[], query[], connectionHandle)
{
    switch(errorid)
    {
        case CR_COMMAND_OUT_OF_SYNC:
        {
            printf("Commands out of sync for thread ID: %d",resultid);
        }
        case ER_SYNTAX_ERROR:
        {
            printf("Something is wrong in your syntax, query: %s",query);
        }
    }
    return 1;
}

To the bottom of the script and start the server?

Thanks!

[Mandrakke]

just use it;

pawn Код:

public OnQueryError(errorid, error[], resultid, extraid, callback[], query[], connectionHandle)
{
    printf("%d %s %d %d %s %s %d",errorid, error[], resultid, extraid, callback[], query[], connectionHandle);
    return 1;
}

and paste here the output.

[iGetty]

Код:

error 029: invalid expression, assumed zero

On this line

pawn Код:

printf("%d %s %d %d %s %s %d",errorid, error[], resultid, extraid, callback[], query[], connectionHandle);

[Mandrakke]

I am a dumb in pawn stuff, try it;

pawn Код:

printf("%d %s %d %d %s %s %d",errorid, error, resultid, extraid, callback, query, connectionHandle);

[iGetty]

It worked :P

and

Код:

[03:35:06] 1054 Unknown column 'user' in 'where clause' -1 -1 NULL UPDATE `Users` SET `Money` = 0, `AdminLevel` = 5, `Banned` = 0, `Score` = 0  WHERE `user` = 'Callum' 1
[03:35:09] 1054 Unknown column 'user' in 'where clause' -1 -1 NULL UPDATE `Users` SET `Money` = 0, `AdminLevel` = 5, `Banned` = 0, `Score` = 0  WHERE `user` = 'Callum' 1
[03:35:09] 1054 Unknown column 'user' in 'where clause' -1 -1 NULL UPDATE `Users` SET `Money` = 0, `AdminLevel` = 5, `Banned` = 0, `Score` = 0  WHERE `user` = 'Callum' 1
[03:35:09] 1054 Unknown column 'user' in 'where clause' -1 -1 NULL UPDATE `Users` SET `Money` = 0, `AdminLevel` = 5, `Banned` = 0, `Score` = 0  WHERE `user` = 'Callum' 1
[03:35:09] 1054 Unknown column 'user' in 'where clause' -1 -1 NULL UPDATE `Users` SET `Money` = 0, `AdminLevel` = 5, `Banned` = 0, `Score` = 0  WHERE `user` = 'Callum' 1
[03:35:09] 1054 Unknown column 'user' in 'where clause' -1 -1 NULL UPDATE `Users` SET `Money` = 0, `AdminLevel` = 5, `Banned` = 0, `Score` = 0  WHERE `user` = 'Callum' 1
[03:35:09] 1054 Unknown column 'user' in 'where clause' -1 -1 NULL UPDATE `Users` SET `Money` = 0, `AdminLevel` = 5, `Banned` = 0, `Score` = 0  WHERE `user` = 'Callum' 1
[03:35:09] 1054 Unknown column 'user' in 'where clause' -1 -1 NULL UPDATE `Users` SET `Money` = 0, `AdminLevel` = 5, `Banned` = 0, `Score` = 0  WHERE `user` = 'Callum' 1
[03:35:09] 1054 Unknown column 'user' in 'where clause' -1 -1 NULL UPDATE `Users` SET `Money` = 0, `AdminLevel` = 5, `Banned` = 0, `Score` = 0  WHERE `user` = 'Callum' 1
[03:35:09] 1054 Unknown column 'user' in 'where clause' -1 -1 NULL UPDATE `Users` SET `Money` = 0, `AdminLevel` = 5, `Banned` = 0, `Score` = 0  WHERE `user` = 'Callum' 1
[03:35:10] 1054 Unknown column 'user' in 'where clause' -1 -1 NULL UPDATE `Users` SET `Money` = 0, `AdminLevel` = 5, `Banned` = 0, `Score` = 0  WHERE `user` = 'Callum' 1
[03:35:10] 1054 Unknown column 'user' in 'where clause' -1 -1 NULL UPDATE `Users` SET `Money` = 0, `AdminLevel` = 5, `Banned` = 0, `Score` = 0  WHERE `user` = 'Callum' 1
[03:35:10] 1054 Unknown column 'user' in 'where clause' -1 -1 NULL UPDATE `Users` SET `Money` = 0, `AdminLevel` = 5, `Banned` = 0, `Score` = 0  WHERE `user` = 'Callum' 1
[03:35:10] 1054 Unknown column 'user' in 'where clause' -1 -1 NULL UPDATE `Users` SET `Money` = 0, `AdminLevel` = 5, `Banned` = 0, `Score` = 0  WHERE `user` = 'Callum' 1
[03:35:10] 1054 Unknown column 'user' in 'where clause' -1 -1 NULL UPDATE `Users` SET `Money` = 0, `AdminLevel` = 5, `Banned` = 0, `Score` = 0  WHERE `user` = 'Callum' 1
[03:35:10] 1054 Unknown column 'user' in 'where clause' -1 -1 NULL UPDATE `Users` SET `Money` = 0, `AdminLevel` = 5, `Banned` = 0, `Score` = 0  WHERE `user` = 'Callum' 1
[03:35:17] 1054 Unknown column 'user' in 'where clause' -1 -1 NULL UPDATE `Users` SET `Money` = 0, `AdminLevel` = 5, `Banned` = 0, `Score` = 0  WHERE `user` = 'Callum' 1
[03:35:18] 1054 Unknown column 'user' in 'where clause' -1 -1 NULL UPDATE `Users` SET `Money` = 0, `AdminLevel` = 5, `Banned` = 0, `Score` = 0  WHERE `user` = 'Callum' 1

Edit: I managed to fix the none saving, but now it's saving the wrong thing in the wrong place?

I typed /setadminlevel callum 10

Instead of adding it to the AdminLevel section, it added it to Money

Thanks.

[Mandrakke]

ok, you have the column "user" missing in your table "Users"

i think that the right column is "Username", because you used that in this line;

Код:

format(Query,sizeof(Query),"SELECT `Username` FROM `Users` WHERE `Username` = '%s' LIMIT 1",pName(playerid));

with no errors.

so, change this line;

Код:

format(Query, sizeof(Query), "UPDATE `Users` SET `Money` = %d, `AdminLevel` = %d, `Banned` = %d, `Score` = %d  WHERE `user` = '%s'",

to the following;

Код:

format(Query, sizeof(Query), "UPDATE `Users` SET `Money` = %d, `AdminLevel` = %d, `Banned` = %d, `Score` = %d  WHERE `Username` = '%s'",

and make a test.