MySQL results - create array

[nzkiwi80]

I’m looping through a MySQL result. The result is a series of football games played by a particular person.

On each game, I want to do a lookup to see what ‘season’ it was in.

I want to group results by team and season.

For example, if a reason was in 2002-2023 I want all games played for team 76 collated together. Showing how many games they played and how many goals they scored.

I can’t do this in MySQL as some games are played for under multiple competitions and are counted twice. So I’m looking to loop through my game list by playerId and create a row for each team and season.

Assuming making that an array and adding to it?

Question is how?

while ($row = $result->fetch_assoc()) {
   $teamId = $row['teamId'];
   $season = getSeason($row['gameId']);

// if season and teamId array row exists add goals and game to appearance else create new one?
}

[NogDog]

nzkiwi80 I can’t do this in MySQL [citation needed]

I.e.: without knowing how your data is organized and what you're exactly trying to do, I'm not sure I could rule out handling this in the SQL?

Anyway, assuming I'm correctly guessing what you want here, maybe you just structure the array to use the combined game ID and team ID as the key, such that you don't have to worry about checking if any pair exists:

while ($row = $result->fetch_assoc()) {
    $results["{$row['teamId']}-{$row['gameId']}"] = $row['goals'];
}
$total_goals = array_sum($results);

[nzkiwi80]

NogDog

I have a ""game"" table, with match_data joining it (this holds who scored etc), I then join it to round_matches, then to rounds and finally to competitions. Which is where a 'season' is defined.

When a game is inside 2 competitions it gets counted twice as soon as a join to a competitions table. So I'm going to just loop though my games and create an array for each game and place it inside a 'season' array which I create on each loop when new.

If a competition is deemed in two different seasons, it'll only count in one and set a flag for a note.

From your one below, I actually need, teamId and seasonName plus my variables. Then add to it if that array exists. If not create one, then have an array of arrays?

Would that work?