Tell me more ×
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
2

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 fail because usernames are not wrapped by single quote like 'zac1987', 'peter', 'mice...'. How to wrap each username with single quote?

share|improve this question
Consider adding the Perl or PHP tag to this question to reach a larger audience. – tomlogic Jul 7 '11 at 21:59
add comment (requires an account with 50 reputation)

3 Answers

up vote 2 down vote accepted

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);
share|improve this answer
I have tested mysql_real_escape_string, the output is still the same as 'zac1987, peter, micellelimmeizheng1152013142' – zac1987 Jul 7 '11 at 22:10
Yes, that should be the case here. What you need is $friendsArray[$key] = "'".mysql_real_escape_string($friend)."'";. – Michael Mior Jul 7 '11 at 22:15
WOW! It works!! Thank you very much :) – zac1987 Jul 7 '11 at 22:21
@Michael: derp. Been a while since I used the standard MySQL functions xD Thanks for the catch :) – Matchu Jul 8 '11 at 14:21
add comment (requires an account with 50 reputation)
up vote 1 down vote

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;
share|improve this answer
It has the same problem that is also presented above. Check Matchu's answer for real solution. – Jasmo Feb 23 '12 at 20:11
add comment (requires an account with 50 reputation)
up vote 0 down vote

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;
share|improve this answer
add comment (requires an account with 50 reputation)

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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