3

I have the following active record query:

$this->db->select('id, firstname, surname');
$this->db->from('users');
$this->db->where('site_id',$siteid);
$this->db->like('firstname', $name);
$this->db->or_like('surname', $name); 
$query = $this->db->get();

This works fine with the exception that my names within the DB can contain upper and lowercase names. If the case does not match, the query fails.

Is there any way I can ignore the case with my query?

Ben
  • 6,026
  • 11
  • 51
  • 72
  • 1
    MySQL is normally case-insensitive. What's the collation on your table? What's the SQL this is generating? – Ken Apr 11 '11 at 17:46
  • Hi Ken, thanks for the prompt reply. The collation of that table is 'utf8_bin' and the SQL produced is as follows: `code`SELECT `id`, `firstname`, `surname` FROM (`users`) WHERE `site_id` = '1' AND `firstname` LIKE '%ben%' OR `surname` LIKE '%ben%' – Ben Apr 11 '11 at 17:51
  • It looks like UTF8_bin is my issue - [link]http://stackoverflow.com/questions/4558707/case-sensitive-collation-in-mysql[/link] – Ben Apr 11 '11 at 17:58
  • @Ken you should try not to restrict your code to a particular db server or a collation. If you want case insensitive search in any case then store the lower case or upper case of the search fields separately and search on them. – Ashwini Dhekane Apr 11 '11 at 18:02
  • Confirmed it was my collation setting that was set to UTF8_bin and causing the issue. Many thanks to @Ken for pointing me in the right direction. Best regards, Ben. – Ben Apr 11 '11 at 19:02
  • Ashwini: If you want it to be database server agnostic (for this one query), why not just wrap both sides in `LOWER()`? I don't see any benefit in storing extra data and breaking 3NF here. – Ken Apr 11 '11 at 21:18

2 Answers2

2

Try this, it works fine for me: (you can use lower or upper mode in both sides to insensitive query )

$this->db->select('id, firstname, surname');
$this->db->from('users');
$this->db->where('site_id',$siteid);
$this->db->like('lower(firstname)', strtolower($name));
$this->db->or_like('lower(surname)', strtolower($name)); 
$query = $this->db->get();
ganji
  • 752
  • 7
  • 17
1

One of the way to manipulate case sensitivity in mysql to have your firstname field in either upper or lower case in db and convert given string in that format and match it.

edit: like is an operator and it depends on the database you use that it will be case sensitive or not . In postgres , it is case insenstive where as in mysql it is case sensitive. So it depends on the database you are using.

edit: Another working but not that useful way will be to use ucase() or lcase() function on given field and then match with lowercase or uppercase of match. Though it will have performance issues.

ankur.singh
  • 658
  • 5
  • 11
  • by default the search in postgresql is case sensitive. http://www.postgresql.org/docs/8.0/static/functions-matching.html and to make it case insensitive they have recommended to user ILIKE instead. also on the contra to your statement like in mysql is case insensitive by default – KAsh Feb 17 '15 at 05:23