SQLSTATE[HY000]: General error with MySQL variable in ZF1

Question

I have a MySQL query that works perfectly in PhpMyAdmin but throws an SQLSTATE[HY000]: General error when using within Zend Framework 1.12 and its fetchAll() method. Here it is:

SET @csum := 0;
SELECT s1.Date, (@csum := @csum + s1.total) as cumulative_sum
FROM (
        SELECT c.datefield AS Date, IFNULL(SUM(subscription_total),0) AS total
        FROM subscription s
        RIGHT JOIN calendar c
        ON c.datefield = DATE(subscription_date)
        WHERE (c.datefield BETWEEN
            (SELECT MIN(DATE(subscription_date)) FROM subscription)
            AND
            NOW()
        )
        GROUP BY DATE
    ) AS s1

The statement doesn't return any error if I remove the SET statement but I need to set a MySQL variable otherwise cumulative_sum will just be NULL values. Here is the code of the method:

public function findCumulativeCashflow($statut)
{
    $db = $this->_getDbTable();

    $dbAdapter = new Zend_Db_Adapter_Pdo_Mysql($db->getAdapter()->getConfig());

    $sql = '<SQL statement above>';

    $statement = $dbAdapter->query($sql);

    $rows = $statement->fetchAll();

    return $rows; 
}

The error points the $rows = $statement->fetchAll(); line, is there another ZF method to use with SET @var := value?

Any ideas would be much appreciated. Thanks!

score 2 · Accepted Answer · edited May 23 '17 at 11:53

2

As per this question, I need to use another query as fetchAll() cannot handle multiple queries.

I therefore need to set the MySQL variable with $dbAdapter->query('SET @csum := 0;'); and then do the main query.

edited May 23 '17 at 11:53

Community

1
1

answered Mar 29 '14 at 23:03

D4V1D

5,805
3
30
65

SQLSTATE[HY000]: General error with MySQL variable in ZF1

1 Answers1