CODEWITHSUNDEEP
phpmysql
Clément
Clément

Reputation: 125

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

Upvotes: 12

Views: 29687

Answers (7)

Anubhav
Anubhav

Reputation: 31

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

Upvotes: 0

Yann Saint-Dizier
Yann Saint-Dizier

Reputation: 206

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.

Upvotes: 3

PachinSV
PachinSV

Reputation: 3780

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.

Upvotes: 4

stevecomrie
stevecomrie

Reputation: 2483

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

Upvotes: 5

James C
James C

Reputation: 14149

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.

Upvotes: 16

user744116
user744116

Reputation:

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

Upvotes: 0

Naftali
Naftali

Reputation: 146302

Try using LIKE instead of =:

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

Upvotes: 11

Related Questions