Little help with MySQL - Printable Version

Little help with MySQL - 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: Little help with MySQL (/showthread.php?tid=462879)

Little help with MySQL - xganyx - 09.09.2013

I Got This

pawn Код:

#include <a_samp>
#include <sscanf2>
#include <a_mysql>

#define     mysql_host                  "127.0.0.1"
#define     mysql_user                  "root"
#define     mysql_pass                  ""
#define     mysql_data                  "database"

enum E_PLAYER_DATA
{
    pAdmin,
    pMoney
}
new PlayerData[MAX_PLAYERS][E_PLAYER_DATA];

main() { print("Test MYSQL"); }

public OnGameModeInit()
{
    mysql_debug(1);
    mysql_connect(mysql_host,mysql_user,mysql_data,mysql_pass);
    mysql_query("CREATE TABLE IF NOT EXISTS accounts(user VARCHAR(24), password VARCHAR(24), admin INT(20), cash INT(20), IP VARCHAR(16))");
    return 1;
}

new IsRegistered[MAX_PLAYERS],
    MoneyGiven[MAX_PLAYERS],
    Logged[MAX_PLAYERS];
    
public OnPlayerConnect(playerid)
{
    MoneyGiven[playerid] = -1;
    new Query[200],pname[24];
    GetPlayerName(playerid,pname,sizeof(pname));
    format(Query,sizeof(Query),"SELECT IP FROM accounts WHERE user = '%s' LIMIT 1",pname);
    mysql_query(Query);
    mysql_store_result();
    new rows = mysql_num_rows();
    if(!rows)
    {
        ShowPlayerDialog(playerid,0,DIALOG_STYLE_INPUT,"Register","Your user is {FF0000}not{FFFFFF} registered! Please {0000FF}register{FFFFFF} with a password below!","Register","Cancel");
    }
    if(rows == 1)
    {
        new IP[2][16];
        mysql_fetch_field_row(IP[0],"IP");
        GetPlayerIp(playerid,IP[1],16);
        if(strlen(IP[0]) != 0 && strcmp(IP[0],IP[1],true))
        {
            MySQL_Login(playerid);
        }
        if(!strlen(IP[0]) || strcmp(IP[0],IP[1],true))
        {
            ShowPlayerDialog(playerid,1,DIALOG_STYLE_INPUT,"Login","Your user is {FF0000}registered{FFFFFF}! Please {0000FF}login{FFFFFF} with your password below!","Login","Cancel"); 
            IsRegistered[playerid] = 1;
        }
    }
    mysql_free_result();
    return 1;
}

public OnPlayerDisconnect(playerid, reason)
{
    if(Logged[playerid] == 1)
    {
        new admin = PlayerData[playerid][pAdmin];
        new money = GetPlayerMoney(playerid);
        new Query[200],pname[24];
        GetPlayerName(playerid,pname,sizeof(pname));
        format(Query,sizeof(Query),"UPDATE accounts SET admin=%d, money=%d, WHERE user='%s'",admin,money,pname);
        mysql_query(Query);
    }
    return 1;
}

public OnPlayerSpawn(playerid)
{
    if(MoneyGiven[playerid] != -1)
    {
        GivePlayerMoney(playerid, MoneyGiven[playerid]);
        MoneyGiven[playerid] = -1;
    }
    return 1;
}

public OnDialogResponse(playerid, dialogid, response, listitem, inputtext[])
{
    if(dialogid == 0)
    {
        if(!response) return Kick(playerid);
        if(response)
        {
            if(!strlen(inputtext) || strlen(inputtext) > 24)
            {
                ShowPlayerDialog(playerid,1,DIALOG_STYLE_INPUT,"Register","Your user is {FF0000}not{FFFFFF} registered! Please {0000FF}register{FFFFFF} with a password below!\n {FF0000}ERROR:Please enter a password between 1-24 characters!","Register","Cancel");
            }
            if(strlen(inputtext) > 0 && strlen(inputtext) <= 24)
            {
                new EscapedText[150];
                mysql_real_escape_string(inputtext,EscapedText);
                MySQL_Register(playerid,EscapedText);
            }
        }
    }
    if(dialogid == 1)
    {
        if(!response) return Kick(playerid);
        if(response)
        {
            new Query[200],pname[24],escapedtext[150];
            GetPlayerName(playerid,pname,sizeof(pname));
            mysql_real_escape_string(inputtext,escapedtext);
            format(Query,sizeof(Query),"SELECT `user` FROM accounts WHERE user = '%s' AND password = SHA1('%s')",pname,escapedtext);
            mysql_query(Query);
            mysql_store_result();
            new rows = mysql_num_rows();
            if(rows == 1)
            {
                MySQL_Login(playerid);
            }
            if(!rows)
            {
                ShowPlayerDialog(playerid, 15500, DIALOG_STYLE_INPUT, "Login","Your user is {FF0000}registered{FFFFFF}! Please {0000FF}login{FFFFFF} with your password below!\n{FF0000} The password you typed was incorrect!","Login","Cancel");
                SendClientMessage(playerid, 0xFF0000, "Incorrect password!");
            }
            mysql_free_result();
        }
    }
    return 1;
}

