14

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
    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 '14 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 '14 at 11:04
21

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
    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 '14 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 '14 at 11:24
1

You can also use Drupal\Core\Database\Database when creating "LIKE" query. This is Drupal 8 alternate syntax since db_select() is deprecated.

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

Or add multiples with OR query.

$DB_OR = $query->orConditionGroup()
  // find match anywhere in field
  ->condition('column1', '%' . $database->escapeLike($search_phrase) . '%', 'LIKE')

  // find match starting at beginning
  ->condition('column2', $database->escapeLike($search_phrase) . '%', 'LIKE');

  // find match at end of field
  ->condition('column1', '%' . $database->escapeLike($search_phrase), 'LIKE')
$query->condition($DB_OR); // Add OR object as condition
$result = $query->execute();
  • 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 '16 at 12:16
1

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

$search_value = "testvalue@xmail.com";
$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 "testvalue@xmail.com" and by using LIKE condition in db_select I got the output as "testvalue@xmail.com".

0

it works in Drupal8 Version 1

$exp = "1";
$connection = \Drupal::database();
$query = $connection ->select('node', 'n');
    $query->fields('n');
    $query->condition('nid', '%'.db_like($exp), 'LIKE');
    $records = $query->execute();
foreach ($records as $record) {
ksm($record);
}

Version 2

$connection = \Drupal::database();
$query = $connection->query("SELECT *FROM {node} WHERE nid like '%' :nid '%'", [
  ':nid' => "1",
]);
$records = $query->fetchAll();

foreach ($records as $record) {
ksm($record);
}

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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