Bind value with PDO, problems inserting IS NULL

Question

I have this function:

  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());
    }
}

And i want $parent_id to be IS NULL. But i get this error message:

PHP Warning: PDOStatement::execute(): SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''IS NULL' ORDER BY comment_timestamp DESC'

And for the sake of nice clean code, i don't want the whole query inside the if statement.

But how can $parent_id be set to IS NULL and not 'IS NULL'?

Prepared statements **prepare** values. They don't understand if it's language construct or something else. This is the case where you should use `if` to build sql sting first. — u_mulder, Sep 12 '15 at 12:34
Read this for #1064 MySql error [link](http://stackoverflow.com/questions/23515347/how-can-i-fix-mysql-error-1064). What do you mean with "be set to IS NULL" ? There is NULL value and there is String value ('IS NULL'). — O_T, Sep 12 '15 at 12:35

score 3 · Answer 1 · edited Oct 29 '21 at 15:55

You are trying to use conditional statements and parameter bindings in a manner that is not allowed.

Try changing this...

    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";

to this...

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";
}

Your Common Sense · Accepted Answer · 2015-09-13T05:36:23.820

$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();
}

Bind value with PDO, problems inserting IS NULL

2 Answers2