Raised This Month: $ Target: $400
 0% 

[Help] MySQL & PHP


Post New Thread Reply   
 
Thread Tools Display Modes
Author Message
Croxeye
Member
Join Date: Jul 2015
Old 01-30-2016 , 06:25   [Help] MySQL & PHP
Reply With Quote #1

This is simple code, 1 KILL = 1 GOLD saved in MySQL database

PHP Code:
#include <amxmodx>
#include <amxmisc>
#include <ColorChat>
#include <sqlx>

#define PLUGIN "MySQL Gold System"
#define VERSION "1.0"
#define AUTHOR "Croxeye"

new Host[]     = "localhost"
new User[]    = "root"
new Pass[]     = ""
new Db[]     = "server"

new Handle:g_SqlTuple
new g_Error[512]

new 
gGolds 33 ];


public 
plugin_init() {
    
register_plugin(PLUGINVERSIONAUTHOR)
    
    
register_event"DeathMsg""Hook_Deathmessage""a" );
    
register_clcmd"say /golds""cmd_showgolds" );
    
    
set_task(1.0"MySql_Init")
}

public 
MySql_Init()
{
    
g_SqlTuple SQL_MakeDbTuple(Host,User,Pass,Db)

    new 
ErrorCode,Handle:SqlConnection SQL_Connect(g_SqlTuple,ErrorCode,g_Error,charsmax(g_Error))
    if(
SqlConnection == Empty_Handle)
        
set_fail_state(g_Error)
       
    new 
Handle:Queries
    Queries 
SQL_PrepareQuery(SqlConnection,"CREATE TABLE IF NOT EXISTS save_golds (SteamID varchar(32), Golds INT(11))")

    if(!
SQL_Execute(Queries))
    {
        
SQL_QueryError(Queries,g_Error,charsmax(g_Error))
        
set_fail_state(g_Error)
    }

    
SQL_FreeHandle(Queries)

    
SQL_FreeHandle(SqlConnection)   
}

public 
client_putinserver(id)
{
    
Load_MySql(id)
}

public 
client_disconnect(id)
{
    
Save_MySql(id)
}

public 
plugin_end()
{
    
SQL_FreeHandle(g_SqlTuple)
}

public 
Load_MySql(id)
{
    new 
SteamID[32], szTemp[512]
    
get_user_authid(idSteamIDcharsmax(SteamID))
    
    new 
Data[1]
    
Data[0] = id

    format
(szTemp,charsmax(szTemp),"SELECT * FROM `save_golds` WHERE (`save_golds`.`SteamID` = '%s')"SteamID)
    
SQL_ThreadQuery(g_SqlTuple,"register_client",szTemp,Data,1)
}

public 
register_client(FailState,Handle:Query,Error[],Errcode,Data[],DataSize)
{
    if(
FailState == TQUERY_CONNECT_FAILED)
    {
        
log_amx("Load - Could not connect to SQL database.  [%d] %s"ErrcodeError)
    }
    else if(
FailState == TQUERY_QUERY_FAILED)
    {
        
log_amx("Load Query failed. [%d] %s"ErrcodeError)
    }

    new 
id
    id 
Data[0]
    
    if(
SQL_NumResults(Query) < 1
    {
        new 
SteamID[32]
        
get_user_authid(idSteamIDcharsmax(SteamID))
        
        if (
equal(SteamID,"ID_PENDING"))
            return 
PLUGIN_HANDLED;
            
        new 
szTemp[512]
        
        
format(szTemp,charsmax(szTemp),"INSERT INTO `save_golds` ( `SteamID` , `Golds`)VALUES ('%s','0');"SteamID)
        
SQL_ThreadQuery(g_SqlTuple,"IgnoreHandle",szTemp)
    } 
    else 
    {
        
gGolds[id]         = SQL_ReadResult(Query1)
    }
    
    return 
PLUGIN_HANDLED
}

public 
Save_MySql(id)
{
    new 
SteamID[32], szTemp[512]
    
get_user_authid(idSteamIDcharsmax(SteamID))
    
    
format(szTemp,charsmax(szTemp),"UPDATE `save_golds` SET `Golds` = '%i' WHERE `save_golds`.`SteamID` = '%s';"gGolds[id], SteamID)
    
SQL_ThreadQuery(g_SqlTuple,"IgnoreHandle",szTemp)
}

public 
IgnoreHandle(FailState,Handle:Query,Error[],Errcode,Data[],DataSize)
{
    
SQL_FreeHandle(Query)
    
    return 
PLUGIN_HANDLED
}

public 
cmd_showgolds(id)
{    
    
ColorChatidGREEN"^1[^4GOLD SYSTEM^1] You Have ^4%d ^1Golds"gGoldsid ] );    
}

