[support] query, like and %s

[Earnie Boyd]

Quoting Ivan Sergio Borgonovo <mail at 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/