Database load stats error

epQQ · **Author**

Hello, I have a problem with loadinfo in my database.
When the plugin uses the saveInfo function, it appears in the right place. But when the map changes all the values in the table are in another column.
loadinfo

Code:

		for(i = 0; i < MaxStats; ++i)
			g_stats[id][i] = SQL_ReadResult(sqlQuery, (42 + i));
		
		SQL_ReadResult(sqlQuery, (42 + MaxStats), Float:g_damage[id][0]);
		SQL_ReadResult(sqlQuery, (43 + MaxStats), Float:g_damage[id][1]);
		
		SQL_ReadResult(sqlQuery, (44 + MaxStats), sInfo__128, 127);
		parse(sInfo__128, sHeadZombie[0], 5, sHeadZombie[1], 5, sHeadZombie[2], 5, sHeadZombie[3], 5, sHeadZombie[4], 5, sHeadZombie[5], 5, sHeadZombie[6], 5, sHeadZombie[7], 5);
		
		g_head_zombie[id][HEAD_RED] = str_to_num(sHeadZombie[0]);
		g_head_zombie[id][HEAD_GREEN] = str_to_num(sHeadZombie[1]);
		g_head_zombie[id][HEAD_BLUE] = str_to_num(sHeadZombie[2]);
		g_head_zombie[id][HEAD_YELLOW] = str_to_num(sHeadZombie[3]);
		g_head_zombie[id][HEAD_WHITE] = str_to_num(sHeadZombie[4]);
		g_head_zombie[id][HEAD_VIOLET_SMALL] = str_to_num(sHeadZombie[5]);
		g_head_zombie[id][HEAD_VIOLET_BIG] = str_to_num(sHeadZombie[6]);
		g_head_zombie[id][HEAD_DARK_BLUE] = str_to_num(sHeadZombie[7]);
		
		g_logros_stats[id][COUNT_UNLIMITED_CLIP] = SQL_ReadResult(sqlQuery, (45 + MaxStats));
		
		g_time_playing[id][0] = SQL_ReadResult(sqlQuery, (46 + MaxStats));
		
		new iHour = 0;
		new iDay = 0;
		
		iHour = g_time_playing[id][0] / 60;
		
		while(iHour >= 24)
		{
			++iDay;
			iHour -= 24;
		}
		
		g_time_playing[id][3] = g_time_playing[id][0] - ((iHour * 60) + (iDay * 24 * 60));
		g_time_playing[id][1] = iHour;
		g_time_playing[id][2] = iDay;
		
		g_logros_count[id] = SQL_ReadResult(sqlQuery, (47 + MaxStats));
		g_desafios_count[id] = SQL_ReadResult(sqlQuery, (48 + MaxStats));
		
		g_duelo_win[id] = SQL_ReadResult(sqlQuery, (49 + MaxStats));
		g_duelo_lost[id] = SQL_ReadResult(sqlQuery, (50 + MaxStats));
		
		g_gift[id] = SQL_ReadResult(sqlQuery, (51 + MaxStats));
		
		g_legendaria_knife_damage[id] = SQL_ReadResult(sqlQuery, (52 + MaxStats));
		
		SQL_FreeHandle(sqlQuery);
	}
	else
		SQL_FreeHandle(sqlQuery);

saveinfo

