not case sensitive query in mysql

Question

hey guys

i need to query my database and find results :

mysql_query("select * from ".ALU_TABLE." where username like '%$q%' or name like '%$q%'");

if i have a name in my table such as Book and i enter book in search box it wont show the Book

i need to query my database as not to be case sensitive.

Answer 1

You need to append something like COLLATE utf8_general_ci to your query. (The _ci suffix stands for case insensitive.)

Have a look at the documentation: 9.1.7.1. Using COLLATE in SQL Statements:

With the COLLATE clause, you can override whatever the default collation is for a comparison. COLLATE may be used in various parts of SQL statements.

Answer 2

You can use the LOWER() function

... WHERE LOWER(username) LIKE blabl OR LOWER(name) LIKE asdasd