Working on a port of imagepicker module from D6 to D7, Debian 5 standard lamp stack, PHP 5.2.0, mysql Ver 14.12 Distrib 5.0.32

I'm having problems getting a db_select statement 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 but it could easily be me.

Comments

Dave Reid’s picture

Dave Reid
he/him
Primary language English
Location Nebraska USA
CreditAttribution: Dave Reid commented

Odd. I don't have this same problem. In my path_redirect D7 port, I do the following:

function path_redirect_filter_query(SelectQueryInterface $query, $keys = '') {
  if ($keys) {
    // Replace wildcards with PDO wildcards.
    $conditions = db_or();
    $wildcard = '%' . preg_replace('!\*+!', '%', $keys) . '%';
    $conditions->condition('source', $wildcard, 'LIKE');
    $conditions->condition('redirect', $wildcard, 'LIKE');
    $conditions->condition('query', $wildcard, 'LIKE');
    $conditions->condition('fragment', $wildcard, 'LIKE');
    $query->condition($conditions);
  }
}

I added dpm((string) $query); to the end of the function and the resulting query was:

SELECT 
FROM 
{path_redirect} path_redirect
WHERE ( (source LIKE :db_condition_placeholder_0 ESCAPE '\\') OR (redirect LIKE :db_condition_placeholder_1 ESCAPE '\\') OR (query LIKE :db_condition_placeholder_2 ESCAPE '\\') OR (fragment LIKE :db_condition_placeholder_3 ESCAPE '\\') )

No error on the filtered page with this query, so I'm not sure why it's not working for you. :/

hutch’s picture

hutch CreditAttribution: hutch commented

Thanks for your interesting posting, it still won't work but I'm closer I think.

mr.baileys’s picture

mr.baileys
Primary language Dutch
Location 🇧🇪 (Ghent)
CreditAttribution: mr.baileys commented
Category: bug » support

Works fine on my end too:

db_select('users', 'u')
  ->fields('u', array('uid, name'))
  ->condition(db_or()->condition('name', '%min', 'like')->condition('name', 'ad%', 'like'))
  ->execute();

Mind if we switch this to a support request unless it's proven to be a bug?

flefle’s picture

flefle CreditAttribution: flefle commented
Status: Active » Fixed

This issue will be automatically closed, feel free to reopen it if needed. mr.baileys's solution seems to be reasonable.

Status: Fixed » Closed (fixed)

Automatically closed -- issue fixed for 2 weeks with no activity.

fabul’s picture

fabul CreditAttribution: fabul commented
Issue tags: +db_select, +users table, +SQL_STAT[42000]

Hi,

For information : I had an error 42000 when I used a db_select statement without fields statement using the 'users' table.

This is my example : 

$query=db_select('users','u')
->condition('mail',$emailParentValue);
$parentuser=$query->execute()->fetchObject();

Am I wrong when I fon't use fields statement as I'd like to read all the fields of the records ?

cheers.