[FIXED] Passing an array to a query using a WHERE clause - works in phpmyadmin, not in my script

Issue

I've searched for the answer online, if I've missed something obvious, I would appreciate links. Otherwise, I'd be grateful for direct help. This is the first time I've ever tried a query like this.

I have the following query:

SELECT * FROM `dice_t` WHERE qty IN (:qty) AND opacity IN (:opacity) AND color IN (:color)

To which I am feeding the following array:

Array
(
    [qty] => 1,2
    [opacity] => 3
    [color] => 467,1007
)

It works perfectly (retrieves 163 rows) in phpMyAdmin (when I type in the values), but in my script, it retrieves only 114 rows, which corresponds to it using only the first value in each field (i.e. qty: 1; opacity: 3; color: 467). I have verified this by running the query with only those values in phpMyAdmin.

My code looks like this:

$statement = $dbConn->prepare($sql);
$statement->execute($queryData);
$result = $statement->fetchAll(PDO::FETCH_ASSOC);

When I print the values of $sql and $queryData I get the values listed in the first two code blocks above.

The fields are all integers. I tried searching with single quotes around the values, but got an error.

I can't figure out what I'm doing wrong.

Solution

the Varable :qty is handled as Strinf So you have '1,1'

So you must use FIND_IN_SET

SELECT * FROM `dice_t` WHERE FIND_IN_SET(`qty`,:qty) AND  FIND_ON_SET(`opacity`,:opacity) AND  FIND_IN_SET(`color`,:color)

other ways are in this thread Can I bind an array to an IN() condition?

Answered By - nbk