I might be overreacting but I am currently working on a PHP script to delete those db-entries completely (with score-updating, pick-up-deleting, updating totals etc.). Well.. lets see how complete it will be.
This will take some time but i will post it here if i get it done
Errrm.. perhaps you got any idea on the column 'ge_event' in the table 'gevents'?
for what does the value in that column stand for?
EDIT:
I got some code to show off now. Dont get confused by table names.. I used 'mkcs_ut' as table-pre-tag.
I think the script should be called, if someone choses the 'UTstatsDB' menu button of our homepage.
Here is the code (english comments for better understandig)
Code: Select all
//get all matches (ID of the match and ID of the map that was played) with no kills
$sql_get_matches = "SELECT mat.gm_num AS match_id, maps.mp_num AS map_id
FROM mkcs_ut_matches mat
INNER JOIN mkcs_ut_maps maps ON mat.gm_map = maps.mp_num
WHERE mat.gm_kills = '0'";
$sql_get_matches_query = mysql_query($sql_get_matches);
while ($matches = mysql_fetch_assoc($sql_get_matches_query))
{
//empty the match id variable
$match_id = "";
//set the match id variable
$match_id = $matches['match_id'];
//empty the map id variable
$map_id = "";
//set the map id variable
$map_id = $matches['map_id'];
//get all players (both IDs of them) who played the match
$sql_get_players = "SELECT gp_num AS player_game_id, gp_pnum AS player_true_id
FROM mkcs_ut_gplayers
WHERE gp_match = '$match_id'";
$sql_get_players_query = mysql_query($sql_get_players);
while ($players = mysql_fetch_assoc($sql_get_players_query))
{
//empty the player game id variable
$players_game_id = "";
//set the player game id variable
$players_game_id = $players['player_game_id'];
//empty the player true id variable
$players_true_id = "";
//set the player true id variable
$players_true_id = $players['player_true_id'];
//empty the overall score variable
$player_score_in_the_match = "";
//get the overall score of players who played the match
$sql_get_player_match_score = "SELECT sum(gs_score) AS player_game_score
FROM mkcs_ut_gscores
WHERE gs_match = '$match_id'
AND gs_player = '$players_game_id'";
$sql_get_player_match_score_query = mysql_query($sql_get_player_match_score);
while ($player_match_score = mysql_fetch_assoc($sql_get_player_match_score_query))
{
//set the overall score variable
$player_score_in_the_match = $player_match_score['player_game_score'];
}
//substract the overall score of players who played the match in the table 'mkcs_ut_players'
$sql_update_player_score_players = "UPDATE mkcs_ut_players
SET plr_score = plr_score - '$player_score_in_the_match'
WHERE pnum = '$players_true_id'";
$sql_update_player_score_players_query = mysql_query($sql_update_player_score_players);
//substract the overall score of players who played the match in the table 'mkcs_ut_playersgt'
$sql_update_player_score_playersgt = "UPDATE mkcs_ut_playersgt
SET gt_score = gt_score - '$player_score_in_the_match'
WHERE gt_pnum = '$players_true_id'";
$sql_update_player_score_playersgt_query = mysql_query($sql_update_player_score_playersgt);
//substract the overall score of players who played the match in the table 'mkcs_ut_totals'
$sql_update_player_score_totals = "UPDATE mkcs_ut_totals
SET tl_score = tl_score - '$player_score_in_the_match'";
$sql_update_player_score_totals_query = mysql_query($sql_update_player_score_totals);
//substract the overall score of players who played the match in the table 'mkcs_ut_maps'
$sql_update_player_score_maps = "UPDATE mkcs_ut_maps
SET mp_score = mp_score - '$player_score_in_the_match'
WHERE mp_num = '$map_id'";
$sql_update_player_score_maps_query = mysql_query($sql_update_player_score_maps);
}
/*
* substract the match in the recently updated tables
*/
//substract the match in the table 'mkcs_ut_players'
$sql_update_player_match_players = "UPDATE mkcs_ut_players
SET plr_matches = plr_matches - 1
WHERE pnum = '$players_true_id'";
$sql_update_player_match_players_query = mysql_query($sql_update_player_match_players);
//substract the match in the table 'mkcs_ut_playersgt'
$sql_update_player_match_playersgt = "UPDATE mkcs_ut_playersgt
SET gt_matches = gt_matches - 1
WHERE gt_pnum = '$players_true_id'";
$sql_update_player_match_playersgt_query = mysql_query($sql_update_player_match_playersgt);
//substract the match in the table 'mkcs_ut_totals'
$sql_update_player_match_totals = "UPDATE mkcs_ut_totals
SET tl_matches = tl_matches - 1";
$sql_update_player_match_totals_query = mysql_query($sql_update_player_match_totals);
//substract the match in the table 'mkcs_ut_maps'
$sql_update_player_match_maps = "UPDATE mkcs_ut_maps
SET mp_matches = mp_matches - 1
WHERE mp_num = '$map_id'";
$sql_update_player_match_maps_query = mysql_query($sql_update_player_match_maps);
//delete all data from the following tables that refer to the match
$sql_delete1 = "DELETE FROM mkcs_ut_connections WHERE cn_match = '$match_id'";
$sql_delete1_query = mysql_query($sql_delete1);
$sql_delete2 = "DELETE FROM mkcs_ut_gbots WHERE gb_match = '$match_id'";
$sql_delete2_query = mysql_query($sql_delete2);
$sql_delete3 = "DELETE FROM mkcs_ut_gchat WHERE gc_match = '$match_id'";
$sql_delete3_query = mysql_query($sql_delete3);
$sql_delete4 = "DELETE FROM mkcs_ut_gevents WHERE ge_match = '$match_id'";
$sql_delete4_query = mysql_query($sql_delete4);
$sql_delete5 = "DELETE FROM mkcs_ut_gitems WHERE gi_match = '$match_id'";
$sql_delete5_query = mysql_query($sql_delete5);
$sql_delete6 = "DELETE FROM mkcs_ut_gkills WHERE gk_match = '$match_id'";
$sql_delete6_query = mysql_query($sql_delete6);
$sql_delete7 = "DELETE FROM mkcs_ut_gplayers WHERE gp_match = '$match_id'";
$sql_delete7_query = mysql_query($sql_delete7);
$sql_delete8 = "DELETE FROM mkcs_ut_gscores WHERE gs_match = '$match_id'";
$sql_delete8_query = mysql_query($sql_delete8);
$sql_delete9 = "DELETE FROM mkcs_ut_gwaccuracy WHERE gwa_match = '$match_id'";
$sql_delete9_query = mysql_query($sql_delete9);
$sql_delete10 = "DELETE FROM mkcs_ut_tkills WHERE tk_match = '$match_id'";
$sql_delete10_query = mysql_query($sql_delete10);
//delete the actual match
$sql_delete_match = "DELETE FROM mkcs_ut_matches WHERE gm_num = '$match_id'";
$sql_delete_match_query = mysql_query($sql_delete_match);
}
In the end all queries get checked if they caused errors.
If they caused some, those errors will be inserted into an 'error-tracking-table' and the script will call the homepage
If everything went correct, script will call the index-page of UTstatsDB
One improvement would be to calculate all weapon-pickups and delete those as well..
Im also thinking about running the script once and after that creating a view at the admin section of UTstatsDB with a drop down menu for the matches so i can delete them one by one easily if necessary
Perhaps you got any suggestions on my thoughts here overall?
Perhaps i am doing total bullshit?
Any feedback is appreciated!
