How to Efficiently Build Dynamic LIKE Queries with mysqli Prepared Statements?

Dynamic LIKE Queries Using mysqli Prepared Statements

This question addresses the issue of creating a prepared statement with a variable number of LIKE conditions based on user input. The provided PHP code attempts to construct the statement, but there is an error related to the formatting of the LIKE clauses.

The key problem lies in where the percent signs (%) are placed around the parameters (?) in the LIKE clauses. Instead of wrapping the parameters, the percent signs should go around the placeholders, as shown below:

foreach ( $search_exploded as $search_each ) {
    $x ++;
    if ( $x == 1 ) {
        $construct .= "name LIKE %??%";
    } else {
        $construct .= " or name LIKE %??%";
    }
}

This correction ensures that the parameter values (e.g., "my name") are correctly bound to the LIKE clauses.

Additionally, the code uses a concatenated string ($construct) to build the WHERE clause. However, a more efficient and secure approach is to use a bind_param() placeholder array for all parameters, as shown below:

$where_params = [];
foreach ( $search_exploded as $search_each ) {
    $where_params[] = "%{$search_each}%";
}

$query = "SELECT * FROM info WHERE name LIKE ?";
$stmt = mysqli_prepare( $conn, $query );
mysqli_stmt_bind_param( $stmt, "s", ...$where_params );

This method gracefully handles any number of LIKE conditions and eliminates the risk of SQL injection vulnerabilities.

With these modifications, the dynamic LIKE query can correctly search for records based on multiple user-defined criteria.

The above is the detailed content of How to Efficiently Build Dynamic LIKE Queries with mysqli Prepared Statements?. For more information, please follow other related articles on the PHP Chinese website!