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

PDO binding array with the IN operator

I have non-standard sql query and I need bind array params for WHERE IN statement. How can I do this?

This:

$db->query(".... WHERE field IN (:ids)", ['ids' => implode(', ', $array)])->fetchAll();

not works



85.5k

whats the error cuz it seems like it should be working ?



7.0k

this variant set this

    IN ('1, 2, 3')

but not this

    IN (1, 2, 3)


85.5k

https://docs.phalcon.io/en/3.0.0/reference/phql.html

search page for inWhere is it possible to try this ?

or show the whole code block to see what $db is

Can you use phql ?



7.0k
edited Nov '16

NO, I can't use phql. I have non-standard sql query



7.0k

sql look like this (simple version):

WITH dates AS (
    SELECT
        s::date AS date,
        to_char(s::time, 'hh24:mi') AS time,
        to_char(s::time, 'hh24') AS hour
    FROM (
        SELECT * FROM generate_series(
            '2016-11-21'::timestamp with time zone, 
            '2016-11-28'::timestamp with time zone, 
            '1 hour'
        ) AS s
    ) AS dates
    WHERE s::time BETWEEN '07:00' AND '23:00'
)

SELECT DISTINCT
    t.*,
    COUNT(o.id) OVER(PARTITION BY o.date, o.time) AS table2_count,
    (
        CASE WHEN CONCAT(t.date, ' ', t.time)::timestamp with time zone < NOW()
        THEN 0
        ELSE 1
        END
    ) AS can_add
FROM dates AS t
LEFT JOIN table2 o ON o.date = t.date 
    AND (o.time = t.time OR o.time = ltrim(t.time, '0'))
    AND (
        CASE WHEN CONCAT(t.date, ' ', t.time)::timestamp with time zone < NOW()
        THEN o.id_status IN (3, 4, 5, 6, 8)
        ELSE o.id_status IN (2, 3, 5)
        END
    )
ORDER BY t.date, t.time
edited Nov '16

Then there is no difference than any other sql. Just check mysql or whatever other sql engine you are using.

I would just rewrite this query to PHQL.



7.0k

this query is nor bind to any model. It's like a tool (or helper). But phql works only in Models. Isn't?

Yes. But PDO as itself support parameters binding obviously.



7.0k

And.... for result we have:

this variant set this

IN ('1, 2, 3')

but not this

IN (1, 2, 3)


7.0k
Accepted
answer
edited Nov '16

solved with NO use phql and bypassing pdo troubles))

? DB service is using just PDO, NOT PHQL.



7.0k

I didn't say that I not use DB service))



7.0k

So, for all may say, that in phalcon (and in native php pdo) it's impossible

edited Nov '16

It is :) Just don't use binding for array. Keep in mind it's not framework problem.

Also this is solution to still have values binded - https://stackoverflow.com/a/920523/4035199 you have to bind values - not array.

Another solution is to use find_in_set