Skip to content

How to Bind Parameters in LIKE Clause Properly in MySQL

Some developers might have problems on using bind parameters in LIKE clauses. Here I demonstrate wrong and right statements.

This is a wrong statement.

<?php
...
$query = 'smith';
$statement = "select full_name from customers where full_name like '%:query%'";
$stmt = $dbh -> prepare($statement);
$stmt -> bindParam(':query', $query);
$stmt -> execute();
$result = $stmt -> fetchAll();
...

The statement is syntactically correct, but using the bind parameter improperly and nothing will be fetched.

This is a right version.

<?php
...
$statement = "select full_name from customers where full_name like concat('%', :query, '%')";
...

We use the function concat() to compose the LIKE-string. It's clear and understandable.

We can also pad '%' to the original input variable $query on the both side. This might be a better version.

<?php
...
$query = '%' . $query . '%';
$statement = "select full_name from customers where full_name like :query";
...
Tags:

Leave a Reply

Your email address will not be published. Required fields are marked *