current community

your communities

more stack exchange communities

Stack Exchange
tour help
Take the 2-minute tour ×
Drupal Answers is a question and answer site for Drupal developers and administrators. It's 100% free, no registration required.
up vote 4 down vote favorite
2

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

share|improve this question

3 Answers 3

up vote 12 down vote accepted

change

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

to

$query->where('LOWER(ttd.name) = :category', array(':category' => $category));
share|improve this answer
    
That works. It didn't even occur to me to do that. –  pthurmond Nov 10 '11 at 21:22
    
Thanks by the way. –  pthurmond Nov 10 '11 at 21:22
up vote 2 down vote

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.

share|improve this answer
up vote 0 down vote

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

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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