How to Correctly Use PDO Prepared Statements for MySQL LIKE Queries?

PHP PDO Prepared Statement: MySQL LIKE Query

When performing a LIKE query using PHP's PDO class, it's essential to correctly handle the LIKE operator and prepare the statement properly.

Problem: In the provided code, the original query worked using the MySQL client but encountered issues when migrated to PHP.

Solution: The error lies in the WHERE clause within the prepare method. The following lines are incorrect:

<code class="php">$ret = $prep->execute(array(':searchTerm' => '"%' . $searchTerm . '"%'));
$ret = $prep->execute(array(':searchTerm' => "%:searchTerm%"));
$ret = $prep->execute(array(':searchTerm' => ':' . $searchTerm . '%'));</code>

Explanation:

• Prepared statements: They transport data separately from the query, making quotes unnecessary when embedding values.
• LIKE operator: Requires a wildcard character, such as % or _, to represent arbitrary character sequences.
• Correct query: The correct WHERE clause should be:

<code class="php">WHERE hs.hs_text LIKE :searchTerm</code>

And the prepared statement should be executed as follows:

<code class="php">$ret = $prep->execute(array(':searchTerm' => '%' . $searchTerm . '%'));</code>

Now, the query should return the desired results.

The above is the detailed content of How to Correctly Use PDO Prepared Statements for MySQL LIKE Queries?. For more information, please follow other related articles on the PHP Chinese website!