0

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.

Dharman
  • 30,962
  • 25
  • 85
  • 135
Erika S
  • 11
  • 3
  • 1
    Does this answer your question? [Can I bind an array to an IN() condition?](https://stackoverflow.com/questions/920353/can-i-bind-an-array-to-an-in-condition) Can you see the end result? What is the SQL sent to the database from your code? Share that. Or compare to what you've used in phpmyadmin, they must differ. – ficuscr Feb 25 '22 at 20:34
  • Well that's interesting! If I type it out without using the array (just copy-paste from phpMyAdmin), it works. So it's not reading anything past the comma in each field. Any idea why that might be? – Erika S Feb 25 '22 at 20:53

1 Answers1

0

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?

nbk
  • 45,398
  • 8
  • 30
  • 47
  • AFAIK, FIND_IN_SET doesn't use indexes, hence this is not a solution but a landmine under the future performance. – Your Common Sense Feb 25 '22 at 21:15
  • The solutions presented in the links take a comma separated stirng and split it and make a more or minus huge `IN` IN is slow for huge data and FIND_IN_SET isn't that much slower. – nbk Feb 25 '22 at 21:31
  • what? what do you mean, "slow"? got any proof? – Your Common Sense Feb 25 '22 at 21:33
  • that is my experience with big databses, so i use normally INNER JOINS ibstead IN Clause, if you got to that point try a join, But for a small amount of ids, it doesn't matter – nbk Feb 25 '22 at 21:39
  • so you don't have any proof. that was quite expected. – Your Common Sense Feb 25 '22 at 21:40
  • i recoment to try it with a million rows generated and then to try it increasing the number of IN elements. Try it – nbk Feb 25 '22 at 21:47
  • I have some knowledge. In how databases work. I don't need your homebrewed tests. There is not a single reason for IN to be slow with a properly configured database. And I even have some experience. That tells me that your fear for IN is coming from a completely DIFFERENT case where you were using it with a SELECT subquery. That indeed could be slow. Have a nice day and try not to spread misinformation. – Your Common Sense Feb 25 '22 at 21:57