multiple keyword search script needs some expert input

Question

I have been writting a keyword search script based on this tutorial: http://www.hackosis.com/2007/11/06/howto-simple-search-engine-with-php-and-mysql/

Like some of the commenters mentioned, the script only ends up returning results based on the last word in the search terms. So I have also tried to implement one of the suggestions from another user, but now I seem to only be able to get results based on the first search term.

The code for my script can be found here: http://php.pastebin.com/m7759afd3

My print_r for the results looks like this:

SELECT * FROM blog WHERE blog_title LIKE '%barry%' OR blog_content LIKE '%barry%' AND blog_title LIKE '%child%' OR blog_content LIKE '%child%' AND blog_title LIKE '%help%' OR blog_content LIKE '%help%' ORDER BY blog_title

SELECT * FROM links WHERE link_title LIKE '%barry%' OR link_desc LIKE '%barry%' AND link_title LIKE '%child%' OR link_desc LIKE '%child%' AND link_title LIKE '%help%' OR link_desc LIKE '%help%' ORDER BY link_title

SELECT * FROM pages WHERE page_title LIKE '%barry%' OR page_content LIKE '%barry%' AND page_title LIKE '%child%' OR page_content LIKE '%child%' AND page_title LIKE '%help%' OR page_content LIKE '%help%' ORDER BY page_title

Thank you for any help you might be able to offer.

Answer 1

If you want to return results that contain any of the keywords, then replace all the AND with OR.

If you do this, then your db will check if any of the keywords exist in any of the columns of the table.

Hence your final query would be read by the DB server like this:

SELECT * FROM blog WHERE blog_title LIKE '%barry%' OR blog_content LIKE '%barry%' OR blog_title LIKE '%child%' OR blog_content LIKE '%child%' OR blog_title LIKE '%help%' OR blog_content LIKE '%help%' ORDER BY blog_title

and should return all that records that have the keywords (barry, child, help) in any column (blog_title, blog_content).

Hope this helps.

Answer 2

The AND operator has a higher precedence than OR, so if you want to return results that contain all of the keywords you need to add parentheses as follows:

  SELECT * FROM blog
  WHERE (blog_title LIKE '%barry%' OR blog_content LIKE '%barry%')
    AND (blog_title LIKE '%child%' OR blog_content LIKE '%child%')
    AND (blog_title LIKE '%help%' OR blog_content LIKE '%help%')
  ORDER BY blog_title