public 
Hook_Deathmessage()
{
    new 
killer read_data);
    new 
victim read_data);
    
    if( 
killer == victim )
    {
        return 
PLUGIN_HANDLED;
    }
    
    
gGoldskiller ] += 1

    
return PLUGIN_CONTINUE;

So, I created a PHP page to add golds using MySQL

PHP Code:
<html>
<
head>
    <
title>MySQL Gold System</title>
    <
link rel="stylesheet" type="text/css" href="style.css">
</
head>
<
body>
    <
form method="POST" action="golds_send.php">
        <
br><label for="steamid"><strong>SteamID</strong></label>
        <
br><input type="text" id="steamid" name="steamid" required="required">
        
        <
br><label for="golds"><strong>Golds</strong></label>
        <
br><input type="number" id="golds" name="golds" required="required">
        
        <
br><br><input type="submit" name="submit" id="submit" value="Send">
    </
form>
</
body>
<
html
PHP Code:
<?php

$connect 
= new PDO('mysql:host=localhost;dbname=server''root''');

$steamid $_POST['steamid'];
$golds $_POST['golds'];

if (
$steamid&&$golds) {
    
$connect->query("UPDATE `save_golds` SET `golds`= `golds` + '$golds' WHERE `save_golds`.`steamid` = '$steamid'");
}

?>
When I add golds to player from php page and then I change map, golds that I added from page are removed. (Ex. I killed 10 player and I get 10 golds. I gave myself 100 golds from php page and when I check database, I see 110 golds but when I restart server or change map, My golds are set to 10 again)

Last edited by Croxeye; 01-30-2016 at 06:29.
Croxeye is offline
Bugsy
AMX Mod X Moderator
Join Date: Feb 2005
Location: NJ, USA
Old 01-30-2016 , 07:41   Re: [Help] MySQL & PHP
Reply With Quote #2

Are you sure that the golds column is the second one returned in your query? You should maybe specify the column in the query instead of using "*".

