准备好的语句"WHERE .. IN(..)"查询和排序-使用MySQL [英] A prepared statement, `WHERE .. IN(..)` query and sorting — with MySQL

问题描述

假设我们有一个查询:

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

和要获取的ID数组:$ids = array(1,5,18,25)

and an array of IDs to fetch: $ids = array(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?

推荐答案

您可以这样操作:

$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

使用此方法，您将为每个id调用bind_param，并且已由mysql进行排序.

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

这篇关于准备好的语句"WHERE .. IN(..)"查询和排序-使用MySQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！