How to Properly Use Wildcards with PDO Prepared Statements in MySQL?

Using Wildcards in PDO Prepared Statements

Executing MySQL queries that employ wildcards, such as % to match any number of characters, can pose challenges when using PDO prepared statements. This article addresses how to effectively utilize wildcards with prepared statements.

Original Query

The initial goal was to execute the following query:

SELECT * FROM `gc_users` WHERE `name` LIKE '%anyname%'

Unsuccessful Attempts

Two unsuccessful attempts were made to use prepared statements:

$stmt = $dbh->prepare("SELECT * FROM `gc_users` WHERE `name` LIKE :name");
$stmt->bindParam(':name', "%" . $name . "%");
$stmt->execute();

$stmt = $dbh->prepare("SELECT * FROM `gc_users` WHERE `name` LIKE '%:name%'");
$stmt->bindParam(':name', $name);
$stmt->execute();

Solution

The issue resides in the use of bindParam(). To correctly use wildcards in prepared statements, you should use bindValue(). The code below demonstrates the correct approach:

$stmt = $dbh->prepare("SELECT * FROM `gc_users` WHERE `name` LIKE :name");
$stmt->bindValue(':name', '%' . $name . '%');
$stmt->execute();

Alternative

Another viable option is to modify the code to use bindParam() in the following manner:

$name = "%$name%";
$stmt = $dbh->prepare("SELECT * FROM `gc_users` WHERE `name` LIKE :name");
$stmt->bindParam(':name', $name);
$stmt->execute();

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