CODEWITHSUNDEEP
phppdo
Adam
Adam

Reputation: 1299

Bind value with PDO, problems inserting IS NULL

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'?

Upvotes: 0

Views: 463

Answers (2)

Kuya
Kuya

Reputation: 7310

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

Upvotes: 2

Your Common Sense
Your Common Sense

Reputation: 157872

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

Upvotes: 1

Related Questions