MySQLi Prepared Statements with the IN Operator

Prepared statements offer enhanced security and performance over traditional queries by allowing you to specify parameter values separately from the statement itself. However, when it comes to using prepared statements with the IN operator, you may encounter issues if your approach is not correct.

The Problem

As mentioned in the initial query, binding a comma-separated string of values to the prepared statement using bind_param('s', $in_statement) will not yield results, even though the data exists in the database.

The Solution

The solution lies in treating each parameter value individually:

1. Create an array with numbered keys and pass values to it explicitly by reference.
2. Count the number of parameters and create a string with the appropriate number of i characters, representing their data type.
3. Combine the data type string with an array of question marks to form the parameter string for the prepared statement.
4. Unshift the data type string into the array of parameter values.
5. Use call_user_func_array to bind the array of parameter values to the prepared statement.

Example Code

 $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 = $mysqli->prepare("SELECT `id`, `name`, `age` FROM `users` WHERE `lastname` IN ({$params})");
call_user_func_array([$data_res, 'bind_param'], $arParams);
$data_res->execute();
$result = $data_res->get_result();

while ($data = $result->fetch_array(MYSQLI_ASSOC)) {
    // Your code here...
}

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

This approach allows you to properly bind multiple values to the prepared statement using the IN operator.