Code:

	formatex(sHud, 127, "%d %d %d %d %d %d %d %d", g_head_zombie[id][HEAD_RED], g_head_zombie[id][HEAD_GREEN], g_head_zombie[id][HEAD_BLUE], g_head_zombie[id][HEAD_YELLOW], g_head_zombie[id][HEAD_WHITE], g_head_zombie[id][HEAD_VIOLET_SMALL], g_head_zombie[id][HEAD_VIOLET_BIG], g_head_zombie[id][HEAD_DARK_BLUE]);
	#define LOG_FILE "sql_queries.log"
	iLen += formatex(sQuery[iLen], charsmax(sQuery) - iLen, "UPDATE stats SET kill_zd='%d', kill_zt='%d', kill_hd='%d', kill_ht='%d', infects_d='%d', infects_t='%d', combos_d='%d', combos_max_d='%d', kill_nd='%d', kill_sd='%d' WHERE zp_id='%d';",
    g_stats[id][KILL_ZOMBIES_D], g_stats[id][KILL_ZOMBIES_T], g_stats[id][KILL_HUMANS_D], g_stats[id][KILL_HUMANS_T], g_stats[id][INFECTS_D], g_stats[id][INFECTS_T], g_stats[id][COMBOS_D], g_stats[id][COMBO_MAX_D], g_stats[id][KILL_NEMESIS_D], g_stats[id][KILL_SURVIVOR_D], g_user_id[id]);
	log_to_file(LOG_FILE, "Consulta SQL generada 1: %s", sQuery);
	iLen = 0; 
	iLen += formatex(sQuery[iLen], charsmax(sQuery) - iLen, "UPDATE stats SET kill_zkd='%d', kill_zkt='%d', dmg_armor='%d', am_crea='%d', duel_final_win='%d' WHERE zp_id='%d';",
    g_stats[id][KILL_ZOMBIES_KNIFE_D], g_stats[id][KILL_ZOMBIES_KNIFE_T], g_stats[id][DAMAGE_ARMOR], g_stats[id][AMULETOS_CREATE], g_stats[id][DUEL_FINAL_WINS], g_user_id[id]);
	log_to_file(LOG_FILE, "Consulta SQL generada 2: %s", sQuery);
	iLen = 0; 
	iLen += formatex(sQuery[iLen], charsmax(sQuery) - iLen, "UPDATE stats SET damage_d='%0.20f', damage_t='%0.20f', time='%d', duel_win='%d', duel_lost='%d', knife_dmg='%d' WHERE zp_id='%d';",
    g_damage[id][0], g_damage[id][1], g_time_playing[id][0], g_duelo_win[id], g_duelo_lost[id], g_legendaria_knife_damage[id], g_user_id[id]);
	log_to_file(LOG_FILE, "Consulta SQL generada 3: %s", sQuery);
	sqlQuery = SQL_PrepareQuery(g_sql_connection, sQuery);
	
	if(!SQL_Execute(sqlQuery))
		executeQuery(id, sqlQuery, 8);
	else
		SQL_FreeHandle(sqlQuery);

Rohanlogs · 03-02-2024 , 09:37 Re: Database load stats error

You have from 45 to 100 columns in your table structure?
That sounds kinda terrible for just saving your stats, but hey as long as it works right?

What do you mean by "all the values in the table are in another column"?

I don't quite understand your issue, but I'm just assuming the loading function loads data into wrong variables?
You haven't posted your table structure, so its impossible to tell what's going wrong just by this.

To find your problem, first off check any errors in your error logs.
Then add some logging into your loading function:

Spoiler

Check what this outputs, then check from your database to see if these values are correct.
Adjust (x + MaxStats) values so that the data is fetched from the correct columns, which I imagine is probably a bit painful due to your excessive amount of columns but it is what it is.

I hope you have some understanding on coding if this is your own code?
Here's a breakdown on reading columns from results

Syntax

Code:

native SQL_ReadResult(Handle:query, column, any:...);

Usage:
query - Handle of a query to read results from.
column - Which column to get the value from.
...	
Passing no extra arguments - returns an integer.
Passing one extra argument - returns a float in the first extra argument
Passing two extra params - returns a string in the first argument
with a maximum string length in the second argument.

Reading an integer column:

PHP Code:


			
// Reading column 0 which is typically the column ID if you have auto_increment, or other relations

new id;

id = SQL_ReadResult(hQuery, 0);

Reading a string column:

Code:

// Read column 3:
// 			id,  		0
// 			name,  		1
// 			ip,  		2
// 			auth,   	3 	<- Reads this one, and stores it in sAuth[]
// 			.... 		4

new sAuth[33];
SQL_ReadResult( hQuery, 3, sAuth, charsmax(sAuth) );

Side notes..

You should use charsmax() instead of hardcoding the max string length (127) which can lead to errors quite easily:

PHP Code:


			
SQL_ReadResult(sqlQuery, (44 + MaxStats), sInfo__128, 127);

// Do this instead -> 

SQL_ReadResult( sqlQuery, (44 + MaxStats), sInfo__128, charsmax(sInfo__128) );

I'm guessing your (44 + MaxStats) column contains a VARCHAR() string, which has a bunch of stats, which you're then parsing to integers, which again is a pretty wonky way of loading stats. This was probably a measure to lessen the amount of columns, but instead of doing that, I would just use a separate table. MySQL is literally made for this. I'd bomb this whole treacherous plugin and redo the database structure.

Instead of having 100s of columns in 1 single table for just stats loading, you could split your stats into separate tables. MySQL is a relative database and its not illegal to use the relations in between tables. But anyway I hope this brought at least some insight into your issue.

epQQ · 03-07-2024 , 15:10 Re: Database load stats error

Thx for reply @Rohanlogs;

I have fix this with separating the query from table "stats" and use "SQL_ReadResult(sqlQuery, MaxStats + numer);"

Solved.