19

I have a query like this in Drupal 6:

$sql = 'SELECT sid, score FROM search_index WHERE word LIKE "%%%s%%"';
$result = db_query($sql,$search_term);

And it worked fine, but now I'm upgrading to Drupal 7.

I read up and this is supposed to work, but it's not:

$sql = 'SELECT sid, score FROM search_index WHERE word LIKE "%:term"';
$result = db_query($sql,array(':term'=>$search_term));

Does anyone know what I'm doing wrong?

2 Answers 2

20

Try this.

 $sql = 'SELECT sid, score FROM {search_index} WHERE word LIKE :term';
 $result = db_query($sql, array(':term' => '%' . db_like($search_term)));
4
  • 3
    db_like ...? Why Drupal, why would you do that?
    – Coomie
    Nov 13, 2012 at 5:22
  • Yes, Drupal introduced a new db api in 7, such as db_select(), db_insert(), db_update(), db_condition(), db_like(), etc. Instead of using db_query(), it recommends to use db_select(). See here drupal.org/developing/api/database, api.drupal.org/api/drupal/includes!database!database.inc/group/…
    – Sithu
    Nov 13, 2012 at 5:33
  • 6
    @Coomie If you look at db_like, you will see that the escaping is done specifically by the DB layer on a per-engine basis. This is done for portability reasons.
    – mpdonadio
    Nov 13, 2012 at 16:35
  • Is it correct that the first line ends with a comma?
    – devunder
    Nov 20, 2017 at 10:39
0

If you followup the the following link, http://api.drupal.org/api/drupal/includes!database!database.inc/group/database/7 This will tell you that,

Note that placeholders should be a "complete" value. For example, when running a LIKE query the SQL wildcard character, %, should be part of the value, not the query itself.

So

SELECT nid, title FROM {node} WHERE title LIKE :title%;

Is incorrect.

The query should be

SELECT nid, title FROM {node} WHERE title LIKE :title;

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.