current community

your communities

more stack exchange communities

Stack Exchange
tour help
stack overflow careers
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote -1 down vote favorite

This question already has an answer here:

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) . ')");
share|improve this question

marked as duplicate by Jordan, Rahil Wazir, andrewsi, Devin, Ghost Sep 25 '14 at 1:55

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.
    
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 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 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

1 Answer 1

up vote 3 down vote accepted

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'

share|improve this answer
    
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
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,.. –  vlzvl Sep 24 '14 at 21:14
1  
Thanks @JayBlanchard, I added your comment in my answer. –  Thiago França Sep 24 '14 at 21:28
1  
@Chaya Cooper: In SQL you use ' for string delimiters. –  zerkms Sep 24 '14 at 21:46

Not the answer you're looking for? Browse other questions tagged or ask your own question.