stock MySQL_Register(playerid,pass[])
{
    new Query[300],pname[24],IP[30];
    GetPlayerName(playerid,pname,sizeof(pname));
    GetPlayerIp(playerid,IP,30);
    format(Query,sizeof(Query),"INSERT TO accounts(user,password,admin,money,IP) VALUES('%s',SHA1('%s'),0,0,'%s')",pname,pass,IP);
    mysql_query(Query);
    SendClientMessage(playerid,-1,"SERVER BOT: You have been registered on this server");
    Logged[playerid] = 1;
    return 1;
}

stock MySQL_Login(playerid)
{
    new Query[300],pname[24],savestr[50];
    GetPlayerName(playerid,pname,sizeof(pname));
    format(Query,sizeof(Query),"SELECT * FROM accounts WHERE user = '%s'",pname);
    mysql_query(Query);
    mysql_store_result();
    while(mysql_fetch_row_format(Query,"|"))
    {
        mysql_fetch_field_row(savestr, "money"); MoneyGiven[playerid] = strval(savestr);
    }
    mysql_free_result();
    SendClientMessage(playerid,-1,"SERVER BOT: You have been logged in!");
    Logged[playerid] = 1;
    return 1;
}

Trying to script another register and login system with tutorial...

But it doesn't work...

It has this in MySQL Debug file.

Код:

[09:49:40] ---------------------------

[09:49:40] MySQL Debugging activated (09/09/13)

[09:49:40] ---------------------------

[09:49:40]  

[09:49:40] >> mysql_connect( )

[09:49:40] CMySQLHandler::CMySQLHandler() - constructor called.

[09:49:40] CMySQLHandler::CMySQLHandler() - Connecting to "127.0.0.1" | DB: "database" | Username: "root" ...

[09:49:40] CMySQLHandler::Connect() - Connection was successful.

[09:49:40] CMySQLHandler::Connect() - Auto-Reconnect has been enabled.

[09:49:40] >> mysql_query( Connection handle: 1 )

[09:49:40] CMySQLHandler::Query(CREATE TABLE IF NOT EXISTS accounts(user VARCHAR(24), password VARCHAR(24), admin INT(20), cash INT(20), IP VARCHAR(16))) - Successfully executed.

[09:50:07] >> mysql_query( Connection handle: 1 )

[09:50:07] CMySQLHandler::Query(SELECT IP FROM accounts WHERE user = 'Wazz' LIMIT 1) - Successfully executed.

[09:50:07] >> mysql_store_result( Connection handle: 1 )

[09:50:07] CMySQLHandler::StoreResult() - Result was stored.

[09:50:07] >> mysql_num_rows( Connection handle: 1 )

[09:50:07] CMySQLHandler::NumRows() - Returned 0 row(s)

[09:50:07] >> mysql_free_result( Connection handle: 1 )

[09:50:07] CMySQLHandler::FreeResult() - Result was successfully free'd.

[09:50:18] >> mysql_real_escape_string( Connection handle: 1 )

[09:50:18] CMySQLHandler::EscapeString(testpass); - Escaped 8 characters to testpass.

[09:50:18] >> mysql_query( Connection handle: 1 )

