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?

Answer 1

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

Answer 2

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