1 reply to this topic

[marklcfc]

I have the following script written to display players appearances by every 50, so their first game appears first, then their 50th, etc, see the image below..

public function get_player_milestones( stdClass $player )
{
  $query = $this->dbo->prepare(
  'SELECT
  returnTable.*
  FROM ( SELECT @rownum := @rownum + 1 AS milestone_count, seasonstats.*
  FROM seasonstats
  WHERE id = ANY
  (
  SELECT matchid
  FROM seasonteamstats AS playergames, (SELECT @rownum:=0) variableInit
  WHERE firstname =  :firstname
  AND
  lastname = :lastname
  )
  ORDER BY date
  )
  AS
  returnTable
  WHERE returnTable.milestone_count = 1
  OR
  ( returnTable.milestone_count )
  MOD 50 = 0;
  ');

  $query->bindParam(':firstname', $player->firstname, PDO::PARAM_STR );
  $query->bindParam(':lastname', $player->lastname, PDO::PARAM_STR );
  $query->execute();

  return $query->fetchAll(PDO::FETCH_OBJ);
}

What I want to do now is display the games the player scored in. I have almost got it by adding

AND goals+penalties > 0

under lastname = :lastname. Problem is, its not taking into account when a player has scored 2, or more in a game. So basically, if a players scored 51 goals, the 50 is missing if they've scored more than 2 in one game. Hopefully someone understood all that

Attached Images

• 

[threetonesun]

I take it you're storing goals per game, not individual goals? It might be easier to create a table that just lists a player ID, game #, and an entry every time a goal is scored (and maybe a type, if you want to separate out standard goals from penalties). Then you could join that with the players table by ID, and limit to whatever you want (1,50,100, etc).

You could also return an array of all the games a player has scored in and run a loop that grabs the game name at 1 and 50.