using same prepared statement parameter multiple times in a sql

Question

i'm trying to use a same parameter in my sql, but it's only recognizing the first. look my code:

$stmt = $dbh->prepare("SELECT
    (SELECT SUM(oq.value)
    FROM operations_quotas AS oq
        JOIN operations AS o ON oq.operation = o.id
    WHERE o.user = :user AND o.type = 1 AND oq.status = 1
    ) AS total_incomes_open,

    (SELECT SUM(oq.value)
    FROM operations_quotas AS oq
        JOIN operations AS o ON oq.operation = o.id
    WHERE o.user = :user AND o.type = 1 AND oq.status = 2
    ) AS total_incomes_wroteoff");

$stmt->bindParam(":user", $this->getId());
$stmt->execute();

is that possible?

No, you must supply it as a different param (they need to be unique) and then bind them individually. — prodigitalson
– prodigitalson, Commented Nov 27, 2012 at 14:09

MrCode · Accepted Answer · 2012-11-27 14:14:56Z

It's not possible to reuse parameters like that. You'll have to make unique parameters:

$stmt = $dbh->prepare("SELECT
    (SELECT SUM(oq.value)
    FROM operations_quotas AS oq
        JOIN operations AS o ON oq.operation = o.id
    WHERE o.user = :user_a AND o.type = 1 AND oq.status = 1
    ) AS total_incomes_open,

    (SELECT COUNT(oq.id)
    FROM operations_quotas AS oq
        JOIN operations AS o ON oq.operation = o.id
    WHERE o.user = :user_b AND o.type = 1 AND oq.status = 2
    ) AS total_incomes_wroteoff");

$stmt->bindParam(":user_a", $this->getId());
$stmt->bindParam(":user_b", $this->getId());
$stmt->execute();

From the Manual:

You must include a unique parameter marker for each value you wish to pass in to the statement when you call PDOStatement::execute(). You cannot use a named parameter marker of the same name twice in a prepared statement. You cannot bind multiple values to a single named parameter in, for example, the IN() clause of an SQL statement.

Collectives™ on Stack Overflow

using same prepared statement parameter multiple times in a sql

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related