mysql LIKE search doesn't work for text with space

Question

I have the following query for my cms, I use it to find posts based on provided search query by user:

"SELECT post_content FROM c_posts WHERE post_content LIKE '%$s%' OR post_title LIKE '%$s%' OR post_description LIKE '%$s%' OR link_title LIKE '%$s%' OR tags LIKE '%$s%' AND post_status <> 'Initialized'";

It works fine when query $s is a word like 'something' but it doesn't return anything if $s includes space anywhere like 'something else' or ' something'. I need to search for exact word. so I can't trim the space. how do deal with this? (because of performance concerns, I decided not to use FULLTEXT)

As far as I can tell, it should work. Have you tried to print out your variable to see if it still contains the spaces in the string? — Logan Hasbrouck, Sep 23 '15 at 16:36
@LoganHasbrouck are you sure? Because it was my understanding that LIKE (in that form) can only handle one word... — Vahid Amiri, Sep 23 '15 at 16:40
I have just tested it both directly with raw sql and my own search function. The variable works just as expected. Raw: `select description from animes where description like '%e died%';` and laravel - toSql(): `select * from 'animes' where ('description' like ? or 'name' like ?)`. Both returned the exact same thing when searching 'e died'. — Logan Hasbrouck, Sep 23 '15 at 16:42
`LIKE` should definitely handle spaces correctly, the special characters are `%` and `_` (escape with backslash). You should get better performance with `FULLTEXT` what were your concerns? — Arth, Sep 23 '15 at 16:44

score 1 · Answer 1 · edited May 23 '17 at 12:07

After looking at your code, I think you may be having a similar issue I used to have with my own site: the order of execution of 'AND's and 'OR's. Here are a few links that may be of assistance.

SQL Server ANDs and ORs precedence

http://www.bennadel.com/blog/70-sql-query-order-of-operations.htm

Try this using a parenthesis:

SELECT post_content 
FROM c_posts 
WHERE (post_content LIKE '%$s%' OR post_title LIKE '%$s%' OR post_description LIKE '%$s%' OR link_title LIKE '%$s%' OR tags LIKE '%$s%') 
AND post_status <> 'Initialized'

score 0 · Answer 2 · edited May 23 '17 at 10:27

There are two problems in your query:

1) The $ sign is managed in a particular way, look at this post for more answers: MySQL search for "$" (dollar sign) fails?

2) You should enclose the ORs using parenthesis

SELECT post_content FROM c_posts WHERE 
  (post_content LIKE '%$s%' 
    OR post_title LIKE '%$s%' 
    OR post_description LIKE '%$s%' 
    OR link_title LIKE '%$s%' 
    OR tags LIKE '%$s%')
  AND post_status <> 'Initialized'

This way the query search the $s in one of the post_status and, if found, get all posts if the status is different from 'Initialized'

Using your code, instead return a false result, the query search for one of the likes but the last one (tags) is computed using the and.. tags AND post_status, so it will return all the posts where, title description etc matches the like but the status is irrelevant...

i'm using php and $ sign is parsed by php and it represents variable 's'. — Vahid Amiri, Sep 23 '15 at 17:36

mysql LIKE search doesn't work for text with space

2 Answers2