在phpmyadmin和网站上运行查询时的结果不同

Question

我有两个表，我正在尝试对它们运行查询。

如果我在 phpmyadmin 中运行以下查询，我会得到我期望的结果，只有一个结果返回 id 为 9，但是在我的网页上运行它时，它会返回所有 4 个结果。我可以把我想要的任何内容作为用户ID，即使是不存在的用户ID，也会忽略它。

$sql = "
SELECT c.*
, cu.userID 
FROM ConversationUsers AS cu 
LEFT 
JOIN Conversations AS c 
ON c.id = cu.convoID 
WHERE cu.userID = userID
";
$q = $pdo->prepare($sql);
$q->bindValue(':userID', $vars['userID']);
$q->execute();
if($q->errorCode() != 0) {
$errors = $q->errorInfo();
echo($errors[2]);
}
foreach ($q->fetchAll() as $row) {
$convos[] = $row;
}
echo "<pre>";
print_r($convos);
echo "</pre>";
echo "
SELECT c.*
, cu.userID 
FROM ConversationUsers AS cu 
LEFT 
JOIN Conversations AS c 
ON c.id = cu.convoID 
WHERE cu.userID = " . $vars["userID"];

回声产生：

SELECT c.*
, cu.userID 
FROM ConversationUsers AS cu 
LEFT 
JOIN Conversations AS c 
ON c.id = cu.convoID 
WHERE cu.userID = 1

印刷品产生

Array
(
[0] => Array
(
[id] => 9
[0] => 9
[lastUpdated] => 2019-12-29 00:00:00
[1] => 2019-12-29 00:00:00
[userID] => 1
[2] => 1
)
[1] => Array
(
[id] => 9
[0] => 9
[lastUpdated] => 2019-12-29 00:00:00
[1] => 2019-12-29 00:00:00
[userID] => 2
[2] => 2
)
[2] => Array
(
[id] => 10
[0] => 10
[lastUpdated] => 2019-12-29 00:00:00
[1] => 2019-12-29 00:00:00
[userID] => 2
[2] => 2
)
[3] => Array
(
[id] => 10
[0] => 10
[lastUpdated] => 2019-12-29 00:00:00
[1] => 2019-12-29 00:00:00
[userID] => 3
[2] => 3
)
)

表格设置如下

CREATE TABLE `Conversations` (
`id` int(11) NOT NULL,
`lastUpdated` datetime NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `Conversations` (`id`, `lastUpdated`) VALUES
(9, '2019-12-29 00:00:00'),
(10, '2019-12-29 00:00:00');
CREATE TABLE `ConversationUsers` (
`convoID` int(11) NOT NULL,
`userID` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `ConversationUsers` (`convoID`, `userID`) VALUES
(9, 1),
(9, 2),
(10, 2),
(10, 3);

Answer 1

你错过了:userID中的:。这使得WHERE始终是正确的：

$sql = "SELECT c.*, cu.userID FROM ConversationUsers AS cu LEFT JOIN Conversations AS c ON c.id = cu.convoID WHERE cu.userID = :userID";

您的echo行使用了$vars["userID"]这就是为什么它显示正确的结果。

Answer 2

您错过了查询中参数之前的:：

$sql = "SELECT c.*, cu.userID FROM ConversationUsers AS cu LEFT JOIN Conversations AS c ON c.id = cu.convoID 
WHERE cu.userID = :userID";