PHP/Mysql Search - Case sensitive

Question

Im using the following PHP and MySql to fetch rows from a table,

$search_word=$_GET['search_word'];
$search_word_new=mysql_escape_string($search_word);
$search_word_fix=str_replace(" ","%",$search_word_new);
$sql=mysql_query("SELECT * FROM tweets WHERE content LIKE '%$search_word_fix%' ORDER BY votes DESC LIMIT 20");

The 'content' field is a TEXT field containing tweets.

The problem I have is if I search 'Stackoverflow' I get all the results containing 'Stackoverflow' but no results when the text is 'stackoverflow'. Basically the search is case sensitive.

Is it possible to change the query or PHP so when searching for 'Stackoverflow' both upper and lower case results are returned?

codaddict · Accepted Answer · 2010-09-16 10:51:29Z

5

You can try:

$search_word_fix=strtolower(str_replace(" ","%",$search_word_new));
$sql=mysql_query("SELECT * FROM tweets WHERE lower(content) LIKE '%$search_word_fix%' ORDER BY votes DESC LIMIT 20");

I've added strtolower to make $search_word_fix all lower case.
And in the where clause I've changed content to lower(content) so that I compare with lowercase of content.

You could have made both these changes in the query as suggested in other answer.

answered Sep 16, 2010 at 10:51

codaddict

457k83 gold badges501 silver badges537 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

CLiown Over a year ago

Simplest solution for me as a PHP novice.

Wrikken Over a year ago

Simplest != best. This will take no advantage of any possible index, and is quite likely slower. (said the pompous idiot who was actually just saddened by the fact nobody seems to care about the oh-so-easy case-insensitive collation in MySQL).

karim79 · Accepted Answer · 2010-09-16 10:49:32Z

4

Force the cases of both the search term and the column value:

SELECT * FROM tweets WHERE LOWER(content) LIKE LOWER('%$search_word_fix%') ORDER BY votes DESC LIMIT 20

or:

SELECT * FROM tweets WHERE UPPER(content) LIKE UPPER('%$search_word_fix%') ORDER BY votes DESC LIMIT 20

http://dev.mysql.com/doc/refman/5.0/en/string-functions.html

answered Sep 16, 2010 at 10:49

karim79

343k67 gold badges420 silver badges409 bronze badges

Comments

Wrikken · Accepted Answer · 2010-09-16 11:04:29Z

3

The 'proper' way to do it is to set it to case-insensitive collation:

CREATE TABLE foo (col1 varchar(24) COLLATE utf8_bin,col2 varchar(24) COLLATE  utf8_general_ci);
Query OK, 0 rows affected (0.03 sec)

DB 5.1.49-1-log:test  mysql> INSERT INTO foo VALUES ('stackoverflow','stackoverflow');
Query OK, 1 row affected (0.01 sec)

DB 5.1.49-1-log:test  mysql> SELECT * FROM foo WHERE col1 LIKE 'Stackoverflow';
Empty set (0.00 sec)

DB 5.1.49-1-log:test  mysql> SELECT * FROM foo WHERE col2 LIKE 'Stackoverflow';
+---------------+---------------+
| col1          | col2          |
+---------------+---------------+
| stackoverflow | stackoverflow |
+---------------+---------------+
1 row in set (0.00 sec)

DB 5.1.49-1-log:test  mysql> SELECT * FROM foo WHERE col1 COLLATE utf8_general_ci LIKE 'Stackoverflow';
+---------------+---------------+
| col1          | col2          |
+---------------+---------------+
| stackoverflow | stackoverflow |
+---------------+---------------+
1 row in set (0.00 sec)

answered Sep 16, 2010 at 11:04

Wrikken

70.8k8 gold badges99 silver badges136 bronze badges

1 Comment

Rick James Over a year ago

Don't use the COLLATE clause unless you simply can't change the column's collation -- You can't use the index.

Rick James · Accepted Answer · 2016-11-03 03:55:23Z

1

Change the COLLATION of the column in question (content) to be case insensitive, such as utf8mb4_unicode_ci.

Doing the case folding in PHP is costly and inefficient.

answered Nov 3, 2016 at 3:55

Rick James

144k15 gold badges144 silver badges255 bronze badges

Comments

Gaurang · Accepted Answer · 2012-12-04 07:53:56Z

0

mysql> SELECT * FROM myDb.myTable WHERE username = 'test980'; 1 row in set (0.00 sec)

mysql> SELECT * FROM myDb.myTable WHERE username = 'TEST980'; Empty set (0.00 sec)

MySQL columns can be made case-sensitive by creating them with the binary keyword. I suspect this is your problem. You can modify the column not to be binary or change your query to:

SELECT * FROM myDb.myTable WHERE UCASE(username) = 'TEST980';

which effectively makes string comparisons case insensitive despite the Binary character set chosen.

answered Dec 4, 2012 at 7:53

Gaurang

1,95818 silver badges12 bronze badges

1 Comment

Rick James Over a year ago

BINARY and UCASE are costly because the index cannot be used.

Jalal · Accepted Answer · 2015-03-26 23:06:51Z

0

It's all about choosing the best collation when creating MySql database.

utf8_unicode_ci is useful if you want to sort for example german character set accurately but it's slow.
utf8_general_ci is by default the standard when creating MySQL database character set utf8 and it's the fastest, but not case sensitive.
Rule of thumb: Use always utf8_general_ci on MySQL and collate utf8_bin if case sensitive required OR use SELECT BINARY in your statement.

answered Mar 26, 2015 at 23:06

Jalal

4174 silver badges5 bronze badges

Comments

Phil Tune · Accepted Answer · 2019-11-05 14:32:30Z

0

Note if you are using another language and you are using "strtolower()" it doesnt convert special characters(non english letters) to lowercase this will do the job mb_strtolower($text, 'utf8');

edited Nov 5, 2019 at 14:32

Phil Tune

3,3824 gold badges27 silver badges53 bronze badges

answered Nov 5, 2019 at 13:31

Engin Yücelen

298 bronze badges

Collectives™ on Stack Overflow

PHP/Mysql Search - Case sensitive

7 Answers 7

2 Comments

Comments

1 Comment

Comments

1 Comment

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

7 Answers 7

2 Comments

Comments

1 Comment

Comments

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related