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?

Answer 1

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

Answer 2

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;

Answer 3

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;

Answer 4

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

Answer 5

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