Ignore case sensitive when searching in mysql db

Databases
1

I’m trying to make an search script and it works fine if I write the username exactly as written in db with capital letters and lowercase letters. But I want to be able to search ignoring uppercase and lowercase… How do I do so?

$search = $_GET['q'];
	
$sql = mysql_query("SELECT * FROM ".$prefix."_users WHERE email = '$q' OR username = '$q'");
$row = mysql_fetch_assoc($sql);

Thanks in advance :slight_smile:

2

By default MySQL is not case-sensitive unless you search against a binary string. You should consider changing your db design to store email addresses or usernames inside varchar fields.

However, there is a workaround using the COLLATE operator (pls check http://dev.mysql.com/doc/refman/5.0/en/case-sensitivity.html)

3

Another quick and dirty workaround if your DB does case-sensitive comparison is to use UPPER() or LOWER() on the search string and the target field data.

4

or convert all data going into the database to lower case first.