0

i have some issue on getting cumulative sum by month with PDO/MySQL, php 7.0 and mysql 5.5.

With multiple queries, i got "Excention : SQLSTATE[HY000]: General errorvar" as error. If i remove those "SET" lines, it disappear but i cannot have what i am trying to do.

$sql = "
SET @accInc := 0;
SET @accOut := 0;
SELECT
q1.y as year,
q1.m as month,
(@accInc := @accInc + q1.income) AS accInc,
(@accOut := @accOut + q1.outgo) AS outInc
FROM
    (SELECT
    YEAR(`date`) AS y,
    MONTH(`date`) AS m,
    SUM(CASE WHEN amount > 0 THEN amount ELSE 0 END) as income,
    SUM(CASE WHEN amount < 0 THEN amount ELSE 0 END) as outgo
    FROM `transaction`
    GROUP BY y, m
    ORDER BY y, m
    ) AS q1";
require("./pdo.php");
try {
    $req = $db->prepare($sql);
    $req->execute();
    $data = $req->fetchAll(PDO::FETCH_ASSOC);
    foreach($data as $row) {
        $acc[$row["year"]][$row["month"]] = array(
            "inc" => (float)$row["accInt"],
            "out" => (float)$row["accOut"]
        );
    }
} catch(PDOException $except) {
    printf("Excention : %s", $except->getMessage());
}

Thank you.

Edit : I had to set $db->setAttribute(PDO::ATTR_EMULATE_PREPARES, false); And isolate SET command. Now it works.

zhifir
  • 17
  • 6
  • 1
    Do you have emulation on? Also `fetchAll` and then `foreach` is a waste of resources. Use `while` and a `fetch`. – user3783243 May 25 '19 at 16:18
  • Same error with `while -> fetch()`. The code above works on mysql commandline. – zhifir May 25 '19 at 16:28
  • That was just a note about performance. It wouldn't resolve the main issue. Back to the first question, `Do you have emulation on?`?? – user3783243 May 25 '19 at 16:50
  • Possible duplicate of [PDO support for multiple queries (PDO\_MYSQL, PDO\_MYSQLND)](https://stackoverflow.com/questions/6346674/pdo-support-for-multiple-queries-pdo-mysql-pdo-mysqlnd) – user3783243 May 25 '19 at 16:51
  • Sorry. Ok i add `PDO::ATTR_EMULATE_PREPARES`, no error but null result. – zhifir May 25 '19 at 16:53
  • Issue solved by setting `PDO::ATTR_EMULATE_PREPARES` to `false` and isolating `SET @accInc := 0` and `SET @accOut := 0`. Thank you. – zhifir May 25 '19 at 17:28

0 Answers0