How to Properly Use MySQLi Prepared Statements with the IN Operator?

MySQLi Prepared Statements with IN Operator

This article addresses the issue of selecting rows from a database using the IN operator and prepared statements in MySQLi. The initial code provided by the original poster:

$data_res = $_DB->prepare('SELECT `id`, `name`, `age` FROM `users` WHERE `lastname` IN (?)');
$data_res->bind_param('s', $in_statement);
$data_res->execute();

failed to return any results, despite the data existing in the database.

The solution involves manually binding each parameter by reference:

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

foreach($lastnames as $key => $value)
    $arParams[] = &$lastnames[$key];

$count_params = count($arParams);

$int = str_repeat('i',$count_params);
array_unshift($arParams,$int);

$q = array_fill(0,$count_params,'?');
$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();

This approach allows for the correct execution of the prepared statement with the IN operator, ensuring that all data matching the input array is returned from the database.

The above is the detailed content of How to Properly Use MySQLi Prepared Statements with the IN Operator?. For more information, please follow other related articles on the PHP Chinese website!