10

I am trying to write a condition into a SQL WHERE clause that forces the column comparison to a variable to be compared in lowercase. However the addExpression function isn't accomplishing this (since that is putting the expression in the field selection, not the where clause.

Here is what I have tried:

$category = 'mobile wifi';
$query = db_select('taxonomy_term_data', 'ttd')
    ->fields('ttd', array('tid'));
$query->innerJoin('taxonomy_vocabulary', 'tv', 'ttd.vid = tv.vid');
$query->addExpression("LOWER(ttd.name) = $category");
$result = $query->condition('machine_name', 'images_cat', '=')
    ->execute()
    ->fetchAssoc();

And this:

$category = 'mobile wifi';
$query = db_select('taxonomy_term_data', 'ttd')
    ->fields('ttd', array('tid'));
$query->innerJoin('taxonomy_vocabulary', 'tv', 'ttd.vid = tv.vid');
$result = $query->condition('machine_name', 'images_cat', '=')
    ->condition('LOWER(ttd.name)', $category, '=')
    ->execute()
    ->fetchAssoc();

The second one becomes invalid and the first one causes the problem I mentioned above. Any thoughts or suggestions?

Thanks, Patrick

15

change

$query->addExpression("LOWER(ttd.name) = $category");

to

$query->where('LOWER(ttd.name) = :category', array(':category' => $category));
  • That works. It didn't even occur to me to do that. – Patrick Nov 10 '11 at 21:22
3

Using LOWER() is considered slow in MySQL. It isn't necessary too because LIKE in Drupal's Database API (DBTNG) is case insensitive, at least when your MySQL table is configured to use one of the *_ci collations. A standard MySQL installation uses *utf8_general_ci* and so is Drupal.

So you just need to use a LIKE condition:

$query->condition('name', $category, 'LIKE');

See Conditional Clauses for a comprehensive explaination.

BTW: A DBTNG based database driver is responsible for implementing a case insensitive LIKE. PostgreSQL for instance uses ILIKE instead of LIKE which is handled in includes/database/pgsql/database.inc.

1

You can still use addExpression.

// Functions - You can use sql functions using addExpression ($expression, $alias = NULL, $arguments = array()).
$query->addExpression('MIN(o.tid)', 'min_term_id');

Resource: SQL query examples in Drupal 7

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.