16

How do you build up a LIKE condition in a db_select() query?

I know how to do this with a db_query() API call, but is there a special syntax / method for doing this with db_select()?

2
  • 2
    Just for future reference, anything that you can pass to db_query as an argument placeholder, you can also pass as an argument to ...Query::condition. You won't find documentation for each individual operator for each different type of query, as it wouldn't make sense to do that. It all goes through PDO at the end of the day, if db_like prepares a variable correctly for db_query, then by definition it will prepare the same variable correctly for db_select
    – Clive
    May 8, 2014 at 9:07
  • 2
    Before anyone suggests it, unless you can find question about db_select + LIKE, I don't think we have a dup of this. We have, db_query + LIKE, but even though the answer is the same, the question is different.
    – mpdonadio
    May 8, 2014 at 11:04

6 Answers 6

22

After digging through Drupal Documentation found a solution in db_like API documentation page and SelectQuery:condition handler doc page..

For example

$result = db_select('field_data_field_name', 'f')->fields('f', array(
    'entity_id',
    'field_name_value'
))
->condition('entity_type', 'user')
->condition('bundle', 'user')
->condition('deleted', 0)
->condition('field_name_value', '%' . db_like($last_item) . '%', 'LIKE')
->distinct()
->range(0, 10)
->execute();

Condition to use for like query is

->condition('field_name_value', '%' . db_like($last_item) . '%', 'LIKE')
2
  • 2
    The two things to notice here are that (1) db_like() is needed to properly escape the argument, and (2) you need to explicitly add the wildcard characters. It will also use the default collation on the database, and I don't think there is any way to specify a different one.
    – mpdonadio
    May 8, 2014 at 11:08
  • 1
    I may take a pass later today at making this a little more comprehensive. I can never remember the DB API, and was literally looking up the same thing yesterday.
    – mpdonadio
    May 8, 2014 at 11:24
4

In Drupal 8 and higher versions, you need to use Drupal\Core\Database\Database, as in the following code.

$database = Database::getConnection();
$query = $database->select('TABLE NAME', 'u')
    ->fields('u', array('column1','column2'));
$query->condition('column1', '%'.$database->escapeLike($search_phrase) . '%', 'LIKE');

Alternatively, you can use this code.

$db_or = $query->orConditionGroup()
 ->condition('column1', '%' . $database->escapeLike($search_phrase) . '%', 'LIKE')
 ->condition('column2', $database->escapeLike($search_phrase) . '%', 'LIKE')
 ->condition('column1', '%' . $database->escapeLike($search_phrase), 'LIKE');
$query->condition($db_or);
$result = $query->execute();
1
  • I prefer this solution, since it's using the built in database function (escapeLike) instead of a global "db_like()". Also mentioning all the three possibilities.
    – ssibal
    Dec 13, 2016 at 12:16
1

For using "like" in db_select is as below and worked for me...

$search_value = "[email protected]";
$query = db_select('TABLE NAME', 'u');
$query->fields('u', ['ROW1', 'ROW2']);
$query->condition('ROW1', '%' . db_like($search_value) . '%', 'LIKE');
$result = $query->execute();

Here exact value in ROW1 is "[email protected]" and by using LIKE condition in db_select I got the output as "[email protected]".

1

The following code works in Drupal 8.x, but it won't work in Drupal 9.x, as db_like() has been removed.

$connection = \Drupal::database();
$query = $connection ->select('node', 'n');
$query->fields('n');
$query->condition('nid', '%'.db_like($exp), 'LIKE');
$records = $query->execute();
0

For code that injected the database service in $this->database. the following is the code I would use in Drupal 9.x.

$escaped = $this->database->escapeLike($input);
$query = $this->database->select('zipcodes', 'z');
$query->fields('z', ['city', 'zip', 'state']);
$orGroup = $query->orConditionGroup()
  ->condition('city', '%' . $escaped . '%', 'LIKE')
  ->condition('z.zip', '%' . $escaped . '%', 'LIKE');

$query->condition($orGroup);
$query->range(0, 10);
$result = $query->execute();
if (!empty($result)) {
  foreach ($result as $record) {
    $string = $record->city . ' ' .$record->state. ' - ' .$record->zip;
    $value_string = $record->city. '~' .$record->state. '~' .$record->zip;
    $data[] = [
      'label' => $string,
      'value' => $value_string,
    ];
  }
}
0

Since db_like() is deprecated in Drupal 8.x and removed in Drupal 9.x, the documentation suggests to use Connection::escapeLike() instead.

The following code works in Drupal 8 and 9.

$connection = Database::getConnection();
$query = $connection->select('your_table_name', 'table_alias')
  ->fields('table_alias', ['whatever_field', 'other_field'])
  ->condition('my_field_name', '%' . $connection->escapeLike($yourSearchString) . '%', 'LIKE');

$result = $query->execute()->fetchAll();

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.