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 '*S*tackoverflow' I get all the results containing 'Stackoverflow' but no results when the text is '*s*tackoverflow'. 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

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 2

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