Player

SELECT mc.*, pl.*, tm.*, trn.*, sum(CASE WHEN ev_event_name = 'goal' THEN ev_value ELSE 0 END) as stat_goal, sum(CASE WHEN ev_event_name = 'goal' AND ev_event_det = 'p' THEN ev_value ELSE 0 END) as stat_goal_pen, sum(CASE WHEN ev_event_name = 'assist' THEN ev_value ELSE 0 END) as stat_assist, sum(CASE WHEN ev_event_name = 'goal' OR ev_event_name = 'assist' THEN ev_value ELSE 0 END) as stat_goal_assist, sum(CASE WHEN ev_event_name = 'missed_pen' THEN ev_value ELSE 0 END) as stat_missed_pen, sum(CASE WHEN ev_event_name = 'own_goal' THEN ev_value ELSE 0 END) as stat_own_goal, sum(CASE WHEN ev_event_name = 'card' AND (ev_event_det = 'y' or ev_event_det = 'yy') THEN ev_value ELSE 0 END) as stat_card_yellow, sum(CASE WHEN ev_event_name = 'card' AND (ev_event_det = 'r' or ev_event_det = 'yy') THEN ev_value ELSE 0 END) as stat_card_red FROM demo_ibpsports_events AS ev INNER JOIN demo_ibpsports_matches mc ON mc.match_id = ev.ev_match_id INNER JOIN demo_ibpsports_players pl ON pl.player_id = ev.ev_player_id INNER JOIN demo_ibpsports_tournaments as trn ON trn.tournament_id=mc.mc_tournament_id INNER JOIN demo_ibpsports_leagues as lg ON lg.league_id=trn.trn_league_id LEFT JOIN demo_ibpsports_teams tm ON (CASE WHEN ev_event_name <> 'own_goal' THEN tm.team_id = ev.ev_team_id WHEN ev_event_name = 'own_goal' AND lg_type <> 'national' THEN tm.team_id = pl.pl_team_id WHEN ev_event_name = 'own_goal' AND lg_type = 'national' THEN tm.team_id = pl.pl_national_id END) WHERE 1=1 AND ev.ev_player_id = 111 AND trn_season_id = 16 GROUP BY ev.ev_player_id , mc_tournament_id ORDER BY stat_goal desc

Array ( [0] => stdClass Object ( [match_id] => 1069 [mc_tournament_id] => 16 [mc_stage_id] => 44 [mc_group_id] => 179 [mc_playoff_id] => 0 [mc_friendly] => 0 [mc_status] => 1 [mc_name] => [mc_datetime] => 2024-09-15 15:30:00 [mc_match_day] => 6 [mc_team_home] => 68 [mc_team_away] => 40 [mc_score_home] => 0 [mc_score_away] => 1 [mc_score_det] => a:6:{s:17:"awarded_winner_id";s:0:"";s:9:"half_time";a:2:{s:4:"home";s:1:"0";s:4:"away";s:1:"0";}s:8:"overtime";a:2:{s:4:"home";s:0:"";s:4:"away";s:0:"";}s:3:"pen";a:2:{s:4:"home";s:0:"";s:4:"away";s:0:"";}s:11:"is_overtime";i:0;s:12:"is_penalties";i:0;} [mc_pts_home] => 0 [mc_pts_away] => 3 [mc_winner_id] => 40 [mc_loser_id] => 68 [mc_venue_id] => 28 [mc_neutral] => 0 [mc_attend] => 1087 [mc_referee] => 23 [mc_featured_img] => [mc_video] => [mc_cust] => [player_id] => 111 [pl_first_name] => [pl_last_name] => [pl_short_name] => М.Задерака [pl_sport] => soccer [pl_ctry] => ukr [pl_photo] => [pl_position] => [pl_birthday] => [pl_height] => 0 [pl_weight] => 0 [pl_team_id] => 40 [pl_national_id] => 0 [pl_cust] => [team_id] => 40 [tm_name] => Кривбас [tm_long_name] => Кривбас [tm_abbr_name] => КРИ [tm_city] => Кривий Ріг [tm_ctry] => ukr [tm_sport] => soccer [tm_logo] => 748 [tm_kit] => [tm_colors] => a:2:{s:4:"main";s:7:"#ff0000";s:4:"text";s:7:"#ffffff";} [tm_coach_id] => 10 [tm_venue_id] => 21 [tm_is_national] => 0 [tm_address] => [tm_website] => fckryvbas.com [tm_founded] => 2020 [tm_president] => Костянтин Караманіц [tm_profile] => [tm_wp_tag] => [tm_cust] => [tournament_id] => 16 [trn_league_id] => 16 [trn_season_id] => 16 [trn_name] => VBET Ліга [trn_status] => active [trn_logo] => 713 [trn_points_rules] => a:7:{s:6:"forwin";s:1:"3";s:7:"fordraw";s:1:"1";s:7:"forloss";i:0;s:9:"forwin_ot";s:1:"0";s:10:"forloss_ot";s:1:"0";s:11:"is_overtime";i:0;s:9:"is_manual";i:0;} [trn_ranking_rules] => a:6:{s:9:"automatic";s:1:"1";s:4:"main";s:3:"pts";s:10:"criteria-2";s:10:"goals_diff";s:10:"criteria-3";s:12:"goals_scored";s:10:"criteria-4";s:9:"win_games";s:10:"criteria-5";s:0:"";} [stat_goal] => 1 [stat_goal_pen] => 0 [stat_assist] => 0 [stat_goal_assist] => 1 [stat_missed_pen] => 0 [stat_own_goal] => 0 [stat_card_yellow] => 0 [stat_card_red] => 0 [tm_logo_thumb] => https://galsports.spy-football.com/wp-content/uploads/2024/07/kryvbas.png [pl_photo_thumb] => https://galsports.spy-football.com/wp-content/plugins/ibpsports/admin/img/no-image.jpg ) )