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

Question

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.

MySQL's queries are case insensitive by default, unless the underlying tables have a case-sensitive collation set. — Marc B, May 09 '11 at 15:04

score 16 · Accepted Answer · answered May 09 '11 at 14:14

16

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.

answered May 09 '11 at 14:14

James C

14,047
1
34
43

1

i am sure in some areas, some info **has to** be uppercase or mixed case and cannot be all lowercase – Naftali May 09 '11 at 14:22

score 11 · Answer 2 · answered May 09 '11 at 14:11

11

Try using LIKE instead of =:

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

answered May 09 '11 at 14:11

Naftali

144,921
39
244
303

This will work ?, he should not use Lower() in statement and strtolower() in PHP code ? – Damian Leszczyński - Vash May 09 '11 at 14:13
@Vash, is that a question or statement? – Naftali May 09 '11 at 14:13
1

MySQL is generally case insensitive within LIKE queries unless you go out of your way to make it not the case. – stevecomrie May 09 '11 at 14:19
@Vash: It's not how MySQL engine works, it's an **SQL** thing. Check this question: http://stackoverflow.com/questions/153944/is-sql-syntax-case-sensitive. – ypercubeᵀᴹ Aug 16 '11 at 12:48

stevecomrie · Answer 3 · 2011-05-09T14:24:32.963

5

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

edited May 09 '11 at 14:24

answered May 09 '11 at 14:17

stevecomrie

2,423
20
28

2

This is possibly the most sensible answer. – Álvaro González May 10 '11 at 10:53

score 4 · Answer 4 · answered May 09 '11 at 14:49

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.

Yann Saint-Dizier · Answer 5 · 2011-08-11T09:43:34.160

3

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.

edited Aug 11 '11 at 09:43

answered Aug 05 '11 at 12:38

Yann Saint-Dizier

206
2
4

*"a little bit of the server's cpu"* can mean a few hours of CPU if the table is big and the query is frequently run. – ypercubeᵀᴹ Aug 13 '11 at 21:25

score 0 · Answer 6 · answered Nov 18 '19 at 12:53

0

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

answered Nov 18 '19 at 12:53

Anubhav

31
2

score 0 · Answer 7 · answered May 09 '11 at 14:13

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

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

7 Answers7

Linked

Related