[dustinandband]

So I have two database tables which are related.

One Main table:
https://i.imgur.com/PE28pek.png

The other table logs events:
https://i.imgur.com/nWvff2T.png

the id_of_Survival_Main (from LoggedEvents) is an integer that corrisponds with the id# of the main table.

My issue is that I have multiple events which I need to store somewhere until I know the id# from the main DB table (which won't happen after the main query is completed):

Spoiler

PHP Code:

{
    // main insertion query - inserting into SourceTV_Survival_Main
    g_hDatabase.Query(TQuery_MainTableStats, sQuery, pack);
}

public void TQuery_MainTableStats(Database db, DBResultSet results, const char[] error, DataPack pack)
{
    pack.Reset();
    
    if (results == null)
    {
        char sPreviousQuery[MAX_QUERY_LENGTH];
        pack.ReadString(sPreviousQuery, sizeof(sPreviousQuery));
        
        LogError("Error logging query to DB:");
        LogError(sPreviousQuery);
        delete pack;
        return;
    }
    delete pack;
    
    // TODO - is there a better way to determine the DB id # from the last query?
    
    char sQuery [82], sTable[64];
    g_cvDBtable_Surv_Main.GetString(sTable, sizeof(sTable));
    
    g_hDatabase.Format(sQuery, sizeof(sQuery), "SELECT * FROM `%s` ORDER BY id DESC LIMIT 1;", sTable);
    g_hDatabase.Query(TQuery_FindID, sQuery);
}

// extra step just to retrieve DB ID so we can associate it with PlayerInfo and LoggedEvents tables
public void TQuery_FindID(Database db, DBResultSet results, const char[] error, any data)
{
    if (results == null)
    {
        ThrowError("Error retrieving results: %s", error);
        return;
    }
    
    int iID_SurvMainTable;
    if (results.FetchRow())
    {
        int field = -1;
        if (SQL_FieldNameToNum(results, "id", field) && field != -1)
        {
            // retrieve "id" so we can log it into "id_of_Survival_Main"
            iID_SurvMainTable = results.FetchInt(field);
        }
    }
    
    // now we can log to other DB tables
    
} 


I was planning on having a key-value system where I can store these values (until the survival round ends and we know what the value of "id_of_Survival_Main" will be), and just iterate over all the stored values in the key-value system while adding each one to a transaction statement. I'm curious if there's a less hacky way of storing multiple pieces of data or is this the preferred method in a situation like this?

Spoiler

PHP Code:

"SourceTV_LoggedEvents"    // to be logged into "SourceTV_Survival_LoggedEvents" DB table
{
    "0"
    {
        "name"                    "dustin"
        "authID"                "86484684684684"
        "reason"                "Idling while charged (may be avoiding dmg)"
        "time_formatted"        "10:50.20"
        "time_tick"                "4001"
    }
    "1"
    {
        "name"                    "zef"
        "authID"                "5406540654654"
        "reason"                "Idling within 13 seconds of being boomed"
        "time_formatted"        "12:49.25"
        "time_tick"                "4501"
    }
    "2"
    {
        "name"                    "angel"
        "authID"                "6516515151"
        "reason"                "SMAC aimbot detection"
        "time_formatted"        "13:50.25"
        "time_tick"                "5100"
    }
    "3"
    {
        "name"                    "scout"
        "authID"                "45345344"
        "reason"                "Admin command usage: 'sm_noclip @me'"
        "time_formatted"        "14:50.25"
        "time_tick"                "6100"
    }
} 


[Peace-Maker]

I don't understand what you're trying to do, but the auto incremented id of the INSERT query can be accessed using results.InsertId https://sm.alliedmods.net/new-api/db...ltSet/InsertId

[dustinandband]

1)
I won't know the value of the autoincrement ID# until after the main DB query is complete.

2)
Since I won't be able to log these events in real-time to the DB - i need a way of storing them somewhere.

3)
There could be any number of events getting logged per round ( 0 - 50+ )

Example:

name (char[])	authID (char[])		reason ((char[]))				time_formatted((char[]))	time_tick(int or char)
"dustin"		"84684684684"		"Idling while charged"			"105:40.50"					"15351511"

name (char[])	authID (char[])		reason ((char[]))				time_formatted((char[]))	time_tick(int or char)
"angel"			"64684684684"		"SMAC detection"				"110:20.27"					"15364684"

