how to use like query in drupal

Question

How to write SQL LIKE Query in drupal ,

SELECT title FROM { node } WHERE type='%s'

i want to add the LIKE CONDITION IN THAT

SELECT title FROM { node } WHERE type='%s' AND LIKE '%S%'

i think i writtern wrong like query formnat, can rewrite and tell me,

Solution 1

Just use % to escape.

$result = db_query('SELECT title FROM {node} WHERE type = "%s" AND title LIKE "%%%s%%"', 'type', 'title');

while ($row = db_fetch_object($result)) {
     // do stuff with the data
}

Node type does not need escaping.

Solution 2

And here is an example with how to use LIKE in a dynamic query (Drupal 7 Only):

$query = db_select('node', 'n')
        ->fields('n', array('title'))
        ->condition('type', 'my_type')
        ->condition('title', '%' . db_like(search_string) . '%', 'LIKE');
    $result = $query->execute()->fetchCol();

db_like() is used to escapes characters that work as wildcard characters in a LIKE pattern.

Solution 3

drupal_query replace %% to % and %s to value string

so your code will be

$sql = "SELECT title FROM node WHERE type='%%%s' AND title LIKE '%%%S%%'";
$type = "type to use in query";
$title = "title to use in query";    
$result = db_result(db_query($sql, $type, $title));

Solution 4

OK, so you want the LIKE operator to refer to the title column. Use this query:

$sql = "SELECT title FROM node WHERE type='%s' AND title LIKE '%S%'";
$type = "type to use in query";
$title = "title to use in query";    
$result = db_result(db_query($sql, $type, $title));

This is because the LIKE operator requires a column name to be specified. Otherwise, your database doesn't have any idea what value you want to perform the comparison on. See here.

View more solutions

Comments

[Bharanikumar]

i want like operator for title field

[theunraveler]

This is correct, but you have to add variables to substitute for those placeholders. So, $sql = db_query("SELECT title FROM node WHERE type='%s' AND title LIKE '%S%'", $type, $title); $result = db_result($sql); where $type and $title are equal to the things you're filtering for. You may have known this; I just wanted to state the obvious.

[theunraveler]

Ah! As noted in the answer below, you have to escape the %s. so %S% should be %%%S%%.

[generalconsensus]

This is wrong, the question was asked about Drupal 6. You provided a Drupal 7 answer

[Felix Eve]

Good point - I edited my answer to made it clear that this only works on Drupal 7. Could still prove useful to someone...

[Abdul Sadik Yalcin]

@FelixEve indeed it has helped me with D7. Thanks :)