Ambiguous PDO Statement - Php?
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 c开发者_运维问答ode fix that would be greatly appreciated. Also The url has the cID of the page.
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:
SELECT Co.info, Co.date
FROM Course C, Comment Co
WHERE C.cID = Co.cID
AND C.cID = ?;
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:
$sth2 = $pdo2->prepare('
SELECT Co.info, Co.date
FROM Comment Co
WHERE Co.cID = :id;
');
$sth2->bindParam( ':id', $cID, PDO::PARAM_INT );
$sth2->execute();
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()
, not fetch()
, 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 a JOIN
on the cID
field.
精彩评论