current community

your communities

more stack exchange communities

company blog
Stack Exchange Inbox Reputation and Badges
tour help
Join the Stack Overflow Community
Stack Overflow is a community of 6.4 million programmers, just like you, helping each other.
Join them; it only takes a minute:
Sign up
up vote 6 down vote favorite
4

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
up vote 9 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
1  
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
up vote 5 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
1  
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 at 12:17
up vote 5 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
up vote 3 down vote

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

share|improve this answer
up vote 0 down vote

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

share|improve this answer
up vote 0 down vote

select find_in_set('userNameSearched','zac1987, peter, micellelimmeizheng1152013142')

http://www.w3resource.com/mysql/string-functions/mysql-find_in_set-function.php

share|improve this answer
    
Please consider editing your post to add more explanation about what your code does and why it will solve the problem. An answer that mostly just contains code (even if it's working) usually wont help the OP to understand their problem. – SuperBiasedMan Nov 26 '15 at 14:42

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.