php mysql WHERE IN 数组字符串/用户名

mysql WHERE IN array string / username

提问by zac1987

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?

它失败了，因为用户名没有被像“zac1987”、“peter”、“mice...”这样的单引号包裹起来。每个用户名如何用单引号括起来？

回答by Matchu

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::quotewith mysql_real_escape_string.

我将建议您使用实际的 MySQL 转义函数，而不是仅仅用引号括起来，以确保数据实际上正确地进入查询。（否则，如果我进入了一个名字一样It's me!，单引号会搞乱了查询。）我要在这里假设你正在使用PDO（你应该！），但是，如果没有，请更换引用PDO::quote用mysql_real_escape_string。

foreach($friendsArray as $key => $friend) {
  $friendsArray[$key] = PDO::quote($friend);
}

$friendsArray2 = join(', ', $friendsArray);

回答by Jignesh Bhavani

If you don't want to use PDO or other complicated solutions use implode function and you are all set.

如果您不想使用 PDO 或其他复杂的解决方案，请使用内爆功能，一切就绪。

$friendsArray  = array("zac1987", "peter", "micellelimmeizheng1152013142");
$friendsArray2 = "'" .implode("','", $friendsArray  ) . "'"; 
$query120      = "SELECT picturemedium FROM users WHERE username IN ( $friendsArray2 )";
echo $query120;

Solution :I just imploded your $friendArrayby ','that's it. plain and simple! and It's working.

解决方案：我只是崩盘您$friendArray通过 ','就是这样。干净利落！它正在工作。

Output: SELECT picturemedium FROM users WHERE username IN ('zac1987','peter','micellelimmeizheng1152013142')

输出： SELECT picturemedium FROM users WHERE username IN ('zac1987','peter','micellelimmeizheng1152013142')

回答by trante

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.

如果你只有数字，一切都很容易。但是如果你有字符串，你需要检查引号并小心..如果你不想使用 PDO 或“mysql_real_escape_string”，下面的代码是可以的。我测试过，这很好用。

$friendsArray = array("zac1987", "peter", "micellelimmeizheng1152013142");
$friendsArray2 = '"' . implode('","', $friendsArray) . '"';
$query120 = "SELECT picturemedium FROM users WHERE username IN ($friendsArray2)";
echo $query120;

回答by Jeremy D

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;

回答by Kevin Haines

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.

这将在数​​组中的每个元素之间添加引号，但不会在开头或结尾添加引号因此：$friendsArray2 = "zac1987','peter','micellelimmeizheng1152013142" 所以现在你缺少的是 z 之前的单引号在 zac1987 和 3142 之后的最后修复此问题，只需在 SELECT 语句中将 $friendsArray2 用单引号括起来即可。

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

输出：SELECT picturemedium FROM users WHERE username IN ('zac1987', 'peter', 'micellelimmeizheng1152013142')