GFZJJh4y

1id | country_id | name 
250       1         Premier League 
3     
4id | competition_id | name
5 70       50          2019 
6     
7id | season_id | name 
8 58       70      Regular Season 
9     
10id | round_id | home | away | result | datetime
11 44      58       22     87     1 - 0  2019-03-16:00:00 
12     
13$app->get('/country/get_countries/{date}', function (Request $request, Response $response, array $args)
14{
15  $start_date = $args["date"] . " 00:00";
16  $end_date = $args["date"] . " 23:59";
17
18  $sql = $this->db->query("SELECT n.* FROM country n
19    JOIN competition c ON c.country_id = n.id
20    JOIN competition_seasons s ON s.competition_id = c.id
21    JOIN competition_rounds r ON r.season_id = s.id
22    JOIN `match` m ON m.round_id = r.id
23    WHERE m.datetime BETWEEN '" . $start_date . "' AND '" . $end_date . "'
24    GROUP BY n.id");
25
26  $sql->execute();
27  $countries = $sql->fetchAll();
28  return $response->withJson($countries);
29}); 
30     
31CREATE TABLE IF NOT EXISTS `swp`.`competition` (
32  `id` INT NOT NULL,
33  `country_id` INT NULL,
34  `name` VARCHAR(255) NULL,
35  `category` INT NULL,
36  PRIMARY KEY (`id`),
37  INDEX `id_idx` (`country_id` ASC),
38  INDEX `FK_competition_types_competition_type_id_idx` (`category` ASC),
39  CONSTRAINT `FK_country_competition_country_id`
40    FOREIGN KEY (`country_id`)
41    REFERENCES `swp`.`country` (`id`)
42    ON DELETE NO ACTION
43    ON UPDATE NO ACTION,
44  CONSTRAINT `FK_competition_categories_competition_category_id`
45    FOREIGN KEY (`category`)
46    REFERENCES `swp`.`competition_categories` (`id`)
47    ON DELETE NO ACTION
48    ON UPDATE NO ACTION)
49ENGINE = InnoDB;
50
51
52CREATE TABLE IF NOT EXISTS `swp`.`competition_seasons` (
53  `id` INT NOT NULL AUTO_INCREMENT,
54  `competition_id` INT NOT NULL,
55  `season_id` INT NULL,
56  `name` VARCHAR(45) NOT NULL,
57  `update_at` DATETIME NULL,
58  PRIMARY KEY (`id`),
59  INDEX `FK_competition_competition_seasons_competition_id_idx` (`competition_id` ASC),
60  CONSTRAINT `FK_competition_competition_seasons_competition_id`
61    FOREIGN KEY (`competition_id`)
62    REFERENCES `swp`.`competition` (`id`)
63    ON DELETE NO ACTION
64    ON UPDATE NO ACTION)
65ENGINE = InnoDB;
66
67CREATE TABLE IF NOT EXISTS `swp`.`competition_rounds` (
68  `id` INT NOT NULL AUTO_INCREMENT,
69  `round_id` INT NULL,
70  `season_id` INT NOT NULL,
71  `name` VARCHAR(255) NULL,
72  PRIMARY KEY (`id`),
73  INDEX `FK_competition_seasons_competition_rounds_season_id_idx` (`season_id` ASC),
74  CONSTRAINT `FK_competition_seasons_competition_rounds_season_id`
75    FOREIGN KEY (`season_id`)
76    REFERENCES `swp`.`competition_seasons` (`id`)
77    ON DELETE NO ACTION
78    ON UPDATE NO ACTION)
79ENGINE = InnoDB;
80
81-- -----------------------------------------------------
82-- Table `swp`.`match`
83-- -----------------------------------------------------
84CREATE TABLE IF NOT EXISTS `swp`.`match` (
85  `id` INT NOT NULL,
86  `round_id` INT NOT NULL,
87  `group_id` INT NULL,
88  `datetime` DATETIME NULL,
89  `status` INT NULL,
90  `gameweek` INT NULL,
91  `home_team_id` INT NULL,
92  `home_team_half_time_score` INT NULL,
93  `home_team_score` INT NULL,
94  `home_extra_time` INT NULL,
95  `home_penalties` INT NULL,
96  `away_team_id` INT NULL,
97  `away_team_half_time_score` INT NULL,
98  `away_team_score` INT NULL,
99  `away_extra_time` INT NULL,
100  `away_penalties` INT NULL,
101  `venue_id` INT NULL,
102  `venue_attendance` INT NULL,
103  `aggregate_match_id` INT NULL,
104  PRIMARY KEY (`id`),
105  INDEX `home_team_id_idx` (`home_team_id` ASC),
106  INDEX `away_team_id_idx` (`away_team_id` ASC),
107  INDEX `venue_id_idx` (`venue_id` ASC),
108  INDEX `match_status_id_idx` (`status` ASC),
109  INDEX `FK_competition_rounds_match_round_id_idx` (`round_id` ASC),
110  INDEX `FK_match_match_aggregate_match_id_idx` (`aggregate_match_id` ASC),
111  INDEX `FK_competition_groups_match_group_id_idx` (`group_id` ASC),
112  CONSTRAINT `FK_team_match_home_team_id`
113    FOREIGN KEY (`home_team_id`)
114    REFERENCES `swp`.`team` (`id`)
115    ON DELETE NO ACTION
116    ON UPDATE NO ACTION,
117  CONSTRAINT `FK_team_match_away_team_id`
118    FOREIGN KEY (`away_team_id`)
119    REFERENCES `swp`.`team` (`id`)
120    ON DELETE NO ACTION
121    ON UPDATE NO ACTION,
122  CONSTRAINT `FK_venue_match_venue_id`
123    FOREIGN KEY (`venue_id`)
124    REFERENCES `swp`.`venue` (`id`)
125    ON DELETE NO ACTION
126    ON UPDATE NO ACTION,
127  CONSTRAINT `FK_match_status_match_status_id`
128    FOREIGN KEY (`status`)
129    REFERENCES `swp`.`match_status` (`id`)
130    ON DELETE NO ACTION
131    ON UPDATE NO ACTION,
132  CONSTRAINT `FK_competition_rounds_match_round_id`
133    FOREIGN KEY (`round_id`)
134    REFERENCES `swp`.`competition_rounds` (`id`)
135    ON DELETE NO ACTION
136    ON UPDATE NO ACTION,
137  CONSTRAINT `FK_match_match_aggregate_match_id`
138    FOREIGN KEY (`aggregate_match_id`)
139    REFERENCES `swp`.`match` (`id`)
140    ON DELETE NO ACTION
141    ON UPDATE NO ACTION,
142  CONSTRAINT `FK_competition_groups_match_group_id`
143    FOREIGN KEY (`group_id`)
144    REFERENCES `swp`.`competition_groups` (`id`)
145    ON DELETE NO ACTION
146    ON UPDATE NO ACTION)
147ENGINE = InnoDB; 
148     
149SELECT round_id, DATE("2019-03-18") FROM `match`