CLiown CLiown - 9 months ago 54
MySQL Question

PHP/Mysql Search - Case sensitive

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

$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 Source

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.