Properly Using LIKE with BindParam in MySQL PDO Query

When attempting to perform LIKE searches with bindParam in MySQL PDO queries, it's essential to use the correct syntax to ensure accurate results.

Optimized Syntax

To match usernames starting with "a" using bindParam, the correct syntax is:

$term = "a%";

In contrast, the syntax provided in the original question, "$term = "'$term%'", is incorrect as it places unnecessary inner single quotes around the $term value, which would result in searching for 'a%' instead of a%.

bindParam's Role

bindParam is responsible for automatically quoting string data when it's inserted into SQL statements. Therefore, appending single quotes manually is not necessary and can lead to incorrect results.

Revised Code

Using the optimized syntax, the revised code would be:

$term = "a%";

$sql = "SELECT username FROM `user` WHERE username LIKE :term LIMIT 10";      

$core = Connect::getInstance();

$stmt = $core->dbh->prepare($sql);
$stmt->bindParam(':term', $term, PDO::PARAM_STR);
$stmt->execute();
$data = $stmt->fetchAll();