Edit: I'm not sure if this is the cause of your issue, but you need to try things like this to debug.
PHP Code:
        gGolds[id]         = SQL_ReadResult(Query1
Replace
PHP Code:
SELECT FROM `save_goldsWHERE (`save_golds`.`SteamID` = '%s')
//with
SELECT Golds FROM `save_goldsWHERE (`save_golds`.`SteamID` = '%s')

//change
gGolds[id]         = SQL_ReadResult(Query1)
//to
gGolds[id]         = SQL_ReadResult(Query0
__________________

Last edited by Bugsy; 01-30-2016 at 08:15.
Bugsy is offline
Croxeye
Member
Join Date: Jul 2015
Old 01-30-2016 , 08:25   Re: [Help] MySQL & PHP
Reply With Quote #3

Quote:
Originally Posted by Bugsy View Post
Are you sure that the golds column is the second one returned in your query? You should maybe specify the column in the query instead of using "*".

Edit: I'm not sure if this is the cause of your issue, but you need to try things like this to debug.
PHP Code:
        gGolds[id]         = SQL_ReadResult(Query1
Replace
PHP Code:
SELECT FROM `save_goldsWHERE (`save_golds`.`SteamID` = '%s')
//with
SELECT Golds FROM `save_goldsWHERE (`save_golds`.`SteamID` = '%s')

//change
gGolds[id]         = SQL_ReadResult(Query1)
//to
gGolds[id]         = SQL_ReadResult(Query0
Again same error.. I added golds from php page, I check my golds in database and all is OK. After I change map or rr server, my golds from php page are removed.

But when I add golds from server using cmd amx_golds, golds are saved permanently.

Last edited by Croxeye; 01-30-2016 at 08:37.
Croxeye is offline
Bugsy
AMX Mod X Moderator
Join Date: Feb 2005
Location: NJ, USA
Old 01-30-2016 , 08:45   Re: [Help] MySQL & PHP
Reply With Quote #4

After you change map, is the data still correct in the database?
__________________
Bugsy is offline
Croxeye
Member
Join Date: Jul 2015
Old 01-30-2016 , 08:49   Re: [Help] MySQL & PHP
Reply With Quote #5

After I change map, golds from php page are removed in database too...

And now plugin register same client (SteamID) 2 times :/

Last edited by Croxeye; 01-30-2016 at 08:53.
Croxeye is offline
Bugsy
AMX Mod X Moderator
Join Date: Feb 2005
Location: NJ, USA
Old 01-30-2016 , 09:31   Re: [Help] MySQL & PHP
Reply With Quote #6

Your plugin works fine for me. One thing you should do is make SteamID your primary key. I did this and a few other fixes in the below:
PHP Code:

#include <amxmodx>
#include <amxmisc>
#include <ColorChat>
#include <sqlx>

#define PLUGIN "MySQL Gold System"
#define VERSION "1.0"
#define AUTHOR "Croxeye"

#define MAX_PLAYERS 32

new const Host[] = "localhost"
new const User[] = "root"
new const Pass[] = ""
new const Db[] = "server"

enum PlayerGold
{
    
pgTotal,
    
pgCurrent
}

new 
Handle:g_SqlTuple
new g_Error[512]
new 
gGoldsMAX_PLAYERS ][ PlayerGold ];
new 
g_szAuthIDMAX_PLAYERS ][ 34 ];

public 
plugin_init() {
    
register_plugin(PLUGINVERSIONAUTHOR)
    
    
register_event"DeathMsg""Hook_Deathmessage""a" );
    
register_clcmd"say /golds""cmd_showgolds" );
}

public 
plugin_cfg()
{
    
g_SqlTuple SQL_MakeDbTuple(Host,User,Pass,Db)

    new 
ErrorCode,Handle:SqlConnection SQL_Connect(g_SqlTuple,ErrorCode,g_Error,charsmax(g_Error))
    if(
SqlConnection == Empty_Handle)
        
set_fail_state(g_Error)
       
    new 
Handle:Queries
    Queries 
SQL_PrepareQuery(SqlConnection,"CREATE TABLE IF NOT EXISTS save_golds (SteamID varchar(32) PRIMARY KEY, Golds INT(11))")

    if(!
SQL_Execute(Queries))
    {
        
SQL_QueryError(Queries,g_Error,charsmax(g_Error))
        
set_fail_state(g_Error)
    }

    
SQL_FreeHandle(SqlConnection)   
}

public 
client_authorized(id)
{
    
gGoldsid ][ pgTotal ] = 0;
    
gGoldsid ][ pgCurrent ] = 0;
    
    
get_user_authidid g_szAuthIDid ] , charsmaxg_szAuthID[] ) );
    
Load_MySql(id)
}

public 
client_disconnect(id)
{
    
Save_MySql(id)
}

public 
plugin_end()
{
    
SQL_FreeHandle(g_SqlTuple)
}

public 
Load_MySql(id)
{
    new 
szTemp256 ] , Data]

    
Data] = id;
    
formatexszTemp charsmaxszTemp ) ,"SELECT Golds FROM save_golds WHERE SteamID='%s';" g_szAuthIDid ] )
    
SQL_ThreadQueryg_SqlTuple "register_client" szTemp Data sizeofData ) )
}

public 
register_client(FailState,Handle:Query,Error[],Errcode,Data[],DataSize)
{
    if(
FailState == TQUERY_CONNECT_FAILED)
    {
        
log_amx("Load - Could not connect to SQL database.  [%d] %s"ErrcodeError)
    }
    else if(
FailState == TQUERY_QUERY_FAILED)
    {
        
log_amx("Load Query failed. [%d] %s"ErrcodeError)
    }

    new 
id Data];
    
    if ( !
SQL_NumResultsQuery ) ) 
    {
        new 
szTemp256 ]
        
        
formatexszTemp charsmaxszTemp ) ,"INSERT INTO save_golds (SteamID,Golds) VALUES ('%s',0);" g_szAuthIDid ] )
        
