php mysql选择匹配id数组的多行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/20472794/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
mysql selecting multiple rows that match array of ids
提问by Vahe Shadunts
First of all:
首先：
SELECT `key`, `value` FROM settings
WHERE `key` = :base_url
OR `key` = :google_analytics
OR `key` = :site_domain
Is this correct way of selecting multiple rows? Or is there a better way? Because it is not really OR, but all of them... So it just doesn't sound right.
这是选择多行的正确方法吗？或者，还有更好的方法？因为它不是真的OR，而是所有的......所以它听起来不对。
Question number 2:
问题 2：
This is my query that selects users with pagination:
这是我选择分页用户的查询：
SELECT id, email, verified, level, name_surname, age, sex, profession, education, location, privacy, suspend FROM users LIMIT :start, :results
Sometimes I want to pass array of users to return to this query, like array(1,2,3,4,5).
有时我想传递用户数组以返回此查询，例如array(1,2,3,4,5).
Obviously simply adding WHERE $arraydoesn't work. So how would I do that?
显然，简单地添加WHERE $array是行不通的。那我该怎么做呢？
Sometimes I don't want to pass this array of ids, so how do I switch between state when there is id list and when there is not? Do I simply create php if else on prepare statement? That seems obvious, but it is better to ask about best practices :)
有时我不想传递这个 id 数组，那么如何在有 id 列表和没有 id 列表时在状态之间切换？我是否只是在准备语句中创建 php if else ？这似乎很明显，但最好询问最佳实践:)
回答by Vahe Shadunts
To obtain multiple values from the whereclause, there is no need to use OR. Use in, like in the query below:
要从where子句中获取多个值，无需使用OR. 使用in，就像在下面的查询中一样：
SELECT `key`, `value` FROM settings
WHERE `key` in ( :base_url, :google_analytics, :site_domain);
This is the same as the first:
这与第一个相同：
SELECT * FROM users where id in (1,2,3,4,5);
So if you have an array of user ids, you must implodeit with the ','glue characters.
因此，如果您有一组用户 ID，则必须implode使用','胶水字符。
Here's a complete example with php:
这是一个完整的 php 示例：
<?php
$users = array(1,2,3,4,5);
$usersStr = implode(',', $users); // returns 1,2,3,4,5
$sql = "SELECT * FROM users where id in ({$userStr})";
....
回答by Explosion Pills
Question 1
问题 1
I'm not sure that I understand what you mean by all of them, but if you use ORit means "any of these conditions can be true, even if multiple of them are true." This is different from the common English usage of "or," which would be exclusive.
我不确定我是否理解你的意思all of them，但如果你使用OR它意味着“这些条件中的任何一个都可以是真的，即使它们中有多个是真的。” 这与“或”的常见英语用法不同，后者是排他性的。
If you want all conditions to be true simultaneously, use AND.
如果您希望所有条件同时为真，请使用AND。
It is also simpler and more efficient to use INrather than ORwhen comparing to multiple conditions at the same time as in your query.
它也更简单，更有效地使用IN，而不是OR在同一时间在你的查询与多个条件时。
Question 2
问题2
A prepared statement, `WHERE .. IN(..)` query and sorting — with MySQL
一个准备好的语句，`WHERE .. IN(..)` 查询和排序——使用 MySQL
Essentially you should always have an array even if you pass in a scalar value:
本质上，即使您传入一个标量值，您也应该始终拥有一个数组：
$values = (array)$argument;
$conditions = implode(",", array_fill(0, count($values), "?");
// assuming you're using PDO
$stmt->execute($conditions);
回答by Snowwolf
For question 1, I think @Ronald have already given you a link to refer to;
对于问题 1，我认为@Ronald 已经给了您一个参考链接；
For question 2, I think you can do it in this way:
对于问题2，我认为你可以这样做：
public function myquery($condition){
if is_string($condition){
//do something to compose the query for string
}
if is_array($condition){
//do something to compose the query for array
}
}
