Geek Answers Handbook

PHP / Mysql Search - Case Sensitive

Im using the following php and mysql to extract 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 is that if I search for " S tackoverflow", I get all the results containing "Stackoverflow", but no results if 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" the results are returned with upper and lower case?

+5
source share
6 answers

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");
  • strtolower, $search_word_fix .
  • where, content lower(content), content.

, .

+4

, :

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


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

+3

"" - :

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

COLLATION (content) , , utf8mb4_unicode_ci.

PHP .

+1

mysql > SELECT * FROM myDb.myTable WHERE username = 'test980'; 1 (0,00 )

mysql > SELECT * FROM myDb.myTable WHERE username = 'TEST980'; (0,00 )

MySQL , . , . , :

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

, .

0

MySql.

  • utf8_unicode_ci , , .
  • utf8_general_ci is the default standard for creating the utf8 MySQL database character set and is the fastest, but not case sensitive.
  • Practical use: always use utf8_general_ci in MySQL and match utf8_bin if case is required, or use SELECT BINARY in your statement.
0
source

More articles:


All Articles