事先准备好的声明，“在哪里……”使用MySQL进行查询和排序

Imagine we have a query:

假设我们有一个查询:

SELECT * FROM somewhere WHERE `id` IN(1,5,18,25) ORDER BY `name`;

and an array of IDs to fetch: $ids = array(1,5,18,25)

要取回的id数组:$id =数组(1、5、18、25)

With prepared statements it's adviced to prepare one statement and call it multiple times:

在事先准备好的声明中，建议准备一份声明，并多次调用:

$stmt = $mysqli->prepare('SELECT * FROM somewhere WHERE `id`=?;');
foreach ($ids as $id){
    $stmt->bind_params('i', $id);
    $stmt->exec();
    }

But now I'll have to sort the results manually. Do I have any nice alternatives?

但是现在我必须手动对结果进行排序。我有更好的选择吗?

7 个解决方案

#1

you could do it this way:

你可以这样做:

$ids = array(1,5,18,25);

// creates a string containing ?,?,? 
$clause = implode(',', array_fill(0, count($ids), '?'));


$stmt = $mysqli->prepare('SELECT * FROM somewhere WHERE `id` IN (' . $clause . ') ORDER BY `name`;');

call_user_func_array(array($stmt, 'bind_param'), $ids);
$stmt->execute();

// loop through results

Using this you're calling bind_param for each id and you have sorting done by mysql.

使用这个，您将为每个id调用bind_param，并由mysql进行排序。

#2

I believe this is the simplest possible answer :

我相信这是最简单的答案:

$ids = [1,2,3,4,5];
$pdos = $pdo->prepare("SELECT * FROM somwhere WHERE id IN (:"
        . implode(',:', array_keys($ids)) . ") ORDER BY id");

foreach ($ids as $k => $id) {
    $pdos->bindValue(":". $k, $id);
}

$pdos->execute();
$results = $pdos->fetchAll();

So long your array of Ids does not contain keys or keys with illegal characters, it wil work.

只要您的id数组不包含带有非法字符的键或键，它就会工作。

#3

I'll add an ultimately slow & ugly solution which nevertheless uses prepared statements for ANY number of array items :) 3 statements are universal for any case and can be reused everywhere.

我将添加一个最终缓慢且丑陋的解决方案，它仍然为任意数量的数组项使用准备好的语句:)3语句对于任何情况都是通用的，可以在任何地方重用。

CREATE TEMPORARY TABLEids(idINT );
创建临时TABLEids(idINT);
INSERT INTOidsVALUES(?); this will insert your IDs
插入INTOidsVALUES(?);这将插入您的id。
SELECTidFROMidsLEFT JOIN .... ; use data from other tables to sort the ids list
SELECTidFROMidsLEFT加入....;使用来自其他表的数据对ids列表进行排序。
SELECTidFROMids; select everything back
SELECTidFROMids;选择所有的回

Otherwise you'll have to use IN (?,?,?,.... or sort the rows manually. The best idea is to use simple MySQL-queries, or, try to get the list of IDs already sorted in the way you like.

否则你将不得不使用(?,?,?,....或者手动排序行。最好的方法是使用简单的mysql查询，或者尝试以您喜欢的方式获得已排序的id列表。

#4

Had the same problem and in addition to the answer of @sled 7 years ago, here is a possibility without making the call_user_func_array(array($stmt, 'bind_param'), $ids); step, but only call bind_params once:

如果有相同的问题，并且除了7年前@sled的答案之外，这里还有一种可能，无需调用call_user_func_array(数组($stmt， 'bind_param')， $id);步骤，但只调用bind_params一次:

$ids = array(1,5,18,25);

// creates a string containing ?,?,? 
$bindClause = implode(',', array_fill(0, count($ids), '?'));
//create a string for the bind param just containing the right amount of iii
$bindString = str_repeat('i', count($ids));

$stmt = $mysqli->prepare('SELECT * FROM somewhere WHERE `id` IN (' . $bindClause . ') ORDER BY `name`;');

$stmt->bind_params($bindString, ...$ids);
$stmt->execute();

#5

An alternative would be to use PHP usort function on the result object, but this is "manual."

另一种方法是在结果对象上使用PHP usort函数，但这是“手工的”。

See this: Sort Object in PHP

请参见:在PHP中排序对象

#6

No, it's not recommended, if you are to fetch certain records from the database using ORDER BY clause.

不，如果要使用ORDER BY子句从数据库中获取某些记录，则不建议这样做。

#7

Have you considered rewriting you original query using a JOIN and WHERE clause to get the IDS you need to avoid the need for a WHERE IN clause? I came here with the same question and after reviewing the possible solutions I realized an INNER JOIN was my solution.

您是否考虑过使用JOIN和WHERE子句重写原始查询以获取id，以避免使用WHERE子句?我带着同样的问题来到这里，在检查了可能的解决方案之后，我意识到我的解决方案是内部连接。

#1