本文介绍了一个准备好的语句,`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的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!