[support] query, like and %s

[Ivan Sergio Borgonovo]

On Fri, 04 Sep 2009 11:58:40 +0200
Michel Morelli <michel at ziobuddalabs.it> wrote:

> Hi all. I have this query:
> 
> $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 '%%s% group
                                                            ^^ '
> by n.title"

A bit nonsense... how can you use distinct on sum()?
A glimpse on the schema would help.

I'm trying to guess...

$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).

> But when i show the output of this query the result for q is
> wrong. Is correct if I exec this code:

No, because it is prone to sql injection.

> $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 '%$marca%
> group by n.title" $res = db_query($query);

-- 
Ivan Sergio Borgonovo
http://www.webthatworks.it