4)
I'm looking for methods of storing multiple strings so, for example,
The first variable "dustin" relates to the proper authID, reason, time_formatted, and time_tick

I'm thinking of either the key-value system that was in the OP, or maybe declaring 5 different arraylists and just iterating through that when I'm ready to log into the EventsLog database table.

But I'm wondering if there's any other methods for storing this info within the plugin until it’s ready to be logged into the database.

Edit:
nvm just ended up using arraylist method to store values.
Not sure why every time I ask a question regarding databases people get confused.

Spoiler

PHP Code:

// tracking logged events
ArrayList g_hArray_Event_Name;
ArrayList g_hArray_Event_AuthID;
ArrayList g_hArray_Event_Reason;
ArrayList g_hArray_Event_TimeFormatted;
ArrayList g_hArray_Event_TimeTick;

public void OnPluginStart()
{
    g_hArray_Event_Name = new ArrayList(ByteCountToCells(32));
    g_hArray_Event_AuthID = new ArrayList(ByteCountToCells(32));
    g_hArray_Event_Reason = new ArrayList(ByteCountToCells(256));
    g_hArray_Event_TimeFormatted = new ArrayList(ByteCountToCells(16));
    g_hArray_Event_TimeTick = new ArrayList(ByteCountToCells(32
}

void EventLogic_LogEvent(int client, const char[] reason, any ...)
{
    char sBuffer[4096];
    VFormat(sBuffer, sizeof(sBuffer), reason, 3);
    
    // client name
    char sName[32];
    GetClientName(client, sName, sizeof(sName));
    
    // authID (if steam is down we still want to put something into array)
    char sAuthId[32];
    if (!GetClientAuthId(client, AuthId_SteamID64, sAuthId, sizeof(sAuthId)))
    {
        strcopy(sAuthId, sizeof(sAuthId), "steam servers down");
    }
    
    // formatted_time
    float fSec = GetGameTime() - g_fSurvivalClockStartTime;
    char sTime[16];
    FormatSeconds(fSec, sTime, sizeof(sTime), FORMATTED_TIME, true);
    
    // time_tick
    char sDemoTick[32];
    IntToString(SourceTV_GetRecordingTick(), sDemoTick, sizeof(sDemoTick));
    
    g_hArray_Event_Name.PushString(sName);
    g_hArray_Event_AuthID.PushString(sAuthId);
    g_hArray_Event_Reason.PushString(reason);
    g_hArray_Event_TimeFormatted.PushString(sTime);
    g_hArray_Event_TimeTick.PushString(sDemoTick);
}

// sometime after executing the main query :

char sName[MAX_NAME_LENGTH], sAuthID[32], sReason[256], sTime[16], sTime_Tick[32];

if (g_hArray_Event_Name.Length > 0)
{
    for (int i = 0; i < g_hArray_Event_Name.Length; i++)
    {
        // name escaped
        g_hArray_Event_Name.GetString(i, sName, sizeof(sName));
        int size3 = 2 * strlen(sName) + 1;
        char[] sEscapedName = new char[size3];
        g_hDatabase.Escape(sName, sEscapedName, size3);
        
        // auth ID
        g_hArray_Event_AuthID.GetString(i, sAuthID, sizeof(sAuthID));
        
        // reason
        g_hArray_Event_Reason.GetString(i, sReason, sizeof(sReason));
        
        // escaped (in case admin writes args that'll mess with DB query;)
        // !admin '... 
        int size = 2 * strlen(sReason) + 1;
        char[] sEscapedReason = new char[size];
        g_hDatabase.Escape(sReason, sEscapedReason, size);
        
        // time_formatted
        g_hArray_Event_TimeFormatted.GetString(i, sTime, sizeof(sTime));
        
        // time_tick
        g_hArray_Event_TimeTick.GetString(i, sTime_Tick, sizeof(sTime_Tick));
        
        char sQuery2[MAX_QUERY_LENGTH];
        Format(sQuery2, sizeof(sQuery2), "INSERT INTO `%s` (id_of_Survival_Main, name, authID, reason, time_formatted, time_tick) VALUES ('%i', '%s', '%s', '%s', '%s', '%s');", sTable, iID_SurvMainTable, sEscapedName, sAuthID, sEscapedReason, sTime, sTime_Tick);
        
        trans.AddQuery(sQuery2);
    }
}