How to Properly Concatenate Variables with Prepared Statements in SQL?

Concatenating Variables with Prepared Statements

When using prepared statements, it's essential to properly concatenate variables with the SQL query. Incorrect syntax can lead to errors, such as those encountered in the following code fragments:

$sql = 'SELECT * FROM `users` WHERE username LIKE \'%{$var}%' '; // Error: Number of variables doesn't match number of parameters
$sql = 'SELECT * FROM `users` WHERE username LIKE %{?}% '; // Error: Wrong SQL

The Correct Approach

To avoid these errors, use the following steps:

1. Concatenate your parameter with wildcards outside the prepared statement:

$likeVar = "%" . $yourParam . "%";

2. Prepare the query using ? as the placeholder:

$stmt = $mysqli->prepare("SELECT * FROM REGISTRY where name LIKE ?");

3. Bind the parameter to the prepared statement:

$stmt->bind_param("s", $likeVar);

In this example, $likeVar contains the value to be searched with wildcards ("%...%"). Binding it to the s (string) parameter type ensures it's properly handled in the database query.

Case Insensitivity

If you require case-insensitive searching, you can add the COLLATE utf8mb4_bin clause to your query:

$stmt = $mysqli->prepare("SELECT * FROM REGISTRY where name LIKE ? COLLATE utf8mb4_bin");

The above is the detailed content of How to Properly Concatenate Variables with Prepared Statements in SQL?. For more information, please follow other related articles on the PHP Chinese website!