How to make a SELECT in PHP/MySQL case insensitive?

Her's my probleme, i guess its really basic.

I'm trying to lookup in the database if a line does exist. 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 exis开发者_如何学Got';
}
else {
echo '  does not exist.';
}

Probleme is, when imm looking for "test", it says does not exist, even if i have "Test" in my database.

you can use LIKE:

WHERE foo LIKE 'bar'

Or you can cast both lowercase with:

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

The example with LOWER() is most effective where you know that all of your data in the database is already lower cased and then you can just execute:

WHERE foo = LOWER("bar")

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

Try using LIKE instead of =:

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

It could also be a problem with your table COLLATE setting

This CREATE statement will force your select queries to be case sensitive even when using LIKE operators:

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

Whereas this one will ensure case-insensitivity:

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

you can solve it using "LIKE" as other people told you, BUT it is important to know that the case sensitivity is determined by the collation in the database. For example if you select a collation utf8_general_ci... that "ci" at the end means "case insensitive" so the comparisons you do in the future will be case insensitive.

In a few words: you have to be careful about the collation you select.

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

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

of course you consume a little bit of the server's cpu but it's rather simpler than those boring collations.

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.';
}

You can use LIKE BINARY in your query..

Like this:

SELECT * FROM table_name WHERE column_name LIKE BINARY 'search_string'

this will check "search_string" data in case sensitive