Re: [support] query, like and %s

Quoting Ivan Sergio Borgonovo mail@webthatworks.it:

$query = "SELECT DISTINCT sum(re.field_rows_ext_quantity) as q, re.nid, n.title from content_field_rows_ext re JOIN node n on re.nid = n.nid where lower(field_rows_ext_brand) like lower('%%%s%%') group by re.nid, n.title"; $res = db_query($query,$marca);

• better you add all the group by otherwise it is not easy for the DB to understand what you want and in some cases it may be impossible. Some DB accept this sloppy syntax but that doesn't make it less sloppy or non deterministic.
• use lower and you may exploit some functional index if any
• count the % properly and decide what are you really looking for: '%pino', 'pino%, '%pino%' (I guess the later).

The use of lower() may be unnecessary in some databases if the data column is case insensitive; e.g.: Drupal on MySQL defaults to a collation of utf8_general_ci. In which case you will get all of the pino it if is Pino, pIno, or etc. without using the lower() function.

-- Earnie -- http://r-feed.com/ -- http://for-my-kids.com/ -- http://www.4offer.biz/ -- http://give-me-an-offer.com/