How to make SELECT insensitive in PHP / MySQL?

Question

Its my problem, I think its really basic.

I am trying to search the database if a row exists. heres my code:

$req="SELECT * FROM INSTITUTS WHERE inst_name='$fc_inst'"; $result=mysql_query($req) or die ('Erreur :'.mysql_error()); if (mysql_num_rows($result)){ echo ' name exist'; } else { echo ' does not exist.'; } 

The problem is that when he searches for “test”, he says that he doesn’t exist even if I have “Test” in my database.

Answer 1

you can use LIKE :

 WHERE foo LIKE 'bar' 

Or you can make the string lowercase with:

 WHERE LOWER(foo) = LOWER("bar") 

The LOWER() example is most effective when you know that all of your data in the database is already trimmed below, and then you can simply do it:

 WHERE foo = LOWER("bar") 

This would be a cheaper comparison than LIKE if you can omit all the data in your database.

Answer 2

Try using LIKE instead of = :

 $req="SELECT * FROM INSTITUTS WHERE `inst_name` LIKE '$fc_inst'"; 

Answer 3

This may also be a problem when setting the COLLATE table.

This CREATE statement will make your selection queries be case sensitive even when using LIKE statements:

 CREATE table instituts (inst_name VARCHAR(64)) CHARACTER SET latin1 COLLATE latin1_general_cs; 

While this one will provide case insensitivity:

 CREATE table instituts (inst_name VARCHAR(64)) CHARACTER SET latin1 

Answer 4

you can solve this using "LIKE", as other people told you, BUT , it is important to know that case sensitivity is determined by sorting in the database. For example, if you choose the sorting utf8_general_ci ..., then “ci” at the end means “case insensitive”, so comparisons that you perform in the future will be case insensitive.

In a few words: you must be careful in choosing sorting.

Answer 5

You can use MD5 () comparison if you want to select case sensitive :

 $req="SELECT * FROM INSTITUTS WHERE MD5(inst_name)=MD5('$fc_inst')"; 

Of course, you consume a little server processor, but it is much simpler than those boring comparisons.

Answer 6

Try:

 $req="SELECT * FROM INSTITUTS WHERE UCASE(inst_name)=UCASE('$fc_inst')"; $result=mysql_query($req) or die ('Erreur :'.mysql_error()); if (mysql_num_rows($result)){ echo ' name exist'; } else { echo ' does not exist.'; }