开发者

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:

Ambiguous PDO Statement - Php?

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.

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