MySQL - case insensitive search

Question

Building my search engine for users to search for three variables $ Title, $ Text and $ Number ... How can I do this when the user searches, he finds all the results regardless of the type of input (lower or upper case) of the user entered 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 the register without taking into account the comparison

 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 

The list of supported mappings 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, the mysql function in php is deprecated, you should use mysqli instead of mysql.

Answer 3

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

Answer 4

One thing that I did in my own database for the company does all the capitalization using the strtoupper () function in PHP (especially for personal security reasons). It will also help if you use utf8_ci collation in your fields, for which you want to be case insensitive.