2

I'm building a search on my site and I noticed it doesn't work when you enter more than one word into the search. Here's the gist of the query:

SELECT * FROM `blog` WHERE `content` LIKE '%$keyword%' OR `title` LIKE '%$keyword%' ORDER BY `id` DESC

The weird things is that when I test the query in phpMyAdmin it returns the expected results. On my website however, no results are found.

I tried replacing spaces in the keyword with %s, but that didn't change anything.

Improve this question
2
  • 2
    so if you search for "Jon Stewart" then that would result in searching for %Jon Stewart% together as one word. It won't find enries with 'Jon' or 'Stewart'. If you want to use them as keywords you'll need to split the string and dynamically create a where clause for each element you're searching for Commented Jan 10, 2013 at 17:58
  • I'm fine with it searching for a combined string, like 'Jon Stewart'. Which is why I'm confused it is not working. Commented Jan 10, 2013 at 18:03

2 Answers 2

Reset to default
1

The problem is that LIKE does pattern matching rather than actually search for keywords. You should create a fulltext Index on your database columns and use WHERE MATCH keywords AGAINST column. That will properly search for all keywords in any order and be a lot faster anyway.

Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

Looks like MATCH AGAINST is what I'm looking for. Did a search and found this: "When doing a search function of your site. Match and Against is better than Like sql statement. The field must be set to FullText match the term on the field: Eg. SELECT field1,field2 FROM table_name WHERE MATCH(field1,field2) AGAINST('keyword') AND field1 = '1' You can also use the IN BOOLEAN MODE to allow operators in the sql statement. eg. ... MATCH(field1,field2) AGAINST('-freak +hellfish' IN BOOLEAN MODE) ..." So my problem now is how do I change a column to be 'fulltext'? I don't see it as an option.
Just Google for "MySQL create fulltext index" or something.
Actually it looks like it works fine with one being tinytext and the other being longtext. Also it appears an issue with my query was partly because it was sent through a url, and spaces were '%20's. I'll be able to take it from here, thanks for the responses.
In phpMyAdmin you go to the table you want -> structure. Then at the bottom you have details. Then add a index for 1 column and there choose fulltext.
0

I just tried this in my database and using LIKE in the query is more than 66 times as fast than using MATCH with fulltext index. I'm using two tables which are "connected" to each other. One is tags and the other one is products.

So what I did was that I added a fulltext index to the tag column in the tags table and performed the match against that column. The query than joins the products and then spits out some data about the item. That took about 4 seconds with ~3000 products & ~3000 tags.

I then tried it by first exploding the search string by whitespaces, and then imploding the result with %' OR tags.tag LIKE '%. This took about 0,06 seconds with the same amount of products and tags.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.