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

MySQL user-variables

Hi,

How can I use MySQL variables in a query?

If I try the variable inside a where clause, i.e:

    UserModel::findFirst([
        'company_id = @COMPANY_ID AND username = :username:',
        'bind' => [
            'username'   => $username,
        ]
    ]);

I get a scanning error: Scanning error before 'COMPANY_ID AND u...'.

When I use

new \Phalcon\Db\RawValue('@COMPANY_ID')

it still gets quoted in the query, while stated in the documentation: raw value without quoting or formatting.

Query log:

734 Query   SELECT `user`.`user_id`, `user`.`company_id`, `user`.`username` FROM `user` WHERE `user`.`company_id` = '@COMPANY_ID' AND `user`.`username` = 'faim' LIMIT 1

Looking forward to a reply :-)

edited Feb '16

Hi!

find and findFirst use PHQL to generate SQL so you can not use all SQL power :( Use plain SQL.

Read more https://docs.phalcon.io/en/latest/reference/phql.html#using-raw-sql

Unfortunately then I have to write every query manually, shouldn't RawValue work in this case?

If there isn't any other solution for this, I think I'm just gonna define the value and put in in every query.

Hi!

find and findFirst use PHQL to generate SQL so you can not use all SQL power :( Use plain SQL.

Read more https://docs.phalcon.io/en/latest/reference/phql.html#using-raw-sql

If i remember correctly if you use SQL you don't need RawValue for your case.

P.S. Unfortunately PHQL cannot fully replace SQL. PHQL just for simple queries.

Unfortunately then I have to write every query manually, shouldn't RawValue work in this case?

If there isn't any other solution for this, I think I'm just gonna define the value and put in in every query.

Hi!

find and findFirst use PHQL to generate SQL so you can not use all SQL power :( Use plain SQL.

Read more https://docs.phalcon.io/en/latest/reference/phql.html#using-raw-sql