MySQL Active record query ignoring case

Question

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?

Answer 1

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();

Answer 2

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.