Case sensitive LIKE and WHERE clause matching in MySQL using CodeIgniter

Question

My database/table/column collation is set to *_ci mean case insensitive. What I want is to write case sensitive query on run time using CodeIgniter for LIKE and WHERE clause. I tried an approach something like this

$this->db->where("fieldname LIKE BINARY 'value'", NULL, true);

But this approach is not working with LIKE clause

$this->db->like("fieldname LIKE BINARY 'value'", NULL);

Any type of help will greatly appreciated!

Answer 1

Well well well ...

After searching around and hammering my head I finally solved this issue! First of all I have to set the collation of table/column to *_cs, after that if I need case sensitive matching I have to run simple

$this->db->where('field', 'value');

or

$this->db->like('field', 'value');

But if you want case insensitive matching than use

$this->db->where("LOWER('field')", strtolower('value'), false);

$this->db->where() accepts an optional third parameter. If you set it to FALSE, CodeIgniter will not try to protect your field or table names with backticks.

or

$this->db->like("LOWER('field')", strtolower('value'));

Remember CodeIgniter follows the standard collation of your MySQL database, there is no built in function is available in CI 2.x to over write default database collation. So if your column/table collation is set to *_ci (case insensitive) CI will consider abc = aBc but if your collation is set as *_cs it will consider abc != aBc