We are moving our forum in GitHub Discussions. For questions about Phalcon v3/v4 you can visit here and for Phalcon v5 here.

Solved thread

This post is marked as solved. If you think the information contained on this thread must be part of the official documentation, please contribute submitting a pull request to its repository.

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 - http://stackoverflow.com/a/920523/4035199 you have to bind values - not array.

Another solution is to use find_in_set