Mysqli prepared statement with multiple values for IN clause
Before running any query with mysqli, make sure you've got a properly configured mysqli connection variable that is required in order to run SQL queries and to inform you of the possible errors.
Imagine you've got an array of values that you want to use in the IN()
clause in your SQL query.
There are several tricks that would help us in this challenge.
- First of all we will need to create a string with as many
?
marks as many elements are in your array. For this we would usestr_repeat()
function which comes very handy for the purpose. - Then this string with comma separated question marks have to be added to the query. Although it's a variable, in this case it is safe as its contents contains only constant values
- then this query must be prepared just like any other query
- then we will need to create a string with types to be used with bind_param(). Note that there is usually no reason to use different types for the bound variables - mysql will happily accept them all as strings. There are edge cases, but extremely rare. For the everyday use you can always keep it simple and use "s" for the everything.
- then we need to bind our array values to the statement. Unfortunately, you cannot just write it as a single variable, like this
$stmt->bind_param("s", $array)
, only scalar variables are allowed inbind_param()
. Luckily, there is an argument unpacking operator that does exactly what we need - sends an array of values into a function as though it's a set of distinct variables! - the rest is as usual - execute the query, get the result and fetch your data!
Here is the code:
$array = [1,2,3]; // our array
$in = str_repeat('?,', count($array) - 1) . '?';
$sql = "SELECT * FROM table WHERE column IN ($in)";
$stmt = $mysqli->prepare($sql);
$types = str_repeat('s', count($array));
$stmt->bind_param($types, ...$array);
$Stmt->execute();
$result = $stmt->get_result(); // get the mysqli result
$data = $result->fetch_all(MYSQLI_ASSOC); // fetch data
Related articles:
- Mysqli examples
- How to run an INSERT query using Mysqli
- How to run an UPDATE query using Mysqli
- How to run a SELECT query using Mysqli
- Mysqli SELECT query with prepared statements
- How to create a search filter for mysqli
- Using mysqli prepared statements with LIKE operator in SQL
- How to call stored procedures with mysqli
- How to run 1000s insert queries with mysqli?
Add a comment
Please refrain from sending spam or advertising of any sort.
Messages with hyperlinks will be pending for moderator's review.
Markdown is now supported:
>
before and an empty line after for a quote