SitePoint Sponsor

User Tag List

Results 1 to 12 of 12
  1. #1
    SitePoint Enthusiast andygout's Avatar
    Join Date
    Jun 2012
    Location
    London, United Kingdom, United Kingdom
    Posts
    68
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Empty array at end of each foreach iteration

    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.

    PHP Code:
    $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'";
        }


  2. #2
    SitePoint Enthusiast
    Join Date
    Jul 2013
    Location
    Voorheesville NY USA
    Posts
    88
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    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.

  3. #3
    SitePoint Enthusiast andygout's Avatar
    Join Date
    Jun 2012
    Location
    London, United Kingdom, United Kingdom
    Posts
    68
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

    Thanks.

  4. #4
    From space with love silver trophy
    SpacePhoenix's Avatar
    Join Date
    May 2007
    Location
    Poole, UK
    Posts
    5,065
    Mentioned
    103 Post(s)
    Tagged
    0 Thread(s)
    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?

    btw, are you escaping the value for $player_id before letting it anywhere near the database? The value from the $player_id needs to be sanitized before being allowed anywhere near the database (either by use of the mysqli_real_escape_string() string function or more preferably by making use of prepared statements) otherwise your code will be vulnerable to an SQL Injection attack. All user submitted data no matter how it's being submitted (GET, POST or REQUEST arrays or a cookie) must always be considered unsafe untill it has been validated and sanitized.
    Community Team Advisor
    Forum Guidelines: Posting FAQ Signatures FAQ Self Promotion FAQ
    Help the Mods: What's Fluff? Report Fluff/Spam to a Moderator

  5. #5
    SitePoint Enthusiast andygout's Avatar
    Join Date
    Jun 2012
    Location
    London, United Kingdom, United Kingdom
    Posts
    68
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks SpacePhoenix.

    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).

  6. #6
    SitePoint Evangelist
    Join Date
    Aug 2006
    Location
    Nantwich, Cheshire
    Posts
    439
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    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:

    Code:
    $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.
    http://www.firenza.net - my homage to a car from the 1970s

  7. #7
    From space with love silver trophy
    SpacePhoenix's Avatar
    Join Date
    May 2007
    Location
    Poole, UK
    Posts
    5,065
    Mentioned
    103 Post(s)
    Tagged
    0 Thread(s)
    What's the end product of the code, eg a list of all teams (showing a count of the number of male and female players in each team)?
    Community Team Advisor
    Forum Guidelines: Posting FAQ Signatures FAQ Self Promotion FAQ
    Help the Mods: What's Fluff? Report Fluff/Spam to a Moderator

  8. #8
    SitePoint Evangelist
    Join Date
    Aug 2006
    Location
    Nantwich, Cheshire
    Posts
    439
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    End result surely is just to update the 'total male' and 'total female' counts on each team when a player record is changed?
    http://www.firenza.net - my homage to a car from the 1970s

  9. #9
    SitePoint Enthusiast andygout's Avatar
    Join Date
    Jun 2012
    Location
    London, United Kingdom, United Kingdom
    Posts
    68
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

    Thanks for all your help so far.

    Code:
    while ($row = mysqli_fetch_array($result)) 
        { 
            $player_counts[] = array('player_sex_male' => $row['player_sex_male'], 'player_sex_female' => $row['player_sex_female'], 'player_amount' => $row['player_amount']); 
    
            $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_amount']; 
                } 
                elseif ($player_count['player_sex_female'] == '1') 
                { 
                    $player_female_arr[] = $player_count['player_amount']; 
                } 
            } 
    
            $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'"; 
        }

  10. #10
    SitePoint Evangelist
    Join Date
    Aug 2006
    Location
    Nantwich, Cheshire
    Posts
    439
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    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

    Code:
    $psm += ($row['player_amount']) * $row['player_sex_male'];
    $psf += ($row['player_amount']) * $row['player_sex_female'];
    assuming that one player entry wouldn't store a different number of male and some females, i.e. that it is a number of either female or male players.

    But it doesn't answer your question. Reading about it elsewhere seems to suggest that it should erase the array as you expect.
    http://www.firenza.net - my homage to a car from the 1970s

  11. #11
    SitePoint Enthusiast andygout's Avatar
    Join Date
    Jun 2012
    Location
    London, United Kingdom, United Kingdom
    Posts
    68
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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!

  12. #12
    SitePoint Evangelist
    Join Date
    Aug 2006
    Location
    Nantwich, Cheshire
    Posts
    439
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Great news, glad it helped even if the real question still remains.
    http://www.firenza.net - my homage to a car from the 1970s


Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •