Drupal 7 - case-insensitive LIKE with db_select

3
votes

I can't get a query to be case-insensitive using MySQL and Drupal 7. Here's my module code:

$results = db_select('people_table', 'p')->fields('p');
if (array_key_exists('department', $_GET)) {
    $results->condition('Department', '%' . db_like($_GET['department']) . '%', 'LIKE');
}
return $results->orderBy('Name', 'ASC')->execute();

With ?department=Chemistry in the URL, I get three results. With ?department=chemistry in the URL, I get no results. When I try $results->condition('UPPER(Department)'..., I get this error:

PDOException: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'UPPERDepartment' in 'where clause': SELECT p.* FROM {people_table} p WHERE (UPPERDepartment LIKE :db_condition_placeholder_0 ESCAPE '\\') ORDER BY Name ASC;

So it looks like it eats my parentheses. How can I do a case-insensitive LIKE?

Edit: the collation on the Department column as well as on the whole table is utf8_bin. This answer says "The only special one is utf8_bin which is for comparing characters in binary format." I don't know why this collation was chosen, since all the data in the table is English text. I might just change the collation to utf8_general_ci.

3
phpmysqldrupaldrupal-7case-insensitive
You have an SQL-injection in your code, see: stackoverflow.com/questions/332365/… I guess you've managed to bypass the PDO successfully :-) - Johan
LIKE is always case insensitive BTW, even on a binary collation. - Johan
How is there a SQL injection here? I thought the point of using the condition() function with db_select() is that it would escape your input. - Sarah Vessels
@SarahVessels: there is no SQL injection vulnerability here don't worry about it, PDO and the Drupal database layer protect you fully as you suspect - Clive

3 Answers

5
votes

what is collation of Department field?, it should be with prefix *_ci (case insensitive) for ex utf8_unicode_ci

4
votes

Instead of using condition you can use the where method to add a custom WHERE clause:

$results->where('UPPER(Department)'...
0
votes

To search case insensitive do following in drupal 7

$query->where('UPPER(Department) LIKE :dept',  array('dept' => '%'.db_like($keyword).'%'));
$result = $query->execute();