We have moved our forum to GitHub Discussions. For questions about Phalcon v3/v4/v5 you can visit here and for Phalcon v6 here.
  1. Home
  2. Beginners

How to execute query which is compound with additional "SET query"

For example:

$sql="SET @runningTotal=0; SELECT @runningTotal + a.quantity as 'RunningTotal', @runningTotal := @runningTotal +a.quantity FROM ask AS a HAVING RunningTotal <=?"

This is unfortunately not working... $result = $this->db->query($sql,[$quantity])->fetchAll();

If i run query in phpmyadmin (SET @runningTotal=0; SELECT @runningTotal + a.quantity as 'RunningTotal', @runningTotal := @runningTotal +a.quantity FROM ask AS a HAVING RunningTotal <=2) it works.

1


1.1k
edited Nov '17

The error i get is

[19-Nov-2017 17:53:53 UTC] SQLSTATE[HY000]: General error

0 [internal function]: PDOStatement->fetch(NULL, NULL, NULL)

...



4.8k
Accepted
answer
edited Nov '17

I'd run it as two separate queries via a transaction like so: 

$quantity = 50;
$db = \Phalcon\DI::getDefault()->get('db');
$db->begin();
$db->execute('SET @runningTotal=0;');
$result = $db->query("SELECT (@runningTotal := @runningTotal + a.quantity) - a.quantity as 'RunningTotal'
                      FROM ask AS a HAVING RunningTotal <= ?", [$quantity]);
$db->commit();
$results = $result->fetchAll(\PDO::FETCH_OBJ);
echo '<pre>',print_r($results,true),'</pre>';

Note I also adjusted your query a bit to save from returning an unwanted duplicate column.



1.1k

Thank you very much Kevin, it's working now :)