| Fabregas21 |
08-27-2018 13:59 |
[Database - Server Connection] - Optimization of plugin
Hello,
I need help with optimization a part of my plugin using concerning rank system.
After playing several maps, I'm getting this error: "User xxx already has more than 'max_user_connections' active connections". Of course I know what it is and the limit of this variable is set to 50. The problem is that hosting does not allow to increase the value of this parameter and instructed me to optimize the plugin.
How can this plugin be improved so that connections to the database are closed or to maintain the continuity of the connection? What should be changed or removed in the plugin?
PHP Code:
public load_data(id)
{
new szError[512], ErrorCode
new szName[32]
get_user_name(id, szName, charsmax( szName ))
new Handle:SqlConnection = SQL_Connect(g_SqlTuple,ErrorCode,szError,511)
SQL_QuoteString(SqlConnection, szName, 32, szName);
new Handle:Query = SQL_PrepareQuery(SqlConnection, "SELECT `id` FROM `stats` WHERE `name`='%s';", szName)
if(!SQL_Execute(Query)) {
SQL_QueryError(Query,szError,511);
log_amx(szError);
return ;
}
if(SQL_NumResults(Query) > 0 ) {
next_load_data(id, "", 0);
}
else {
register_player(id,"");
}
SQL_FreeHandle(Query);
SQL_FreeHandle(SqlConnection);
}
public next_load_data(id,data[],len)
{
new szName[33];
get_user_name(id, szName, charsmax( szName ));
new szError[512], ErrorCode;
new Handle:SqlConnection = SQL_Connect(g_SqlTuple,ErrorCode,szError,511)
SQL_QuoteString(SqlConnection, szName, 32, szName);
if(SqlConnection == Empty_Handle)
{
log_amx(szError);
return ;
}
new Handle:Query = SQL_PrepareQuery(SqlConnection, "SELECT * FROM `stats` WHERE `name`='%s';", szName)
if(!SQL_Execute(Query))
{
SQL_QueryError(Query,szError,511)
log_amx(szError)
}
if( SQL_NumResults(Query) > 0)
{
UserData[id][gPoints]= SQL_ReadResult(Query, 2);
UserData[id][gGoals]= SQL_ReadResult(Query, 3);
UserData[id][gAssists]= SQL_ReadResult(Query, 4);
UserData[id][gSteals]= SQL_ReadResult(Query, 5);
UserData[id][gStealsRv]= SQL_ReadResult(Query, 6);
UserData[id][gDisarm]= SQL_ReadResult(Query, 7);
UserData[id][gDisarmRv]= SQL_ReadResult(Query, 8);
UserData[id][gBKills]= SQL_ReadResult(Query, 9);
UserData[id][gBKillsRv]= SQL_ReadResult(Query, 10);
UserData[id][gOffside]= SQL_ReadResult(Query, 11);
UserData[id][gFoul]= SQL_ReadResult(Query, 12);
UserData[id][gWarns]= SQL_ReadResult(Query, 13);
}
SQL_FreeHandle(Query);
SQL_FreeHandle(SqlConnection);
}
public register_player(id, data[])
{
new szName[33];
get_user_name(id, szName, charsmax( szName ));
new szError[512], ErrorCode;
new Handle:SqlConnection = SQL_Connect(g_SqlTuple, ErrorCode, szError,511)
SQL_QuoteString(SqlConnection, szName, 32, szName);
if(SqlConnection == Empty_Handle)
{
log_amx(szError);
return ;
}
new Handle:Query = SQL_PrepareQuery(SqlConnection, "INSERT INTO `stats` (`name`, `points`, `goals`, `assists`, `steals`, `stealsrv`, `disarm`, `disarmrv`, `bkills`, `bkillsrv`, `offside`, `foul`, `warns`) VALUES ('%s', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0');", szName);
if(!SQL_Execute(Query))
{
SQL_QueryError(Query,szError,511)
log_amx(szError)
}
SQL_FreeHandle(Query);
SQL_FreeHandle(SqlConnection);
UserData[id][gPoints]= 0;
UserData[id][gGoals]= 0;
UserData[id][gSteals]= 0;
UserData[id][gStealsRv]= 0;
UserData[id][gAssists]= 0;
UserData[id][gDisarm]= 0;
UserData[id][gDisarmRv]= 0;
UserData[id][gBKills]= 0;
UserData[id][gBKillsRv]= 0;
UserData[id][gOffside]= 0;
UserData[id][gFoul]= 0;
UserData[id][gWarns]= 0;
}
public save_usr(id)
{
new szName[33];
get_user_name(id, szName, charsmax( szName ));
new szError[512], ErrorCode;
new Handle:szUpdate;
new Handle:SqlConnection = SQL_Connect(g_SqlTuple, ErrorCode, szError, 511)
SQL_QuoteString(SqlConnection, szName, 32, szName)
if(SqlConnection == Empty_Handle)
{
log_amx(szError);
return ;
}
szUpdate = SQL_PrepareQuery(SqlConnection, "UPDATE `stats` SET `points`='%d', `goals`='%d', `assists`='%d', `steals`='%d', `stealsrv`='%d', `disarm`='%d', `disarmrv`='%d', `bkills`='%d', `bkillsrv`='%d', `offside`='%d', `foul`='%d', `warns`='%d' WHERE `stats`.`name`='%s';",
UserData[id][gPoints], UserData[id][gGoals], UserData[id][gAssists], UserData[id][gSteals], UserData[id][gStealsRv], UserData[id][gDisarm], UserData[id][gDisarmRv], UserData[id][gBKills], UserData[id][gBKillsRv], UserData[id][gOffside], UserData[id][gFoul], UserData[id][gWarns], szName)
if(!SQL_Execute(szUpdate))
{
SQL_QueryError(szUpdate, szError, charsmax( szError ))
set_fail_state( szError )
}
}
public top_players(id){
new szError[512], ErrorCode;
new num = 0;
new name[10][32], points[10][11], goals[10][11], assists[10][11], steals[10][11], stealsrv[10][11], disarm[10][11], disarmrv[10][11], bkills[10][11], bkillsrv[10][11], offside[10][11], foul[10][11];
new Handle:SqlConnection = SQL_Connect(g_SqlTuple, ErrorCode, szError, 511)
new Handle:Query = SQL_PrepareQuery(SqlConnection, "SELECT * FROM `stats` ORDER BY `points` DESC, `goals` DESC")
SQL_Execute(Query)
while(SQL_MoreResults(Query) && num <= 9){
SQL_ReadResult(Query, 1, name[num], 31);
SQL_ReadResult(Query, 2, points[num], 10);
SQL_ReadResult(Query, 3, goals[num], 10);
SQL_ReadResult(Query, 4, assists[num], 10);
SQL_ReadResult(Query, 5, steals[num], 10);
SQL_ReadResult(Query, 6, stealsrv[num], 10);
SQL_ReadResult(Query, 7, disarm[num], 10);
SQL_ReadResult(Query, 8, disarmrv[num], 10);
SQL_ReadResult(Query, 9, bkills[num], 10);
SQL_ReadResult(Query, 10, bkillsrv[num], 10);
SQL_ReadResult(Query, 11, offside[num], 10);
SQL_ReadResult(Query, 12, foul[num], 10);
num++
SQL_NextRow(Query)
}
new motd[2001], iLen;
iLen = format(motd, sizeof motd - 1, "<body bgcolor=#000000><font color=#98f5ff><pre>");
iLen += format(motd[iLen], (sizeof motd - 1) - iLen, "<center><h2>---- SoccerJam Top 10 ----</h2></center>^n^n");
iLen += format(motd[iLen], (sizeof motd - 1) - iLen, "<b><U>%2s %-22.22s %8s %6s %6s %6s %6s %6s %6s %6s %6s %6s %6s</U></b>^n", "#", "Nick", "Points", "Goals", "Assists", "Steals", "StealsRv", "Disarm", "DisarmRv", "BKills", "BKillsRv", "Offsides", "Fouls");
iLen += format(motd[iLen], (sizeof motd - 1) - iLen,"1 %-22.22s %6i %7i %6i %7i %5i %8i %7i %7i %6i %6i %6i^n", name[0], str_to_num(points[0]), str_to_num(goals[0]), str_to_num(assists[0]), str_to_num(steals[0]), str_to_num(stealsrv[0]), str_to_num(disarm[0]), str_to_num(disarmrv[0]), str_to_num(bkills[0]), str_to_num(bkillsrv[0]), str_to_num(offside[0]), str_to_num(foul[0]))
iLen += format(motd[iLen], (sizeof motd - 1) - iLen,"2 %-22.22s %6i %7i %6i %7i %5i %8i %7i %7i %6i %6i %6i^n", name[1], str_to_num(points[1]), str_to_num(goals[1]), str_to_num(assists[1]), str_to_num(steals[1]), str_to_num(stealsrv[1]), str_to_num(disarm[1]), str_to_num(disarmrv[1]), str_to_num(bkills[1]), str_to_num(bkillsrv[1]), str_to_num(offside[1]), str_to_num(foul[1]))
iLen += format(motd[iLen], (sizeof motd - 1) - iLen,"3 %-22.22s %6i %7i %6i %7i %5i %8i %7i %7i %6i %6i %6i^n", name[2], str_to_num(points[2]), str_to_num(goals[2]), str_to_num(assists[2]), str_to_num(steals[2]), str_to_num(stealsrv[2]), str_to_num(disarm[2]), str_to_num(disarmrv[2]), str_to_num(bkills[2]), str_to_num(bkillsrv[2]), str_to_num(offside[2]), str_to_num(foul[2]))
iLen += format(motd[iLen], (sizeof motd - 1) - iLen,"4 %-22.22s %6i %7i %6i %7i %5i %8i %7i %7i %6i %6i %6i^n", name[3], str_to_num(points[3]), str_to_num(goals[3]), str_to_num(assists[3]), str_to_num(steals[3]), str_to_num(stealsrv[3]), str_to_num(disarm[3]), str_to_num(disarmrv[3]), str_to_num(bkills[3]), str_to_num(bkillsrv[3]), str_to_num(offside[3]), str_to_num(foul[3]))
iLen += format(motd[iLen], (sizeof motd - 1) - iLen,"5 %-22.22s %6i %7i %6i %7i %5i %8i %7i %7i %6i %6i %6i^n", name[4], str_to_num(points[4]), str_to_num(goals[4]), str_to_num(assists[4]), str_to_num(steals[4]), str_to_num(stealsrv[4]), str_to_num(disarm[4]), str_to_num(disarmrv[4]), str_to_num(bkills[4]), str_to_num(bkillsrv[4]), str_to_num(offside[4]), str_to_num(foul[4]))
iLen += format(motd[iLen], (sizeof motd - 1) - iLen,"6 %-22.22s %6i %7i %6i %7i %5i %8i %7i %7i %6i %6i %6i^n", name[5], str_to_num(points[5]), str_to_num(goals[5]), str_to_num(assists[5]), str_to_num(steals[5]), str_to_num(stealsrv[5]), str_to_num(disarm[5]), str_to_num(disarmrv[5]), str_to_num(bkills[5]), str_to_num(bkillsrv[5]), str_to_num(offside[5]), str_to_num(foul[5]))
iLen += format(motd[iLen], (sizeof motd - 1) - iLen,"7 %-22.22s %6i %7i %6i %7i %5i %8i %7i %7i %6i %6i %6i^n", name[6], str_to_num(points[6]), str_to_num(goals[6]), str_to_num(assists[6]), str_to_num(steals[6]), str_to_num(stealsrv[6]), str_to_num(disarm[6]), str_to_num(disarmrv[6]), str_to_num(bkills[6]), str_to_num(bkillsrv[6]), str_to_num(offside[6]), str_to_num(foul[6]))
iLen += format(motd[iLen], (sizeof motd - 1) - iLen,"8 %-22.22s %6i %7i %6i %7i %5i %8i %7i %7i %6i %6i %6i^n", name[7], str_to_num(points[7]), str_to_num(goals[7]), str_to_num(assists[7]), str_to_num(steals[7]), str_to_num(stealsrv[7]), str_to_num(disarm[7]), str_to_num(disarmrv[7]), str_to_num(bkills[7]), str_to_num(bkillsrv[7]), str_to_num(offside[7]), str_to_num(foul[7]))
iLen += format(motd[iLen], (sizeof motd - 1) - iLen,"9 %-22.22s %6i %7i %6i %7i %5i %8i %7i %7i %6i %6i %6i^n", name[8], str_to_num(points[8]), str_to_num(goals[8]), str_to_num(assists[8]), str_to_num(steals[8]), str_to_num(stealsrv[8]), str_to_num(disarm[8]), str_to_num(disarmrv[8]), str_to_num(bkills[8]), str_to_num(bkillsrv[8]), str_to_num(offside[8]), str_to_num(foul[8]))
iLen += format(motd[iLen], (sizeof motd - 1) - iLen,"10 %-22.22s %6i %7i %6i %7i %5i %8i %7i %7i %6i %6i %6i^n", name[9], str_to_num(points[9]), str_to_num(goals[9]), str_to_num(assists[9]), str_to_num(steals[9]), str_to_num(stealsrv[9]), str_to_num(disarm[9]), str_to_num(disarmrv[9]), str_to_num(bkills[9]), str_to_num(bkillsrv[9]), str_to_num(offside[9]), str_to_num(foul[9]))
show_motd(id, motd, "Top 10");
}
public rank_players(id){
new szError[512], ErrorCode;
new num = 1;
new name[32], points[11], notes;
new szName[32];
get_user_name(id, szName, 31);
new Handle:SqlConnection = SQL_Connect(g_SqlTuple, ErrorCode, szError, 511);
new Handle:Query = SQL_PrepareQuery(SqlConnection, "SELECT * FROM `stats` ORDER BY `points` DESC, `goals` DESC");
SQL_Execute(Query)
notes = SQL_NumResults(Query)
while(SQL_MoreResults(Query))
{
SQL_ReadResult(Query, 1, name, 31)
if(equal(name, szName))
{
SQL_ReadResult(Query, 2, points, 10)
break
}else{
num++
SQL_NextRow(Query)
}
}
PrintChatColor(id, _, "%L", LANG_PLAYER, "SJ_RANK", num, notes, str_to_num(points))
}
public rankstats_players(id){
new motd[1501],iLen;
new szName[33];
get_user_name(id,szName,32);
iLen = format(motd, sizeof motd - 1,"<body bgcolor=#000000><font color=#98f5ff><pre>");
iLen += format(motd[iLen], (sizeof motd - 1) - iLen,"<center><h2>---- SoccerJam Stats ----</h2></center>^n^n");
iLen += format(motd[iLen], (sizeof motd - 1) - iLen,"<center><b>%s</b></center>^n", szName);
iLen += format(motd[iLen], (sizeof motd - 1) - iLen,"<font color=#fff000><b>%-22.22s</b>: %10i^n", "Points", UserData[id][gPoints]);
iLen += format(motd[iLen], (sizeof motd - 1) - iLen,"<b>%-22.22s</b>: %10i^n", "Goals", UserData[id][gGoals]);
iLen += format(motd[iLen], (sizeof motd - 1) - iLen,"<b>%-22.22s</b>: %10i^n", "Assists", UserData[id][gAssists]);
iLen += format(motd[iLen], (sizeof motd - 1) - iLen,"<b>%-22.22s</b>: %10i^n", "Steals", UserData[id][gSteals]);
iLen += format(motd[iLen], (sizeof motd - 1) - iLen,"<b>%-22.22s</b>: %10i^n", "StealsRv", UserData[id][gStealsRv]);
iLen += format(motd[iLen], (sizeof motd - 1) - iLen,"<b>%-22.22s</b>: %10i^n", "Disarm", UserData[id][gDisarm]);
iLen += format(motd[iLen], (sizeof motd - 1) - iLen,"<b>%-22.22s</b>: %10i^n", "DisarmRv", UserData[id][gDisarmRv]);
iLen += format(motd[iLen], (sizeof motd - 1) - iLen,"<b>%-22.22s</b>: %10i^n", "BKills", UserData[id][gBKills]);
iLen += format(motd[iLen], (sizeof motd - 1) - iLen,"<b>%-22.22s</b>: %10i^n", "BKillsRv", UserData[id][gBKillsRv]);
iLen += format(motd[iLen], (sizeof motd - 1) - iLen,"<b>%-22.22s</b>: %10i^n", "Offsides", UserData[id][gOffside]);
iLen += format(motd[iLen], (sizeof motd - 1) - iLen,"<b>%-22.22s</b>: %10i^n^n", "Fouls", UserData[id][gFoul]);
show_motd(id, motd, "Stats");
return PLUGIN_HANDLED
}
public plugin_end() {
if(g_SqlTuple != Empty_Handle)
SQL_FreeHandle(g_SqlTuple);
}
public fw_ServerDown()
{
for(new i = 0; i <= MaxPlayers; i++)
{
if(is_user_connected(i))
save_usr(i)
}
}
public StatsEnable() {
new suma_player = get_playersnum()
if(suma_player >= 6) {
stats_enable = 1
}
else {
stats_enable = 0
}
}
|
