MySQL user-variables

Question

MySQL user-variables

FaimMedia B.V. Feb '16

Created Feb '16	Last Reply Feb '16	Replies 2	Views 643	Votes 0

FaimMedia B.V. 3.6k

Feb '16

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 :-)

Kostya Kufta · Answer 1 · 2016-02-17T07:59:58-07:00

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

FaimMedia B.V. · Answer 2 · 2016-02-17T11:21:27-07:00

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

Kostya Kufta · Answer 3 · 2016-02-17T13:39:21-07:00

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