mysql WHERE IN array string / username

Question

Code:

$friendsArray = array("zac1987", "peter", "micellelimmeizheng1152013142");
$friendsArray2 = join(', ',$friendsArray);  
$query120 = "SELECT picturemedium FROM users WHERE username IN ('$friendsArray2')";
echo $query120;

This is the output :

SELECT picturemedium FROM users WHERE username IN ('zac1987, peter, micellelimmeizheng1152013142')

It fails because usernames are not wrapped by single quotes like 'zac1987', 'peter', 'mice...'. How can each username be wrapped with single quotes?

score 14 · Accepted Answer · answered Jul 07 '11 at 22:01

14

Let's loop through each name one by one, escaping each.

I'm going to recommend that you use an actual MySQL escaping function rather than just wrapping quotes around, to ensure that the data actually goes into the query correctly. (Otherwise, if I entered a name like It's me!, the single quote would mess up the query.) I'm going to assume here that you're using PDO (which you should!), but, if not, replace references to PDO::quote with mysql_real_escape_string.

foreach($friendsArray as $key => $friend) {
  $friendsArray[$key] = PDO::quote($friend);
}

$friendsArray2 = join(', ', $friendsArray);

answered Jul 07 '11 at 22:01

Matchu

83,922
18
153
160

I have tested mysql_real_escape_string, the output is still the same as 'zac1987, peter, micellelimmeizheng1152013142' – zac1987 Jul 07 '11 at 22:10
1

Yes, that should be the case here. What you need is `$friendsArray[$key] = "'".mysql_real_escape_string($friend)."'";`. – Michael Mior Jul 07 '11 at 22:15
@Michael: derp. Been a while since I used the standard MySQL functions xD Thanks for the catch :) – Matchu Jul 08 '11 at 14:21

score 13 · Answer 2 · answered Oct 17 '15 at 06:51

13

If you don't want to use PDO or other complicated solutions use implode function and you are all set.

$friendsArray  = array("zac1987", "peter", "micellelimmeizheng1152013142");
$friendsArray2 = "'" .implode("','", $friendsArray  ) . "'"; 
$query120      = "SELECT picturemedium FROM users WHERE username IN ( $friendsArray2 )";
echo $query120;

Solution : I just imploded your $friendArray by ',' that's it. plain and simple! and It's working.

Output : SELECT picturemedium FROM users WHERE username IN ('zac1987','peter','micellelimmeizheng1152013142')

answered Oct 17 '15 at 06:51

Jignesh Bhavani

383
2
14

Very simple solution and in my case very timely, Thanks – Mario Mar 13 '18 at 15:45
Not very clean though & it might get a bit buggy, when the $friendsArray would be an empty array, the condition would end up beign IN ('') and that's definitely not desirable. – Lukáš Jelič Jul 02 '18 at 14:27

score 7 · Answer 3 · answered Dec 06 '12 at 21:10

Everything is easy if you only have numbers. But if you have strings you need to check for quotes and be careful.. If you don't want to use PDO or "mysql_real_escape_string", following code is OK. I tested, this works well.

$friendsArray = array("zac1987", "peter", "micellelimmeizheng1152013142");
$friendsArray2 = '"' . implode('","', $friendsArray) . '"';
$query120 = "SELECT picturemedium FROM users WHERE username IN ($friendsArray2)";
echo $query120;

score 5 · Answer 4 · answered Feb 23 '12 at 16:27

5

Found this question on Google and by doing so figured out a solution. Not sure how "proper" this solution is, but it worked for me.

    $friendsArray = array("zac1987", "peter", "micellelimmeizheng1152013142");
    $friendsArray2 = "'" . join(', ',$friendsArray) . "'";  
    $query120 = "SELECT picturemedium FROM users WHERE username IN ($friendsArray2)";
    echo $query120;

answered Feb 23 '12 at 16:27

Jeremy D

105
3
9

It has the same problem that is also presented above. Check Matchu's answer for real solution. – Jasmo Feb 23 '12 at 20:11
2

This appears to have a not add the middle single quotes. I believe what Jeremy meant to post is: $friendsArray2 = "'" . join("', '",$friendsArray) . "'"; – useSticks Aug 26 '15 at 20:43
The middle single quotes are missing (in case of numbers), should be `$friendsArray2 = "'" . join("', '",$friendsArray) . "'"; ` – Silvan Jul 12 '16 at 12:17

score 0 · Answer 5 · answered Sep 06 '15 at 21:33

Just had to do something very similar. This is a very simple way of doing it that I figured out after much headache.

$friendsArray = array("zac1987", "peter", "micellelimmeizheng1152013142");
$friendsArray2 = implode("','",$friendsArray);

this will add quotes in between each element in the array but not at the very beginning or the very end Therefore: $friendsArray2 = "zac1987','peter','micellelimmeizheng1152013142" so now all you are missing is the single quote before the z in zac1987 and at the very end after 3142 to fix this just wrap $friendsArray2 in single quotes within your SELECT statement.

Its been tried, tested and true.

$query120 = "SELECT picturemedium FROM users WHERE username IN ('$friendsArray2')";
echo $query120;

OUTPUT: SELECT picturemedium FROM users WHERE username IN ('zac1987', 'peter', 'micellelimmeizheng1152013142')

score 0 · Answer 6 · answered Feb 06 '22 at 06:48

When using "IN" logical operator with strings, each string should be wrapped with quotation marks. This is not necessary if all values are numeric values.

$friendsArray = array("zac1987", "peter", "micellelimmeizheng1152013142");
$friendsArray2 = join("','",$friendsArray);  
$query120 = "SELECT picturemedium FROM users WHERE username IN ('{$friendsArray2}')";
echo $query120;

The output should be :

SELECT picturemedium FROM users WHERE username IN ('zac1987','peter','micellelimmeizheng1152013142')

mysql WHERE IN array string / username

6 Answers6

Linked