How to Build a Dynamic LIKE Condition SELECT Query in MySQLi Using Prepared Statements?

Building a SELECT Query with Dynamic LIKE Conditions as a MySQLi Prepared Statement

Problem

Users can input a variable number of search terms, and a corresponding query needs to be built dynamically based on those terms. The objective is to create a prepared statement that accommodates this dynamic input.

Solution

Constructing the Query Expression:

Wrap each search term in a LIKE condition with placeholders for binding values (%?).

Example:

$construct .= "name LIKE %?%";

Binding Parameters:

Pack the data types and input values into one array using the splat operator (...).

Example:

$parameters = ['sss', '%Bill%', '%N_d%', '%Dave%'];

Preparing and Executing the Statement:

Prepare the statement with the dynamic WHERE clause and bind the parameters.

$stmt = $mysqli->prepare('SELECT * FROM info WHERE ' . implode(' OR ', $conditions));
$stmt->bind_param(...$parameters);
$stmt->execute();

Retrieving Results:

Execute the query and fetch the results as needed.

$result = $stmt->get_result();
foreach ($result as $row) {
    echo '<div>' . $row['name'] . '</div>\n'; 
}

Example Corrected Code:

<?php
$string = "my name";
$search_exploded = explode(" ", $string);
$num = count($search_exploded);

$conditions = [];
$parameters = [''];
foreach (array_unique($search_exploded) as $value) {
    $conditions[] = "name LIKE ?";
    $parameters[0] .= 's';
    $parameters[] = "%{$value}%";
}

$query = "SELECT * FROM info";
if ($conditions) {
    $stmt = $mysqli->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'] . '</div>\n'; 
}

?>

The above is the detailed content of How to Build a Dynamic LIKE Condition SELECT Query in MySQLi Using Prepared Statements?. For more information, please follow other related articles on the PHP Chinese website!