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

Answer 1

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.