The %
wrapping goes around the parameters, not the placeholders.
My snippet will be using object-oriented mysqli syntax instead of the procedural syntax that your code demonstrates.
First you need to set up the necessary ingredients:
- the WHERE clause expressions — to be separated by ORs
- the data types of your values — your values are strings, so use “s”
- the parameters to be bound to the prepared statement
I am going to combine #2 and #3 into one variable for simpler “unpacking” with the splat operator (...
). The data type string must be the first element, then one or more elements will represent the bound values.
As a logical inclusion, if you have no conditions in your WHERE clause, there is no benefit to using a prepared statement; just directly query the table.
Code: (100% Tested / Successful Code)
$string = "my name";
$conditions = [];
$parameters = [''];
foreach (array_unique(explode(' ', $string)) as $value) {
$conditions[] = "name LIKE ?";
$parameters[0] .= 's';
$parameters[] = "%{$value}%";
}
// $parameters now holds ['ss', '%my%', '%name%']
$query = "SELECT * FROM info";
if ($conditions) {
$stmt = $conn->prepare($query . ' WHERE ' . implode(' OR ', $conditions));
$stmt->bind_param(...$parameters);
$stmt->execute();
$result = $stmt->get_result();
} else {
$result = $conn->query($query);
}
foreach ($result as $row) {
echo "<div>{$row['name']} and whatever other columns you want</div>";
}
For anyone looking for similar dynamic querying techniques: