Ambiguous PDO Statement - Php?

Question

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.

Answer 1

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.

Answer 2

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 = ?;