本文介绍了一个准备好的语句,`WHERE .. IN(..)` 查询和排序——使用 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的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!

08-20 12:37
查看更多