[09:50:18] CMySQLHandler::Query(INSERT TO accounts(user,password,admin,money,IP) VALUES('Wazz',SHA1('testpass'),0,0,'127.0.0.1')) - An error has occured. (Error ID: 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 'TO accounts(user,password,admin,money,IP) VALUES('Wazz',SHA1('testpass'),0,0,'12' at line 1)

[09:50:25] >> mysql_query( Connection handle: 1 )

[09:50:25] CMySQLHandler::Query(UPDATE accounts SET admin=0, money=0, WHERE user='Wazz') - An error has occured. (Error ID: 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 'WHERE user='Wazz'' at line 1)

I'm starting to make that on this tutorial

Re: Little help with MySQL - Eyce - 09.09.2013

Use this:

pawn Код:

format(Query, sizeof(Query), "UPDATE accounts SET admin='%d', money='%d' WHERE user='%s'", admin, money, pname);

You had an extra comma after "money=%d", also added apostrophes.

Re: Little help with MySQL - xganyx - 10.09.2013

Ok fixed and have this

pawn Код:

[07:43:06] ---------------------------

[07:43:06] MySQL Debugging activated (09/10/13)

[07:43:06] ---------------------------

[07:43:06]  

[07:43:06] >> mysql_connect( )

[07:43:06] CMySQLHandler::CMySQLHandler() - constructor called.

[07:43:06] CMySQLHandler::CMySQLHandler() - Connecting to "127.0.0.1" | DB: "database" | Username: "root" ...

[07:43:06] CMySQLHandler::Connect() - Connection was successful.

[07:43:06] CMySQLHandler::Connect() - Auto-Reconnect has been enabled.

[07:43:06] >> mysql_query( Connection handle: 1 )

[07:43:06] CMySQLHandler::Query(CREATE TABLE IF NOT EXISTS accounts(user VARCHAR(24), password VARCHAR(24), admin INT(20), cash INT(20), IP VARCHAR(16))) - Successfully executed.

[07:43:32] >> mysql_query( Connection handle: 1 )

[07:43:32] CMySQLHandler::Query(SELECT IP FROM accounts WHERE user = 'Phineas' LIMIT 1) - Successfully executed.

[07:43:32] >> mysql_store_result( Connection handle: 1 )

[07:43:32] CMySQLHandler::StoreResult() - Result was stored.

[07:43:32] >> mysql_num_rows( Connection handle: 1 )

[07:43:32] CMySQLHandler::NumRows() - Returned 0 row(s)

[07:43:32] >> mysql_free_result( Connection handle: 1 )

[07:43:32] CMySQLHandler::FreeResult() - Result was successfully free'd.

[07:43:36] >> mysql_real_escape_string( Connection handle: 1 )

[07:43:36] CMySQLHandler::EscapeString(01204855914); - Escaped 11 characters to 01204855914.

[07:43:36] >> mysql_query( Connection handle: 1 )

[07:43:36] CMySQLHandler::Query(INSERT INTO accounts (user, password, admin, money, IP) VALUES('Phineas', SHA1('01204855914'), 0, 0, '127.0.0.1') - An error has occured. (Error ID: 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 '' at line 1)

Here

pawn Код:

format(Query,sizeof(Query),"INSERT INTO accounts (user, password, admin, money, IP) VALUES('%s', SHA1('%s'), 0, 0, '%s'",pname,pass,IP);

Re: Little help with MySQL - Eyce - 10.09.2013

pawn Код:

format(Query, sizeof(Query), "INSERT INTO accounts (user, password, admin, money, IP) VALUES('%s', SHA1('%s'), 0, 0, '%s')", pname, pass, IP);

You forgot the to close the parentheses for VALUES, these characters (comma, apostrophe, parenthesis, etc.) are very important. So make sure that you double check.

Re: Little help with MySQL - xganyx - 10.09.2013

Ok all things is work fine now i have fixed anything and just a little more problem is how can i load player data...

i maded this

pawn Код:

stock MySQL_Login(playerid)
{
    new Query[300],pname[24],savestr[50];
    GetPlayerName(playerid,pname,sizeof(pname));
    format(Query,sizeof(Query),"SELECT * FROM accounts WHERE user = '%s'",pname);
    mysql_query(Query);
    mysql_store_result();
    while(mysql_fetch_row_format(Query,"|"))
    {
        mysql_fetch_field_row(savestr, "money"); MoneyGiven[playerid] = strval(savestr);
    }
    mysql_free_result();
    SendClientMessage(playerid,-1,"SERVER BOT: You have been logged in!");
    Logged[playerid] = 1;
    return 1;
}

And

pawn Код:

public OnPlayerSpawn(playerid)
{
    if(MoneyGiven[playerid] != -1)
    {
        GivePlayerMoney(playerid, MoneyGiven[playerid]);
        MoneyGiven[playerid] = -1;
    }
    return 1;
}

But it don't give me the money...

Re: Little help with MySQL - xganyx - 11.09.2013

Bump. Please help me i can't find a nything how to Load Player i just know how to save player

Re: Little help with MySQL - ViruZz - 11.09.2013

Are you sure OnPlayerSpawn is not getting called before you actually load the player?

Re: Little help with MySQL - xganyx - 11.09.2013

Whut?..

I just want to make a load player when player login.