CODEWITHSUNDEEP
phpmysql
Vahid Amiri
Vahid Amiri

Reputation: 11117

mysql LIKE search doesn't work for text with space

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)

Upvotes: 2

Views: 3185

Answers (2)

Marcx
Marcx

Reputation: 6826

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

Upvotes: 0

Logan Hasbrouck
Logan Hasbrouck

Reputation: 416

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'

Upvotes: 1

Related Questions