不明确的 PDO 语句 - Php?
所以,我正在尝试获取某门课程的所有评论。例如,它告诉哪个课程有哪些评论的方式是通过 course.php?cID=1563 。 cID 是执行此区分的标识符。
它不断抛出这个致命错误:致命错误:未捕获异常'PDOException',消息'SQLSTATE[23000]:完整性约束违规:1052 列'cID'在where子句中不明确'PDOStatement->execute(Array)# 1 在第 42 行抛出
(2) 个表的数据库结构:
PHP:
<?php
// Get course information cID, prefix, code and dept info : name
$cID = filter_input(INPUT_GET, 'cID', FILTER_SANITIZE_NUMBER_INT);
if(!$cID) {
echo "No cID specified.";
exit;
}
$username = "###";
$password = "###";
$pdo2 = new PDO('mysql:host=localhost;dbname=####', $username, $password);
$pdo2->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );
$sth2 = $pdo2->prepare('
SELECT Co.info, Co.date
FROM Course C, Comment Co
WHERE C.cID = Co.cID
AND cID = ?;
;');
$sth2->execute(array(
$cID
));
?>
PHP 调用上述内容:
<?php
// Did we get any *course detail*
if($sth2->rowCount() > 0) {
$row = $sth2->fetch(PDO::FETCH_ASSOC);
echo "<img class='left' style='margin:5px;' src='img/courseComment.png'/> <p> {$row['info']} </p>";
} else {
echo "No results.";
}
unset($sth2);
?>
为什么会发生这种情况?如果有人可以帮助我解释和修复代码,我将不胜感激。 url 还包含页面的 cID。
So, I'm trying to get all the comments for a certain course. The way it tells what course has which comments is by the course.php?cID=1563 for example. The cID is the identifer which performs this distinction.
It keeps throwing this fatal error: Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[23000]: Integrity constraint violation: 1052 Column 'cID' in where clause is ambiguous' PDOStatement->execute(Array) #1 thrown on line 42
DB Structure for (2) tables:
PHP:
<?php
// Get course information cID, prefix, code and dept info : name
$cID = filter_input(INPUT_GET, 'cID', FILTER_SANITIZE_NUMBER_INT);
if(!$cID) {
echo "No cID specified.";
exit;
}
$username = "###";
$password = "###";
$pdo2 = new PDO('mysql:host=localhost;dbname=####', $username, $password);
$pdo2->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );
$sth2 = $pdo2->prepare('
SELECT Co.info, Co.date
FROM Course C, Comment Co
WHERE C.cID = Co.cID
AND cID = ?;
;');
$sth2->execute(array(
$cID
));
?>
PHP Calling the above:
<?php
// Did we get any *course detail*
if($sth2->rowCount() > 0) {
$row = $sth2->fetch(PDO::FETCH_ASSOC);
echo "<img class='left' style='margin:5px;' src='img/courseComment.png'/> <p> {$row['info']} </p>";
} else {
echo "No results.";
}
unset($sth2);
?>
Why does this happen? If someone could please help me with an explanation and code fix that would be greatly appreciated. Also The url has the cID of the page.
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(2)
您引用的是两个表中都存在的
cID
列。更新您的查询 ($sth2
) 以仅指向其中一个表,如下所示:You are referring to a
cID
column which exists in both tables. Update your query ($sth2
) to point to only one of the tables, like so:我(从你的问题中)看不出为什么你需要引用课程表。您返回的数据完全来自 Comment 表。我假设您之前有一个查询来提取课程信息,现在您将获得相关的评论。如果这是正确的,您的代码的一个稍微好一点的版本可能是:
我对 Visio 图表中的符号的含义有点生疏,但是如果一门课程可以有多个评论,您将需要一个
fetchAll ()
,而不是fetch()
,并且需要迭代结果数组。如果每个课程只能有一个评论,您可以通过cID
字段上的JOIN
将此查询合并到上一个查询中。I don't see (from your question) why you need to reference the Course table at all. The data you're returning is completely from the Comment table. I assume you have a previous query that pulls the Course info, and now you're getting the associated comments. If this is correct, a slightly better version of your code might be:
I'm a little rusty on what the symbols from your Visio graph mean, but if there can be multiple Comments for one Course, you would want a
fetchAll()
, notfetch()
, and would need to iterate over the resulting array. If there can be only one Comment per Course, you could combine this query into the previous query with aJOIN
on thecID
field.