绑定值与PDO，问题插入是空的 - Bind value with PDO, problems inserting IS NULL

Bind value with PDO, problems inserting IS NULL

本文关键字：插入问题 PDO 绑定 | 更新日期: 2023-09-27

我有这个函数:

  function fetch_article_comments($article_id, $parent_id) {
$app = new Connection();
    if ($parent_id > 0) {
        $parent_id = '= '. $parent_id;
    } else {
        $parent_id = "IS NULL";
    }
    $sql = "SELECT * FROM recursive WHERE article_id = :article_id AND comment_parent :parent_id ORDER BY comment_timestamp DESC";
    $query = $app->getConnection()->prepare($sql);
    try{
        $query->execute(array(':article_id' => $article_id,
                             ':parent_id' => $parent_id));
        $comments = $query->fetchAll();   //returns an stdClass
        $query->closeCursor();
        return $comments;
    } catch(PDOException $e){
        die($e->getMessage());
    }
}

我想让$parent_id等于IS NULL。但是我得到这个错误信息:

PHP Warning: PDOStatement::execute(): SQLSTATE[42000]: Syntax error你的SQL语法有错误;检查该手册对应您的MySQL服务器版本为正确ORDER BY comment_timestamp DESC'

为了简洁的代码，我不希望整个查询都在if语句中。

但$parent_id怎么能设为IS NULL而不设为'IS NULL'呢

您正在尝试以不允许的方式使用条件语句和参数绑定。

尝试改变这个…

    if ($parent_id > 0) {
        $parent_id = '= '. $parent_id;
    } else {
        $parent_id = "IS NULL";
    }
$sql = "SELECT * FROM recursive WHERE article_id = :article_id AND comment_parent :parent_id ORDER BY comment_timestamp DESC";

这个…

if ($parent_id > 0) {
    $sql = "SELECT * FROM recursive WHERE article_id = :article_id AND comment_parent = :parent_id ORDER BY comment_timestamp DESC";
} else {
    $sql = "SELECT * FROM recursive WHERE article_id = :article_id AND comment_parent IS NULL ORDER BY comment_timestamp DESC";
}

$app = new Connection();
function fetch_article_comments($article_id, $parent_id, $app) {
    if ($parent_id <= 0) {
        $parent_id = NULL;
    }
    $sql = "SELECT * FROM recursive WHERE article_id = :article_id AND comment_parent <=> :parent_id ORDER BY comment_timestamp DESC";
    $query = $app->getConnection()->prepare($sql);
    $query->execute(array(':article_id' => $article_id,
                             ':parent_id' => $parent_id));
    return $query->fetchAll();
}