CODEWITHSUNDEEP
phpmysqlpdoprepared-statement
lampshade
lampshade

Reputation: 2796

PDO bind parameter doesn't work

I have a query like:

$stmt = $db->query('
    SELECT e.id
    FROM `entity` e
    WHERE e.deleted = 0 AND e.published = 1 AND e.date <= CURDATE() ' .
          ( !empty($_SESSION['filter']) ? ' AND e.category = :category' : '' )
);

if (!empty($_SESSION['filter'])) {
    $stmt->bindValue('category', $_SESSION['filter'], PDO::PARAM_INT);
}

$entities = $stmt->fetchAll(PDO::FETCH_ASSOC);

I've some entries in catgeory 1 and $_SESSION['filter'] is set:

string(1) "1"

But it doesn't load any entries nor do I get any error.

What I tried too:

Out of all possibilites only the last one worked. It's also the one I actually don't want to use. How can I fix this?

Upvotes: 0

Views: 242

Answers (2)

u_mulder
u_mulder

Reputation: 54841

PDO::query() executes an SQL statement in a single function call, returning the result set (if any) returned by the statement as a PDOStatement object.

Your query is already executed. Binding values takes no effect.

You should prepare your query first.

$stmt = $db->prepare('
    SELECT e.id
    FROM `entity` e
    WHERE e.deleted = 0 AND e.published = 1 AND e.date <= CURDATE() ' .
      ( !empty($_SESSION['filter']) ? ' AND e.category = :category' : '' )
);

Upvotes: 3

Joel Hinz
Joel Hinz

Reputation: 25384

You're missing a colon in this line:

$stmt->bindValue('category', $_SESSION['filter'], PDO::PARAM_INT);

Should be:

$stmt->bindValue(':category', $_SESSION['filter'], PDO::PARAM_INT);

Edit: I missed the most obvious issue. Please see u_mulder's answer as well.

Upvotes: 1

Related Questions