General Error SQLSTATE[HY000] when executing Query in Yii 2

Question

Here's my code:

$sql = "
        SET @run_balqty :=0;

        SELECT 
            transaction_date, 
            item_id, 
            item_description, 
            unit_id, 
            quantity,
            ( @run_balqty := @run_balqty + quantity ) AS balance_qty,
            reference_code
        FROM 
            `report_ledger` AS ledger
        WHERE
            item_id = 3147
        ORDER BY
            transaction_date ";

$query = Yii::$app->db->createCommand($sql)->queryAll();

When I tried to run this code. I get this error.

SQLSTATE[HY000]: General error

Now.. My question is: Why Do I get this error? and how can I make it run?

Need Help. Thanks.

Answer 1

Your are trying to fetch the results of a query that contains a command (SET @run_balqty :=0) that it is not 'fetchable'. You have to execute first that command alone, and then you can call queryAll() to your SELECT query command:

Yii::$app->db->createCommand("SET @run_balqty :=null;")->execute();

$sql = "
        SELECT 
            transaction_date, 
            item_id, 
            item_description, 
            unit_id, 
            quantity,
            ( @run_balqty := @run_balqty + quantity ) AS balance_qty,
            reference_code
        FROM 
            `report_ledger` AS ledger
        WHERE
            item_id = 3147
        ORDER BY
            transaction_date ";

$query = Yii::$app->db->createCommand($sql)->queryAll();

P.S.: Be careful using the SET statement, read this.