How to Use Wildcards with PDO Prepared Statements: Why bindParam() Fails and How to Use bindValue() or Pre-Formatted Strings?

Using Wildcards with PDO Prepared Statements

Executing SQL queries with wildcards using PDO (PHP Data Objects) prepared statements can be challenging. This article addresses the issue and provides solutions.

The question presented involves finding all rows in the gc_users table with a wildcard in the name field, which can be expressed as:

<code class="sql">SELECT * FROM `gc_users` WHERE `name` LIKE '%anyname%';</code>

When attempting to execute this query using prepared statements, two unsuccessful approaches were taken:

<code class="php">$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();</code>

The preferred solution involves using bindValue() instead of bindParam():

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

Alternatively, as suggested in the provided answer, bindParam() can also be used:

<code class="php">$name = "%$name%";
$query = $dbh->prepare("SELECT * FROM `gc_users` WHERE `name` like :name");
$query->bindParam(':name', $name);
$query->execute();</code>

These solutions demonstrate how to successfully use wildcards with PDO prepared statements.

The above is the detailed content of How to Use Wildcards with PDO Prepared Statements: Why bindParam() Fails and How to Use bindValue() or Pre-Formatted Strings?. For more information, please follow other related articles on the PHP Chinese website!