SQL Query在MySQL中返回完整结果,部分结果返回到PHP
我有一个查询来返回一个球员和他的一个Web应用程序的选择。当我在MySQL中运行它时,我得到了我期望的结果:7名玩家和他们接下来5场比赛的选择。但是,当我将它移入我的PHP脚本时,只有三名选手被选中。所有5个选择,返回三个人,每次相同的三个人,而不是“前三个人”或“最后三个人”SQL Query在MySQL中返回完整结果,部分结果返回到PHP
我一直在这里砸了我几个小时。 ..
MySQL查询:
SELECT Players.name, BowlTeams.team_name
FROM Players, BowlTeams, BowlGames, PlayerPicks
WHERE Players.ID = PlayerPicks.playerID AND PlayerPicks.win_team = BowlTeams.ID AND PlayerPicks.bowlID = BowlGames.ID AND
(BowlGames.ID = 42 OR BowlGames.ID = 41 OR BowlGames.ID = 40 OR BowlGames.ID = 39 OR BowlGames.ID = 38)
ORDER BY Players.name, BowlGames.ID DESC
PHP代码:
try {
$stmt = $conn->prepare('SELECT Players.name, BowlTeams.team_name
FROM Players, BowlTeams, BowlGames, PlayerPicks
WHERE Players.ID = PlayerPicks.playerID AND PlayerPicks.win_team = BowlTeams.ID AND PlayerPicks.bowlID = BowlGames.ID AND
(BowlGames.ID = :bowl_zero OR BowlGames.ID = :bowl_one OR BowlGames.ID = :bowl_two OR BowlGames.ID = :bowl_three OR BowlGames.ID = :bowl_four)
ORDER BY Players.name, BowlGames.ID DESC');
$stmt->bindParam(':bowl_zero', $bowl0, PDO::PARAM_INT);
$stmt->bindParam(':bowl_one', $bowl1, PDO::PARAM_INT);
$stmt->bindParam(':bowl_two', $bowl2, PDO::PARAM_INT);
$stmt->bindParam(':bowl_three', $bowl3, PDO::PARAM_INT);
$stmt->bindParam(':bowl_four', $bowl4, PDO::PARAM_INT);
$stmt->execute();
$emparray = array();
while($row = $stmt->Fetch(PDO::FETCH_ASSOC)) {
$emparray[] = $row;
}
$stmt->closeCursor();
$stmt = null;
echo json_encode($emparray);
} catch(PDOException $e) {
echo "Oh no! Something f'd up! Error: " . $e->getMessage();
}
我一再的$ bowl0-4变量正确填充验证(这会影响BowlTeams.team_name返回,这些都很好)。
我只是没有得到我的所有球员回来......
感谢您的帮助,我在我束手无策。
ķ
作为一个起点,这似乎更容易阅读...
SELECT p.name
, t.team_name
FROM BowlGames g
JOIN PlayerPicks k
ON k.bowlID = g.ID
JOIN Players p
ON p.ID = k.playerID
JOIN BowlTeams t
ON t.ID = k.win_team
WHERE
AND g.ID IN(42,41,40,39,38)
...但不知道您的架构,很难进一步帮助。
谢谢,是的,我喜欢那个更好....同样的问题与不匹配的结果.... –
好吧,也许编辑你的问题,无论如何合并此信息。 – Strawberry
想通了。我正在开发一个开发版本,数据库导入失败,所以我有一个旧版本的数据....
哇。我认为,当它发生的时候,这是一个愚蠢的时刻...
谢谢大家。
有时候会发生这样的事情,休息一下。 –
史诗!这正是发生在我身上的事情。谢谢! –
我不是一个PHP的专家,但也许你会覆盖结果数组,而不是推入它的条目... http://www.w3schools.com/php/func_array_push.asp –
此外,如果我分在实际PHP中参数的值,我收到相同的输出(只有三名玩家返回)。 –