I have two tables which I’m trying to run a query on.
If I run the below query in phpmyadmin I get the results I expect, just one result returned with id 9, but when running it on my webpage, its returning all 4 results. I can put what ever I want as the user id, even one that doesn’t exist, its ignoring it.
$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"];
The echo produces:
SELECT c.*
, cu.userID
FROM ConversationUsers AS cu
LEFT
JOIN Conversations AS c
ON c.id = cu.convoID
WHERE cu.userID = 1
The print produces
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
)
)
The tables are setup as follows
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);
2
Answers
You miss the
:
before the parameter in your query:You’re missing the
:
in:userID
. That makes theWHERE
always be true:Your
echo
line used$vars["userID"]
which is why it showed the correct result.