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,

score 12 · Accepted Answer · answered Nov 02 '10 at 20:04

12

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.

answered Nov 02 '10 at 20:04

Kevin

13,153
11
60
87

Felix Eve · Answer 2 · 2016-01-31T23:02:53.527

5

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.

edited Jan 31 '16 at 23:02

answered Jun 26 '13 at 10:47

Felix Eve

3,811
3
40
50

This is wrong, the question was asked about Drupal 6. You provided a Drupal 7 answer – generalconsensus Sep 21 '15 at 23:27
Good point - I edited my answer to made it clear that this only works on Drupal 7. Could still prove useful to someone... – Felix Eve Sep 21 '15 at 23:35
@FelixEve indeed it has helped me with D7. Thanks :) – Abdul Sadik Yalcin Nov 04 '19 at 11:25

score 4 · Answer 3 · answered Nov 02 '10 at 18:49

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));

Donut · Answer 4 · 2010-11-02T17:23:00.203

3

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.

edited Nov 02 '10 at 17:23

answered Nov 02 '10 at 16:50

Donut

110,061
20
134
146

i want like operator for title field – Bharanikumar Nov 02 '10 at 17:01
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 Nov 02 '10 at 17:18
1

Ah! As noted in the answer below, you have to escape the %s. so %S% should be %%%S%%. – theunraveler Nov 02 '10 at 20:01

how to use like query in drupal

4 Answers4