Take this problem using an example of teams and individual players (who can belong to multiple teams):-
Each player stores a boolean value of male (0/1) and female (0/1) to delineate its sex. When a team is edited, it counts the number of male and female members on its team and creates a respective total for both. Should an individual player be edited and have their sex changed (perhaps data was initially entered incorrectly), when updating I need to update the counts of all the player’s associated teams as well.
I’ve got this to work should the person only have one associated team, but for multiple teams the counts accumulate with each iteration of the foreach loop (i.e. the second team would have the sum of teams 1 and 2, the third team the sum of teams 1, 2 and 3, etc,).
Basically, I need to reset the ‘player_male_arr’ and ‘player_female_ arr’ arrays for each team (and the unset function doesn’t seem to be doing that or I am not using it in the right place).
Below is the player index code.
Thanks for any help.
$sql = "SELECT
teamid
FROM
teamplayer
WHERE playerid = '$player_id'"; //selects associated team ids of player
while ($row = mysqli_fetch_array($result))
{
$assoc_team_ids[] = $row['teamid'];
}
foreach($assoc_team_ids as $assoc_team_id)
{
$assoc_team_id = $assoc_team_id['teamid'];
$sql = "SELECT
player_sex_male,
player_sex_female,
FROM teamplayer
INNER JOIN player
ON playerid = player_id
WHERE teamid = '$assoc_team_id'"; //selects male/female data for associated teams
$result = mysqli_query($link, $sql);
while ($row = mysqli_fetch_array($result))
{
$player_counts[] = array('player_sex_male' => $row['player_sex_male'], 'player_sex_female' => $row['player_sex_female']);
$player_male_arr = array(); $player_female_arr = array();
foreach($player_counts as $player_count)
{
if ($player_count['player_sex_male'] == '1')
{
$player_male_arr[] = $player_count['player_sex_male'];
}
elseif ($player_count['player_sex_female'] == '1')
{
$player_female_arr[] = $player_count['player_sex_female'];
}
}
$player_male = array_sum($player_male_arr);
$player_female = array_sum($player_female_arr);
unset($player_male_arr); unset($player_female_arr);
$sql = "UPDATE team SET
player_male = '$player_male',
player_female = '$player_female',
WHERE team_id = '$assoc_team_id'";
}
}
Without examining your code at all I see that you have a db issue. You need to normalize it and store “player” attributes separately. If teams is one table, you store the player name there, but you store the player’s sex in a completely separate table. Then you won’t have this issue at all.
If player names are saved in the team table then this prevents them from being associated with multiple teams; surely they should be saved as individual entities as in the table layout I’m using:-
team
team_id / team_name / player_male / player_female (these last two fields contain the total number of players of each respective sex on the team, the issue at hand)
player
player_id / player_name / player_sex_male (BOOL) / player_sex_female (BOOL)
teamplayer
teamid / playerid
I don’t quite understand how saving the player’s sex in a separate table is going to solve this; can you explain further?
But the layout I’m using really is working fine and as far as I know follows best practices ; it really is just how I reset the arrays that I’m after.
You could store the player’s sex in one field, call that field something like “gender” or “player_gender”, that field would be a one character char field with “M” for male and “F” for female. Could the count of the number of male and female players in each team not be calculated each time you get a list of the teams from the database using a derived table?
I have considered using a derived table and applying a COUNT command to gather the results that way, but the example I’m using is actually a simplified version of what I actually need to do, which sometimes actually requires each player to represent a multiple amount (it makes sense in the context of what I’m doing but would take too long to explain; essentially player_sex_male and player_sex_female could represent any number rather than a boolean value; sorry - realize it was probably misleading to say the opposite in the earlier message).
I therefore need to pull out the info for each individual player, scrutinize it, and at the end apply the ‘array_sum’ function to the totals (rather than the ‘count’ function).
(and $player_id is sanitized earlier in the code; I just haven’t included it here).
Sorry to sitck an oar in here, and point me elsewhere if this is a basic question, but isn’t using an array quite a complex way of doing this? I’m thinking your loop should be more like:
$sql = "SELECT
teamid
FROM
teamplayer
WHERE playerid = '$player_id'"; //selects associated team ids of player
while ($row = mysqli_fetch_array($result))
{
$assoc_team_ids[] = $row['teamid'];
}
foreach($assoc_team_ids as $assoc_team_id)
{
$assoc_team_id = $assoc_team_id['teamid'];
$sql = "SELECT
player_sex_male,
player_sex_female,
FROM teamplayer
INNER JOIN player
ON playerid = player_id
WHERE teamid = '$assoc_team_id'"; //selects male/female data for associated teams
$result = mysqli_query($link, $sql);
$psm = 0;
$psf = 0;
while ($row = mysqli_fetch_array($result))
{
$psm += $row['player_sex_male'];
$psf += $row['player_sex_female'];
}
$sql = "UPDATE team SET
player_male = '$psm',
player_female = '$psf',
WHERE team_id = '$assoc_team_id'";
}
}
I can’t see why you build an array, then sum the values in the array, rather than just summing the values as you loop through the results. Again, sorry if dragging o/t based on my inexperience.
droopsnoot - correct: end result is exactly as you say.
I’ve not done a great job of explaining this, for which I apologise.
Let’s say each player also has a field ‘player_amount’: normally this will be ‘1’ for individuals but in the instance that a player represents a duo or trio of players it would contain ‘2’ and ‘3’ respectively, etc. (let’s not argue the logic of saving a collection of people individually for the time being as it does make sense in the context of this particular problem).
Therefore the amount being entered into the respective ‘player_male_arr’ and ‘player_female_arr’ arrays will not necessarily be ‘1’ each time so can’t just be incremented by 1 for each occurrence, hence the requirement to sum the values in the arrays using the ‘sum_array’ function (unless I can do it whilst cycling through?).
It’s just resetting the arrays to empty after looping through each team that is the problem I think.
Ah, I thought you meant that player_sex_male and/or player_sex_female would be something other than 1 or 0 in that case, hence me just adding the value of those fields to my total. In that case I’d just do
THAT’S IT - got it working! I’ve not used arithmetic operators within a loop before so this had not occurred to me.
I’m still wondering just why I can’t get the ‘unset’ function to work but I guess I’ll leave that until I really need it, as this is a far more efficient solution to the problem.
Thanks SO much, droopsnoot! You can’t believe just how nuts this has been driving me!