<?php
// Using MySQLi
$sql = "SELECT g.game_number, ht.name AS home_team, at.name AS away_team
FROM Gamelog g
JOIN Teams ht ON g.home_team_id = ht.id
JOIN Teams at ON g.away_team_id = at.id";
$result = $mysqli->query($sql);
while ($row = $result->fetch_assoc()) {
echo "game " . $row["game_number"] . ": " . $row["home_team"] . " vs " . $row["away_team"] . "\n";
$sql2 = "SELECT t.name AS team_name
FROM Goals g
JOIN Teams t ON g.team_id_goal = t.id
WHERE g.game_number_id = ?";
$stmt2 = $mysqli->prepare($sql2);
$stmt2->bind_param("i", $row["game_number"]);
$stmt2->execute();
$result2 = $stmt2->get_result();
while ($row2 = $result2->fetch_assoc()) {
echo "goals in game " . $row["game_number"] . ": " . $row2["team_name"] . " scores\n";
}
}
?>