問題描述

我必須使用 IN 運(yùn)算符從數(shù)據(jù)庫中選擇一些行.我想使用準(zhǔn)備好的語句來做到這一點(diǎn).這是我的代碼:

I have to select some rows from the database using IN operator. I want to do it using prepared statement. This is my code:

<?php
$lastnames = array('braun', 'piorkowski', 'mason', 'nash');
$in_statement = '"' . implode('", "', $lastnames) . '"'; //"braun", "piorkowski", "mason", "nash"

$data_res = $_DB->prepare('SELECT `id`, `name`, `age` FROM `users` WHERE `lastname` IN (?)');
$data_res->bind_param('s', $in_statement);
$data_res->execute();
$result = $data_res->get_result();
while ($data = $result->fetch_array(MYSQLI_ASSOC)) {
    ...
}
?>

雖然數(shù)據(jù)庫中所有數(shù)據(jù)都存在，但是什么都不返回.

還有一點(diǎn):如果我直接通過$in_statement來查詢執(zhí)行，會(huì)返回?cái)?shù)據(jù).所以問題出現(xiàn)在準(zhǔn)備階段.

我在谷歌中尋找問題，但沒有成功.我的代碼有什么問題?
感謝您的幫助！

But returns nothing although all data exists in the database.

And one more: if i pass $in_statement directly to query and execute it, the data will be returned. So the problem appears on preparing.

I was looking for the question in Google but it wasn't' successful. What's wrong with my code?
Thanks for the help!

推薦答案

我最近為我的問題找到了解決方案.也許這不是最好的方法，但它很好用！證明我錯(cuò)了:)

I've recently found the solution for my question. Maybe it's not the best way to do it, but it works nice! Prove me wrong:)

<?php
$lastnames = array('braun', 'piorkowski', 'mason', 'nash');
$arParams = array();

foreach($lastnames as $key => $value) //recreate an array with parameters explicitly passing every parameter by reference
    $arParams[] = &$lastnames[$key];

$count_params = count($arParams);

$int = str_repeat('i',$count_params); //add type for each variable (i,d,s,b); you can also determine type of the variable automatically (is_int, is_float, is_string) in loop, but i don't need it
array_unshift($arParams,$int); 

$q = array_fill(0,$count_params,'?'); //form string of question marks for statement
$params = implode(',',$q);

$data_res = $_DB->prepare('SELECT `id`, `name`, `age` FROM `users` WHERE `lastname` IN ('.$params.')');
call_user_func_array(array($data_res, 'bind_param'), $arParams);
$data_res->execute();
$result = $data_res->get_result();
while ($data = $result->fetch_array(MYSQLI_ASSOC)) {
    ...
}

$result->free();
$data_res->close();
?>

這篇關(guān)于MySQLi 準(zhǔn)備語句與 IN 運(yùn)算符的文章就介紹到這了，希望我們推薦的答案對(duì)大家有所幫助，也希望大家多多支持html5模板網(wǎng)！