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"
and this code $res = db_query($query,$marca);
But when i show the output of this query the result for q is wrong. Is correct if I exec this code:
$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);
Where is my error ?
M.
On Fri, 04 Sep 2009 11:58:40 +0200 Michel Morelli michel@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);
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/
On Fri, 04 Sep 2009 12:24:04 +0000 Earnie Boyd earnie@users.sourceforge.net wrote:
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.
But that's going to make it not portable. Of course you could "lower" on the php side. But then... you still have the added cost of running a function but you may miss the chance to use a functional index. Any decent DB will compute lower on a const just once making the DB solution better then the php one on most cases. Somehow you're moving the problem of portability to the index creation... but at least you leave a door open for optimization and you've a portable solution.
Of course you still have the problem of %string% that can't generally exploit any index... etc...
Ivan Sergio Borgonovo ha scritto:
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.
I know it. Don't consider me so stupid! It was to say that the query works.
If the solution is "%%%s%%" tnx for the reply.
M.
-
Earnie Boyd -
Ivan Sergio Borgonovo -
Michel Morelli