[development] db_select problem

[Damien Tournoud]

Please open an issue [1]. That looks like a regression when the ESCAPE
statement was introduced (and maybe a bug in MySQL).

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.module).
> ####
>
> 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
> -----------------
>