MySQL - Case-insensitive search [duplicate]

Question

Building my search engine for users to search for three variables $Title, $Text and $Number... How do i make it when user searches it finds all results no matter the case type (lower or upper case) user typed in $query?

$query = trim ($query);

$raw_results = mysql_query("SELECT * FROM posts
        WHERE (`Number` LIKE '%".$query."%') OR (`Title` LIKE '%".$query."%') OR (`Text` LIKE '%".$query."%') ") or die(mysql_error());

Answer 1

Use case insensitive collation

SELECT * FROM posts
WHERE (`Number` LIKE '%".$query."%') 
OR (`Title` LIKE '%".$query."%') 
OR (`Text` LIKE '%".$query."%')
COLLATE utf8_ci  --'ci' stands for case insensitive: choose the colaltion that fits your encoding

List of supported collations is here: https://dev.mysql.com/doc/refman/5.5/en/charset-charsets.html

Answer 2

You can use the mysql LOWER() function:

$query=strtolower($query); //Query to lower case
$raw_results = mysql_query("SELECT * FROM posts
    WHERE (LOWER(`Number`) LIKE '%".$query."%') OR (LOWER(`Title`) LIKE '%".$query."%') OR (LOWER(`Text`) LIKE '%".$query."%') ") or die(mysql_error());

However, mysql function in php are deprecated, you should use mysqli instead of mysql.

Answer 3

MySQL is case-insensitive for char, varchar, and text (non-binary strings) by default.