[development] db_select problem

[Bob Hutchinson]

On Monday 28 December 2009, Damien Tournoud wrote:
> Please open an issue [1]. That looks like a regression when the ESCAPE
> statement was introduced (and maybe a bug in MySQL).

Done that, thanks.

>
> Damien Tournoud
>
> [1] http://drupal.org/node/add/project-issue/drupal
>
> On Mon, Dec 28, 2009 at 7:39 PM, Bob Hutchinson <hutchlists at midwales.com> 
wrote:
> > Working on a port of imagepicker module from D6 to D7
> >
> > I'm having problems getting some db_select statements to work, in
> > particular a db_or() that will not accept more than one 'LIKE' condition.
> > It will accept multiple '=' conditions but not 'LIKE'.
> >
> > Here is the error it throws:
> > ####
> > PDOException: SQLSTATE[42000]:
> > Syntax error or access violation: 1064 You have an error in your SQL
> > syntax; check the manual that corresponds to your MySQL server version
> > for the right syntax to use near
> > ':db_condition_placeholder_2 ESCAPE '\\') )'
> > at line 1:
> > SELECT i.img_id AS img_id, i.uid AS uid, i.img_name AS img_name,
> > i.img_title AS img_title, i.img_description AS img_description,
> > i.img_date AS img_date FROM {imagepicker} i
> > WHERE (uid = :db_condition_placeholder_0)
> > AND( (img_description LIKE :db_condition_placeholder_1 ESCAPE '\\')
> > OR (img_title LIKE :db_condition_placeholder_2 ESCAPE '\\') );
> >
> > Array (
> > [:db_condition_placeholder_0] => 2
> > [:db_condition_placeholder_1] => %testword%
> > [:db_condition_placeholder_2] => %testword%
> > )
> > in _imagepicker_browse() (line 965
> > of
> > /mnt/sdb1/www/drupal-7.x-dev/sites/all/modules/imagepicker/imagepicker.mo
> >dule). ####
> >
> > If I rewrite the above SQL to work in phpmyadmin:
> > ####
> > SELECT i.img_id AS img_id, i.uid AS uid, i.img_name AS img_name,
> > i.img_title AS img_title, i.img_description AS img_description,
> > i.img_date AS img_date FROM imagepicker i
> > WHERE (uid = 2)
> > AND( (img_description LIKE '%testword%')
> > OR (img_title LIKE '%testword%') )
> > ####
> > It works fine.
> >
> > I have tried various combinations of 'LIKE' and '=' with and without
> > wildcards and the result is the same, only one 'LIKE' condition allowed.
> >
> > Here is a snip of the code:
> > ####
> > return db_or()
> >  ->condition('img_description', '%testword%', 'LIKE')
> >  ->condition('img_title', '%testword%', 'LIKE');
> > ####
> > This one fails
> >
> > If I hardwire '='
> > ####
> > return db_or()
> >  ->condition('img_description', '%testword%', 'LIKE')
> >  ->condition('img_title', 'testword', '=');
> > ####
> > It works
> >
> > I have tried 3 conditions, changed the order but nothing will let more
> > than one 'LIKE' condition through
> >
> > Looks like a bug to me, but perhaps I'm doing something wrong.
> > Any ideas would be most welcome ;-)
> >
> >
> > --
> > -----------------
> > Bob Hutchinson
> > Midwales dot com
> > -----------------



-- 
-----------------
Bob Hutchinson
Midwales dot com
-----------------