AlliedModders

AlliedModders (https://forums.alliedmods.net/index.php)
-   Scripting Help (https://forums.alliedmods.net/forumdisplay.php?f=11)
-   -   3d Array saving (MySQL) [90 Queires?] (https://forums.alliedmods.net/showthread.php?t=223629)

^SmileY 08-15-2013 14:16

3d Array saving (MySQL) [90 Queires?]
 
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
    
);



fysiks 08-15-2013 21:32

Re: 3d Array saving (MySQL)
 
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).

^SmileY 08-15-2013 22:15

Re: 3d Array saving (MySQL)
 
1 Attachment(s)
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 :3

fysiks 08-15-2013 23:04

Re: 3d Array saving (MySQL)
 
Quote:

Originally Posted by ^SmileY (Post 2014274)
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 :3

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)?

^SmileY 08-16-2013 09:42

Re: 3d Array saving (MySQL)
 
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];

^SmileY 06-27-2014 01:37

Re: 3d Array saving (MySQL)
 
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)
); 


Black Rose 06-27-2014 07:39

Re: 3d Array saving (MySQL) [90 Queires?]
 
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.

Backstabnoob 06-27-2014 07:58

Re: 3d Array saving (MySQL) [90 Queires?]
 
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.

Arkshine 06-27-2014 09:40

Re: 3d Array saving (MySQL) [90 Queires?]
 
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.


Backstabnoob 06-27-2014 09:59

Re: 3d Array saving (MySQL) [90 Queires?]
 
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.


All times are GMT -4. The time now is 15:51.

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