英文:
Multiple Joins Duplicating Rows from Other Tables
问题
I understand your request for translation, so I'll provide translations for the relevant parts of your text. Here are the translated portions:
"我正在尝试连接多个表，但某些行会出现'重复'。这是因为我要连接的其中一个表比其他表多了几行（'Goals'表）。
例如，这是我要连接的三个表。'gamelog'（表示所有比赛），'teams'（表示所有球队）和'goals'（表示每场比赛中进球的情况）："
"Gamelog" -> "比赛记录"
"Teams" -> "球队"
"Goals" -> "进球"
"When I join 'teams' and 'gamelog', it works great." -> "当我连接 '球队' 和 '比赛记录' 时，一切运行良好。"
"The problem is when I add the third table called 'goals'." -> "问题出现在我添加第三个名为 '进球' 的表时。"
"I instead want it to look like this:" -> "我希望它看起来像这样："
"How can I make this possible?" -> "我该如何做才能实现这一目标？"
"Do I need to use a GROUP BY?" -> "我需要使用 GROUP BY 吗？"
"I tried adding that into my code but my syntax must be off." -> "我尝试将它添加到我的代码中，但语法可能有问题。"
"It may also have something to do with how my foreach loops are set up?" -> "这可能还与我的 foreach 循环设置有关？"
"I even tried making two separate queries." -> "我甚至尝试进行两个单独的查询。"
"Really stumped by this." -> "这真的让我很困惑。"
I hope this helps with your translation needs. If you have any more specific questions or need further assistance, please feel free to ask.
英文:
I am trying to join multiple tables, however some rows are 'duplicating'. This is because one of the tables I am joining has more rows than the others (the 'Goals' table).
For exmaple, here are my three tables i am joining. The 'gamelog' (represents all the games played), 'teams' (representing all teams) and 'goals' (representing all goals that were scored in each game):
Gamelog
| id | game_number | home_team_id | away_team_id |
|---|---|---|---|
| 1 | 1 | 1 | 2 |
| 2 | 2 | 3 | 4 |
Teams
| id | name |
|---|---|
| 1 | MTL |
| 2 | BOS |
| 3 | CGY |
| 4 | EDM |
Goals
| id | game_number_id | team_id_goal |
|---|---|---|
| 1 | 1 | 2 |
| 2 | 1 | 2 |
| 3 | 1 | 1 |
| 4 | 2 | 4 |
| 5 | 2 | 4 |
When I join 'teams' and 'gamelog', it works great. I am able to display the team name instead of the id number. And it only displays one row per game. Here is my query prior to adding the third table 'goals':
Example A
$sql = "SELECT gamelog.game_number, gamelog.home_team_id, gamelog.away_team_id,
t1.name AS hometeam, t2.name AS awayteam";
FROM gamelog
JOIN teams AS t1 ON gamelog.home_team_id=t1.id
JOIN teams AS t2 ON gamelog.away_team_id=t2.id
$statement = $pdo->query($sql);
$results = $statement->fetchAll();
<?php foreach($results as $result) { ?>
Game Number: <?=$result['game_number'];?>
<?=$result['hometeam'].' VERSUS '.$result['awayteam']; ?>
<?php } ?>
Result:
Game 1: MTL vs BOS
Game 2: EDM vs CGY
The problem is when I add the third table called 'goals'. As you can see there are three goals scored in game #1 between MTL (team_id = 1) and BOS(team_id = 2). And two goals scored in game #2 between CGY (team_id = 3) and EDM (team_id=4)/ When I join this, it duplicates everything in gamelog by amount of goals. So game #1 is duplicated x3, and game #2 is duplicated x2.
Here is the updated query with the third table. Changes are inside the double asterisks:
Example B
$sql = "SELECT gamelog.game_number, gamelog.home_team_id, gamelog.away_team_id,
t1.name AS hometeam, t2.name AS awayteam,
**goals.game_number_id**";
FROM gamelog
JOIN teams AS t1 ON gamelog.home_team_id=t1.id
JOIN teams AS t2 ON gamelog.away_team_id=t2.id
**JOIN goals ON gamelog.game_number = goals.game_number_id**
$statement = $pdo->query($sql);
$results = $statement->fetchAll();
<?php foreach($results as $result) { ?>
Game Number: <?=$result['game_number'];?>
<br>
<?=$result['hometeam'].' VERSUS '.$result['awayteam']; ?>
<br>
Which team scored the goals: <?=$result['team_id_goal']; ?>
<br>
<?php } ?>
The end result of this would have three rows under Game #1 like so:
game 1: MTL vs BOS
game 1: MTL vs BOS
game 1: MTL vs BOS
goals in game 1: MTL scores
goals in game 1: MTL scores
goals in game 1: BOS scores
game 2: CGY vs EDM
game 2 CGY vs EDM
goals in game 2: EDM scores
goals in game 2: EDM scores
I instead want it to look like this:
game 1: MTL vs BOS
goals in game 1: MTL scores
goals in game 1: MTL scores
goals in game 1: BOS scores
game 2 CGY vs EDM
goals in game 2: EDM scores
goals in game 2: EDM scores
How can I make this possible? I want to display all the goals scored in each game from 'goals' table without duplicating anything in 'gamelog' table.
Do i need to use a GROUP BY? I tried adding that into my code but my syntax must be off. I cannot get it work. Here is what I tried:
$sql = "SELECT gamelog.game_number, gamelog.home_team_id, gamelog.away_team_id,
t1.name AS hometeam, t2.name AS awayteam,
goals.game_number_id,
COUNT(*)";
FROM gamelog
JOIN teams AS t1 ON gamelog.home_team_id=t1.id
JOIN teams AS t2 ON gamelog.away_team_id=t2.id
JOIN goals ON gamelog.game_number = goals.game_number_id
GROUP BY gamelog.game_number
It may also have something to do with how my foreach loops are set up? Or maybe even the fact that I am using 'fetchAll'?
I even tried making two separate queries. One for the gamelog like in example A from above, and the other for the goals scored. The duplication stopped since my 'gamelog' table wasn't joined to the 'goals' table, but this just resulted in my php printing all the goals in database like so:
Game 1: MTL vs BOS
goals in game 1: MTL scores
goals in game 1: MTL scores
goals in game 1: BOS scores
goals in game 2: EDM scores
goals in game 2: EDM scores
Game 2: EDM vs CGY
goals in game 1: MTL scores
goals in game 1: MTL scores
goals in game 1: BOS scores
goals in game 2: EDM scores
goals in game 2: EDM scores
Really stumped by this. I feel like it is a small error on my end and would really appreciate any help with this 
Edit: Here is a var_dump of my $results array. It is even showing columns i didnt even include in my SELECT query
array(8) { [0]=> array(11) { ["game_number"]=> int(1) ["home_team_id"]=> int(1) ["away_team_id"]=> int(2) ["home_score"]=> int(2) ["away_score"]=> int(1) ["hometeam"]=> string(3) "MTL" ["awayteam"]=> string(3) "BOS" ["homelogo"]=> string(12) "habs-svg.svg" ["awaylogo"]=> string(14) "bruins-svg.svg" ["game_number_id"]=> int(1) ["team_id_goal"]=> int(1) } [1]=> array(11) { ["game_number"]=> int(1) ["home_team_id"]=> int(1) ["away_team_id"]=> int(2) ["home_score"]=> int(2) ["away_score"]=> int(1) ["hometeam"]=> string(3) "MTL" ["awayteam"]=> string(3) "BOS" ["homelogo"]=> string(12) "habs-svg.svg" ["awaylogo"]=> string(14) "bruins-svg.svg" ["game_number_id"]=> int(1) ["team_id_goal"]=> int(1) } [2]=> array(11) { ["game_number"]=> int(1) ["home_team_id"]=> int(1) ["away_team_id"]=> int(2) ["home_score"]=> int(2) ["away_score"]=> int(1) ["hometeam"]=> string(3) "MTL" ["awayteam"]=> string(3) "BOS" ["homelogo"]=> string(12) "habs-svg.svg" ["awaylogo"]=> string(14) "bruins-svg.svg" ["game_number_id"]=> int(1) ["team_id_goal"]=> int(2) } [3]=> array(11) { ["game_number"]=> int(2) ["home_team_id"]=> int(2) ["away_team_id"]=> int(1) ["home_score"]=> int(3) ["away_score"]=> int(2) ["hometeam"]=> string(3) "BOS" ["awayteam"]=> string(3) "MTL" ["homelogo"]=> string(14) "bruins-svg.svg" ["awaylogo"]=> string(12) "habs-svg.svg" ["game_number_id"]=> int(2) ["team_id_goal"]=> int(2) } [4]=> array(11) { ["game_number"]=> int(2) ["home_team_id"]=> int(2) ["away_team_id"]=> int(1) ["home_score"]=> int(3) ["away_score"]=> int(2) ["hometeam"]=> string(3) "BOS" ["awayteam"]=> string(3) "MTL" ["homelogo"]=> string(14) "bruins-svg.svg" ["awaylogo"]=> string(12) "habs-svg.svg" ["game_number_id"]=> int(2) ["team_id_goal"]=> int(2) } [5]=> array(11) { ["game_number"]=> int(2) ["home_team_id"]=> int(2) ["away_team_id"]=> int(1) ["home_score"]=> int(3) ["away_score"]=> int(2) ["hometeam"]=> string(3) "BOS" ["awayteam"]=> string(3) "MTL" ["homelogo"]=> string(14) "bruins-svg.svg" ["awaylogo"]=> string(12) "habs-svg.svg" ["game_number_id"]=> int(2) ["team_id_goal"]=> int(2) } [6]=> array(11) { ["game_number"]=> int(2) ["home_team_id"]=> int(2) ["away_team_id"]=> int(1) ["home_score"]=> int(3) ["away_score"]=> int(2) ["hometeam"]=> string(3) "BOS" ["awayteam"]=> string(3) "MTL" ["homelogo"]=> string(14) "bruins-svg.svg" ["awaylogo"]=> string(12) "habs-svg.svg" ["game_number_id"]=> int(2) ["team_id_goal"]=> int(1) } [7]=> array(11) { ["game_number"]=> int(2) ["home_team_id"]=> int(2) ["away_team_id"]=> int(1) ["home_score"]=> int(3) ["away_score"]=> int(2) ["hometeam"]=> string(3) "BOS" ["awayteam"]=> string(3) "MTL" ["homelogo"]=> string(14) "bruins-svg.svg" ["awaylogo"]=> string(12) "habs-svg.svg" ["game_number_id"]=> int(2) ["team_id_goal"]=> int(1) } }
答案1
得分: 1
以下是翻译好的部分：
SQL的角色是存储和检索数据，PHP的角色是使数据满足您的演示需求。不要混淆这两个角色，也不要期望SQL执行演示任务。
对于SQL来说，当表格连接时，预期（并且是正确的）是SQL会提供行的乘积，并且在每一行上您会看到一些重复的数据（例如游戏名称）。因此，您的问题不在于表格如何连接，而在于如何使用PHP按您想要的顺序显示您想要看到的项目。
查看此示例运行https://phpize.online/s/eC（使用PDO）
<?php
// 使用PDO
$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";
$stmt = $pdo->prepare($sql);
$stmt->execute();
$result = $stmt->fetchAll();
foreach ($result as $row) {
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 = :game_number";
$stmt2 = $pdo->prepare($sql2);
$stmt2->execute(['game_number' => $row["game_number"]]);
$result2 = $stmt2->fetchAll();
foreach ($result2 as $row2) {
echo "goals in game " . $row["game_number"] . ": " . $row2["team_name"] . " scores\n";
}
}
?>
结果
game 1: MTL vs BOS
goals in game 1: BOS scores
goals in game 1: BOS scores
goals in game 1: MTL scores
game 2: CGY vs EDM
goals in game 2: EDM scores
goals in game 2: EDM scores
使用MySQLi等效方式
// 使用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";
}
}
?>
英文:
It is the role of SQL to store and retrieve your data. It is the role of PHP to make that data meet your presentation requirements. Don't confuse the two roles and expect SQL to do presentation.
For SQL it is expected (and correct) that SQL will give you the multiplication of rows when the tables are joined, and that on each row you will see some data repeated (such as the game name). So, your problem isn't how the tables join, it is how do you use PHP to show only the items you want to see, in the order you want to see them.
See this operating at https://phpize.online/s/eC (using pdo)
<?php
// Using PDO
$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";
$stmt = $pdo->prepare($sql);
$stmt->execute();
$result = $stmt->fetchAll();
foreach ($result as $row) {
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 = :game_number";
$stmt2 = $pdo->prepare($sql2);
$stmt2->execute(['game_number' => $row["game_number"]]);
$result2 = $stmt2->fetchAll();
foreach ($result2 as $row2) {
echo "goals in game " . $row["game_number"] . ": " . $row2["team_name"] . " scores\n";
}
}
result
game 1: MTL vs BOS
goals in game 1: BOS scores
goals in game 1: BOS scores
goals in game 1: MTL scores
game 2: CGY vs EDM
goals in game 2: EDM scores
goals in game 2: EDM scores
equivalent using MySQLi
// 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";
}
}
?>
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论