0

Response to possible duplicate question - Please note that while to some (perhaps more experienced) programmers this might seem like a duplicate question, but to a noob like myself it isn't :-( The question that was indicated as a duplicate does not address my question, whereas the accepted answer solved the problem perfectly.

According to numerous SO posts (including this one), in order to use an IN() operator with an array you first need to implode it (thus converting the array to a string).

The query below works correctly with a variable in the IN() statement, but I can't seem to get it to work with an imploded array.

This works and returns 8 rows of products

$colors_VAR = "'Black','Royal_Blue','Dodger_Blue','Red'";
$stmt = $conn->prepare("SELECT * FROM products WHERE products.Color IN ($colors_VAR)");

This doesn't return any results

$colors_Array = array('Black','Royal_Blue','Dodger_Blue','Red');
$stmt = $conn->prepare("SELECT * FROM products WHERE products.Color IN (' . implode(',', $colors_Array) . ')");
Community
  • 1
  • 1
Chaya Cooper
  • 2,566
  • 2
  • 38
  • 67
  • `echo` the query before you execute it. Check your variables and other stuff every time you are not sure. PS: if the array isn't static you must sanitize it with escape function or use prepared statements instead. – zerkms Sep 24 '14 at 21:00
  • Doing string concatenation like this defeats the purpose of using `PDO::prepare`. The above answer shows how to use it correctly with an array. (Basically you generate the `?, ?, ?, ...` based on the length of the array and then pass the array to `execute`, which lets PDO do the work of escaping and quoting the values for you, which is why you were using PDO in the first place.) – Jordan Running Sep 24 '14 at 21:06
  • @Jordan - How would you use it with an array that's generated dynamically? When I'm having trouble getting code to work I create the simplest possible code just to figure it out, but the array is actually being generated dynamically by values fetched from the db – Chaya Cooper Sep 24 '14 at 21:12
  • @ChayaCooper I don't understand the question. An array is an array regardless of how it's generated. (Well, PHP has two kinds of arrays, associative and not, but they're still just arrays regardless of how they're generated.) – Jordan Running Sep 24 '14 at 21:15
  • @Jordan - I guess I should have specified that the length of the array isn't constant when it's generated dynamically – Chaya Cooper Sep 24 '14 at 21:18
  • @ChayaCooper The answer I linked to doesn't care about the size of the array. It generates a string of `?, ?, ?, ?, ...` based on the size of the array (`implode(',', array_fill(0, count($ids), '?'))`), so it'll work with an array of any length. (Well, except length 0, so you should check that beforehand.) – Jordan Running Sep 24 '14 at 21:20
  • @Jordan - I totally misunderstood that (chalk it up to my coding being a bit rusty). To be honest I'm not sure if I really need to use PDO's here because in this particular instance there isn't a risk of sql injection, I just figured it's good practice to be consistent. Are there any benefits that I should know about here (either about the method you suggested or PDO's in general)? – Chaya Cooper Sep 24 '14 at 21:38
  • Personally I just think string concatenation, especially when multiple levels of quotes are involved, is messy, difficult to read and, as you discovered, error-prone. Much smarter people than you or I have put thousands of person-hours into building PDO, which handles all of that stuff correctly so we don't have to think about it. – Jordan Running Sep 24 '14 at 21:46
  • @Jordan, You're absolutely right :-D Out of curiosity, is there a way to combine that with a subquery? At the moment I'm 1st running a query to build the array, and then calling it in the 2nd query (which feels pretty inefficient ;-)) – Chaya Cooper Sep 24 '14 at 21:52
  • Doing a subquery would probably eliminate the business with the array entirely. It might be as simple as `WHERE field IN (SELECT …)` but you may want to post that as another question. – Jordan Running Sep 24 '14 at 22:46
  • I answered on this problem, it will solve your problem too. http://stackoverflow.com/questions/920353/can-i-bind-an-array-to-an-in-condition/36070527#36070527 – 4EACH Mar 17 '16 at 20:03
  • Thankfully Thiago's answer below has already solved the problem perfectly ;-) – Chaya Cooper Mar 17 '16 at 20:35

1 Answers1

4

you need to enclose colors with quotes, like 

$colors_Array = array('Black','Royal_Blue','Dodger_Blue','Red');
$stmt = $conn->prepare("SELECT * FROM products WHERE products.Color IN ('" . implode("','", $colors_Array) . "')");

The additional set of quotes place a set of quotes around each individual item in the array - 'Black','Royal_Blue','Dodger_Blue','Red'

Thiago França
  • 1,817
  • 1
  • 15
  • 20
  • That worked :-D (And I'll accept it in 6 minutes, when SO allows me to ;-)) Would you mind explaining the syntax so that I don't make the same silly mistake again? – Chaya Cooper Sep 24 '14 at 21:07
  • @Chaya Cooper: just compare the syntax highlight – zerkms Sep 24 '14 at 21:08
  • @zerkms - I'd love to understand the reason for the additional sets of quotes, not just how to fix this specific problem ;-) – Chaya Cooper Sep 24 '14 at 21:10
  • @Chaya Cooper: in your example it was a long string literal delimited by `"`. In this answer - it's 2 literals concatenated with an `implode` function call results. – zerkms Sep 24 '14 at 21:12
  • 2
    The additional set of quotes place a set of quotes around each individual item in the array - `"Black","Royal_Blue","Dodger_Blue","Red"` – Jay Blanchard Sep 24 '14 at 21:12
  • 2
    @Chaya Cooper, this answer uses the `','` delimiter which sorts the elements as `'elem1','elm2','..`. Your version just sorts them out by a single comma `'elem1,elem2,..` –  Sep 24 '14 at 21:14
  • @vlzvl That's super helpful :-D – Chaya Cooper Sep 24 '14 at 21:20
  • 1
    Thanks @JayBlanchard, I added your comment in my answer. – Thiago França Sep 24 '14 at 21:28
  • 1
    @ThiagoFranca - Your answer is perfect :-D Thank you so much :-D – Chaya Cooper Sep 24 '14 at 21:29
  • @ThiagoFrança a side note: in your code you use `'` as a string literal delimiter, then in explanation you use `"`. And another important thing: `'` as a string literal delimiter is "more" ANSI SQL friendly (since `"` is supposed to be used for identifiers) – zerkms Sep 24 '14 at 21:43
  • @zerkms - Would you mind clarifying which one would be better to use in this situation? – Chaya Cooper Sep 24 '14 at 21:44
  • 1
    @Chaya Cooper: In SQL you use `'` for string delimiters. – zerkms Sep 24 '14 at 21:46