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?

Answer 1

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.

Answer 2

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

Answer 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)