SQL_ThreadQueryg_SqlTuple "IgnoreHandle" szTemp )
    } 
    else 
    {
        
gGoldsid ][ pgTotal ] = SQL_ReadResultQuery )
    }
    
    return 
PLUGIN_HANDLED
}

public 
Save_MySqlid )
{
    new 
szTemp256 ]
    
    
formatex(szTemp,charsmax(szTemp),"UPDATE save_golds SET Golds=Golds+%i WHERE SteamID='%s';"gGoldsid ][ pgCurrent ] , g_szAuthIDid ] )
    
SQL_ThreadQuery(g_SqlTuple,"IgnoreHandle",szTemp)
}

public 
IgnoreHandle(FailState,Handle:Query,Error[],Errcode,Data[],DataSize)
{
    return 
PLUGIN_HANDLED
}

public 
cmd_showgolds(id)
{    
    
ColorChatid GREEN "^1[^4GOLD SYSTEM^1] You Have ^4%d ^1Golds" gGoldsid ][ pgTotal ] + gGoldsid ][ pgCurrent ] );    
}

public 
Hook_Deathmessage()
{
    new 
killer read_data);
    new 
victim read_data);
    
    if( 
killer != victim )
        
gGoldskiller ][ pgCurrent ]++;
    
    return 
PLUGIN_CONTINUE;

__________________

Last edited by Bugsy; 01-30-2016 at 12:05.
Bugsy is offline
Croxeye
Member
Join Date: Jul 2015
Old 01-30-2016 , 10:28   Re: [Help] MySQL & PHP
Reply With Quote #7

Not working again :/

Did you test with PHP too ?
Croxeye is offline
Bugsy
AMX Mod X Moderator
Join Date: Feb 2005
Location: NJ, USA
Old 01-30-2016 , 10:32   Re: [Help] MySQL & PHP
Reply With Quote #8

No, I only tested that the plugin is working correctly. I do not know php, but there's a chance that data is getting lost in that UPDATE statement.
__________________
Bugsy is offline
Croxeye
Member
Join Date: Jul 2015
Old 01-30-2016 , 10:59   Re: [Help] MySQL & PHP
Reply With Quote #9

AMXX plugin works fine... Golds are saved and I don't have problem with that But I want to add golds from PHP Page.

If I kill 100 Player, I will get 100 GOLDS and that golds are saved in database. When I add +100 golds from php page, then in database I see 200 GOLDS. When I change map, my golds are set to 100 again. WHYY ??
Croxeye is offline
Bugsy
AMX Mod X Moderator
Join Date: Feb 2005
Location: NJ, USA
Old 01-30-2016 , 11:17   Re: [Help] MySQL & PHP
Reply With Quote #10

Because the plugin does not know that you made a change on the website.

Here's what is happening:
In the game, player gets gGolds[ id ] = 100
php site changes this player golds to 200
On map change, player disconnects and it saves whatever is held in gGolds[] (100). It does not know that you changed the golds to 200 on the php.

Maybe you should create 2 variables to store golds. One stores all golds, other stores golds for just the connection. Then in the SQL save query, do:
formatex(szTemp,charsmax(szTemp),"UPDATE save_golds SET Golds = Golds + %i WHERE `SteamID` = '%s';", gCurrentGolds[ id ] , g_szAuthID[ id ] )

gCurrentGolds[id] is set to 0 on connect and it does not get loaded from database.

Does this make sense?
__________________

Last edited by Bugsy; 01-30-2016 at 11:18.
Bugsy is offline
Old 01-30-2016, 11:50
Croxeye
This message has been deleted by Croxeye. Reason: nvm
Reply


Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off

Forum Jump


All times are GMT -4. The time now is 09:25.


Powered by vBulletin®
Copyright ©2000 - 2024, vBulletin Solutions, Inc.
Theme made by Freecode