mysql WHERE IN array string / username [duplicate]

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?

Matchu · Accepted Answer · 2011-07-07 22:01:05Z

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 7, 2011 at 22:01

Matchu

86.1k18 gold badges155 silver badges160 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

zac1987 Over a year ago

I have tested mysql_real_escape_string, the output is still the same as 'zac1987, peter, micellelimmeizheng1152013142'

Michael Mior Over a year ago

Yes, that should be the case here. What you need is $friendsArray[$key] = "'".mysql_real_escape_string($friend)."'";.

Matchu Over a year ago

@Michael: derp. Been a while since I used the standard MySQL functions xD Thanks for the catch :)

Jignesh Bhavani · Accepted Answer · 2015-10-17 06:51:37Z

14

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, 2015 at 6:51

Jignesh Bhavani

3932 silver badges14 bronze badges

2 Comments

Mario Over a year ago

Very simple solution and in my case very timely, Thanks

Lukáš Jelič Over a year ago

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.

trante · Accepted Answer · 2012-12-06 21:10:49Z

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;

Jeremy D · Accepted Answer · 2012-02-23 16:27:29Z

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, 2012 at 16:27

Jeremy D

1053 silver badges9 bronze badges

3 Comments

Jasmo Over a year ago

It has the same problem that is also presented above. Check Matchu's answer for real solution.

useSticks Over a year ago

This appears to have a not add the middle single quotes. I believe what Jeremy meant to post is: $friendsArray2 = "'" . join("', '",$friendsArray) . "'";

Silvan Over a year ago

The middle single quotes are missing (in case of numbers), should be $friendsArray2 = "'" . join("', '",$friendsArray) . "'";

Kevin Haines · Accepted Answer · 2015-09-06 21:33:42Z

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')

Jaadu · Accepted Answer · 2022-02-06 06:48:18Z

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')

Collectives™ on Stack Overflow

mysql WHERE IN array string / username [duplicate]

6 Answers 6

3 Comments

2 Comments

Comments

3 Comments

Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

3 Comments

2 Comments

Comments

3 Comments

Comments

Comments

Linked

Related