Raised This Month: $ Target: $400
 0% 

3d Array saving (MySQL) [90 Queires?]


Post New Thread Reply   
 
Thread Tools Display Modes
Author Message
^SmileY
Veteran Member
Join Date: Jan 2010
Location: Brazil [<o>]
Old 08-15-2013 , 14:16   3d Array saving (MySQL) [90 Queires?]
Reply With Quote #1

Hi, i need a little help to save a 3d array into mysql db
I need to know how to create a table for do this.

I have this array:

PHP Code:
#include <amxmodx>

new g_iMaxPlayers;

#define IsPlayer(%0) (1 <= %0 <= g_iMaxPlayers)

enum _:Stats
{
    
KILLS 0,
    
DEATHS,
    
HEADSHOTS,
    
SHOTS,
    
DAMAGE,
    
HITS
};

#define MAX_PLAYERS     33
#define MAX_WEAPONS     33
#define MAX_STATS     6

new g_iWeapon[MAX_PLAYERS][MAX_WEAPONS][Stats];     // 

public plugin_init()
{
    
register_plugin("CS Stats Test",AMXX_VERSION_STR,"Amxx Dev Team");

    
register_event("DeathMsg","ev_DeathMsg","a","1>0","2>0");
    
    
g_iMaxPlayers get_maxplayers();
}

public 
ev_DeathMsg()
{
    new 
iVictim read_data(2);
    new 
iKiller get_user_attacker(iVictim);
    new 
iWeapon get_user_weapon(iKiller);
    
    
g_iWeapon[iVictim][iWeapon][DEATHS]++;
    
    if(
IsPlayer(iKiller) && (iKiller != iVictim))
    {
        
g_iWeapon[iKiller][iWeapon][KILLS]++;
        
        if(
read_data(3)) g_iWeapon[iKiller][iWeapon][HEADSHOTS]++;
    }
    
    new 
szName[2][32],szWeapon[32];
    
get_user_name(iKiller,szName[0],charsmax(szName[]));
    
get_user_name(iVictim,szName[1],charsmax(szName[]));
    
    
get_weaponname(iWeapon,szWeapon,charsmax(szWeapon));
    
    
replace_all(szWeapon,charsmax(szWeapon),"weapon_","");

    
client_print
    
(
        
0,
        
print_center,
        
"%s Killed %s with their %s",
        
szName[0],
        
szName[1],
        
szWeapon
    
);

__________________
Projects:

- See my Git Hub: https://github.com/SmileYzn
PHP Code:
set_pcvar_num(pCvar, !get_pcvar_num(pCvar)); 
^SmileY is offline
Send a message via MSN to ^SmileY Send a message via Skype™ to ^SmileY
fysiks
Veteran Member
Join Date: Sep 2007
Location: Flatland, USA
Old 08-15-2013 , 21:32   Re: 3d Array saving (MySQL)
Reply With Quote #2

Have you tried searching for an answer to this? I'm sure it's been asked before on SQL/Database/Scripting websites. I'm sure there are many valid answers.

How are you going to save the players? SteamID? I don't use SQL that much so I don't know all the nifty features but I would have a table for each weapon and then have a list of the players with their associated value. So, you would have every player in each weapon table once.

But, like I said, there are probably many valid answers and likely one better than mine but you need to make an effort to find the solution in a better place (IMO).
__________________
fysiks is offline
^SmileY
Veteran Member
Join Date: Jan 2010
Location: Brazil [<o>]
Old 08-15-2013 , 22:15   Re: 3d Array saving (MySQL)
Reply With Quote #3

Sorry, i searched but i need a way to save a 3d Array weapons into any table..
If weapon exists it will update only i have this code and not find anything related to weapons and sql.

I save a 3d array for use to weapon kills, deaths, hs and others and using steamid
Attached Files
File Type: sma Get Plugin or Get Source (stats.sma - 579 views - 16.4 KB)
__________________
Projects:

- See my Git Hub: https://github.com/SmileYzn
PHP Code:
set_pcvar_num(pCvar, !get_pcvar_num(pCvar)); 
^SmileY is offline
Send a message via MSN to ^SmileY Send a message via Skype™ to ^SmileY
fysiks
Veteran Member
Join Date: Sep 2007
Location: Flatland, USA
Old 08-15-2013 , 23:04   Re: 3d Array saving (MySQL)
Reply With Quote #4

Quote:
Originally Posted by ^SmileY View Post
Sorry, i searched but i need a way to save a 3d Array weapons into any table..
If weapon exists it will update only i have this code and not find anything related to weapons and sql.

I save a 3d array for use to weapon kills, deaths, hs and others and using steamid
That plugin appears to be complete to me . . . does it work? Did you write that whole thing? What do you currently have implemented in SQL there?

Why not try what I suggested (If you have no other solution)?
__________________
fysiks is offline
^SmileY
Veteran Member
Join Date: Jan 2010
Location: Brazil [<o>]
Old 08-16-2013 , 09:42   Re: 3d Array saving (MySQL)
Reply With Quote #5

Not, is not complete it is saving a stats, but i need basically to save weapon stats for each SteamID, and if exists the weapon + Steamid, will update the registry.

g_iWeapons[33][MAX_WEAPONS][STATS];
__________________
Projects:

- See my Git Hub: https://github.com/SmileYzn
PHP Code:
set_pcvar_num(pCvar, !get_pcvar_num(pCvar)); 
^SmileY is offline
Send a message via MSN to ^SmileY Send a message via Skype™ to ^SmileY
^SmileY
Veteran Member
Join Date: Jan 2010
Location: Brazil [<o>]
Old 06-27-2014 , 01:37   Re: 3d Array saving (MySQL)
Reply With Quote #6

Hi, BUMP again!

I created the function to save in db, but is using so many queries (One query to one weapon if the player died by weapon or fired the weapon)

I can optmize this part?

PHP Code:
    new sWeapon[32],sQuery[256];
    
    for(new 
iWeapon;iWeapon sizeof(g_iWeapon[]);iWeapon++)
    {
        if(
g_iWeapon[id][iWeapon][Shots] || g_iWeapon[id][iWeapon][Deaths])
        {
            
get_weaponname(iWeapon,sWeapon,charsmax(sWeapon));
            
            
format
            
(
                
sQuery,
                
charsmax(sQuery),
                
"CALL PugSaveWeapon(%i, '%s', %i, %i, %i, %i, %i, %i, '%s')",
                
iWeapon,
                
sWeapon,
                
g_iWeapon[id][iWeapon][Kills],
                
g_iWeapon[id][iWeapon][Deaths],
                
g_iWeapon[id][iWeapon][Headshots],
                
g_iWeapon[id][iWeapon][Shots],
                
g_iWeapon[id][iWeapon][Hits],
                
g_iWeapon[id][iWeapon][Damage],
                
sSteam
            
);
            
            
SQL_ThreadQuery(g_hSQL,"PugHandlerSQL",sQuery); // Arround 9 queries per player disconnect, if is a 10 player servers, this will be 90 queries at change map and changelevel
        
}
    } 
The table in DB

PHP Code:
CREATE TABLE IF NOT EXISTS pug_weapon
(
    
id INT NOT NULL AUTO_INCREMENT,
    
weapon SMALLINT NOT NULL DEFAULT 0,
    
string VARCHAR(32NOT NULL DEFAULT '',
    
kills SMALLINT NOT NULL DEFAULT 0,
    
deaths SMALLINT NOT NULL DEFAULT 0,
    
headshots SMALLINT NOT NULL DEFAULT 0,
    
shots SMALLINT NOT NULL DEFAULT 0,
    
hits SMALLINT NOT NULL DEFAULT 0,
    
damage SMALLINT NOT NULL DEFAULT 0,
    
steam VARCHAR(35NOT NULL DEFAULT '',
    
PRIMARY KEY (id),
    
FOREIGN KEY (steamREFERENCES pug_players(steam)
); 
__________________
Projects:

- See my Git Hub: https://github.com/SmileYzn
PHP Code:
set_pcvar_num(pCvar, !get_pcvar_num(pCvar)); 

Last edited by ^SmileY; 06-27-2014 at 01:37.
^SmileY is offline
Send a message via MSN to ^SmileY Send a message via Skype™ to ^SmileY
Black Rose
Veteran Member
Join Date: Feb 2011
Location: Stockholm, Sweden
Old 06-27-2014 , 07:39   Re: 3d Array saving (MySQL) [90 Queires?]
Reply With Quote #7

Afaik, you have these options:
1. Place everything on one row in the table.
2. Send stacked queries (by adding them after each other within the same string). I don't know how the AMXX SQL core will handle it though. It may split them before sending.
3. Send the SQL queries instantly instead of buffering them until the end of the map. The count will increase, but the stream will be more even.
4. Store the information locally in a file or a vault before mapchange and then once the map starts, send the stored queries one by one.

Personally I would experiment with the fourth option because it sounds interesting.
__________________

Last edited by Black Rose; 06-27-2014 at 07:41.
Black Rose is offline
Backstabnoob
BANNED
Join Date: Feb 2009
Location: Iwotadai Dorm
Old 06-27-2014 , 07:58   Re: 3d Array saving (MySQL) [90 Queires?]
Reply With Quote #8

If you don't mind using a third party solution then this can do what you need easily: https://forums.alliedmods.net/showthread.php?t=242833
It combines rows and sends as many rows as the buffer allows with a single query. So you could get 90 down to 10 or something.

If you're interested, I can show you how to do it.

Last edited by Backstabnoob; 06-27-2014 at 07:58.
Backstabnoob is offline
Arkshine
AMX Mod X Plugin Approver
Join Date: Oct 2005
Old 06-27-2014 , 09:40   Re: 3d Array saving (MySQL) [90 Queires?]
Reply With Quote #9

If I don't say bullshits, I guess Transaction API like in SM would be welcomed in AMXX, right?

Quote:
Originally Posted by Documentation
A transaction object is a list of queries that can be sent to the database thread and executed as a single transaction.
__________________

Last edited by Arkshine; 06-27-2014 at 09:41.
Arkshine is offline
Backstabnoob
BANNED
Join Date: Feb 2009
Location: Iwotadai Dorm
Old 06-27-2014 , 09:59   Re: 3d Array saving (MySQL) [90 Queires?]
Reply With Quote #10

The way I'm doing it is sending multiple rows with REPLACE INTO table VALUES (row1), (row2), (row3)...

Transactions aren't really necessary for this because you can simply insert/replace multiple rows with one query.
Also, there's hardly any need for an API IMO, as you can just send this via a threaded query:
START TRANSACTION; QUERY1; QUERY2; QUERY3; COMMIT

Which works, but the callback is called for each query anyways, so I'm not sure if there's any improvement in terms of performance. The adventage of transactions is not speed/performance/less queries, but the ability to roll back the entire transaction if something goes wrong.

Again, there's no need to reinvent the wheel, I already had to deal with the same problem which was a lot of queries and my solution is probably the best.

Last edited by Backstabnoob; 06-27-2014 at 10:09.
Backstabnoob is offline
Reply



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 15:51.


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