Searching by each word rather than complete string using MYSQL PHP

Question

My search allows users to search games in my database. The only problem is that if a user types multiple terms within the search, it will only get hits if those multiple words are right next to each other.

Example:

searched term = Call Duty Modern

result = none

BUT if they input:

searched term = Call of Duty -or- Modern Warfare

result = Call of Duty Modern Warfare 1/2/3 etc.

here is my mysql query:

$query = "SELECT ID, title FROM games WHERE title LIKE '%{$title}%' AND platform = :platform LIMIT $startFrom,15";
        $statement = $dbG->prepare($query);
        $statement->bindValue(':platform', $platform);
        $statement->execute();
        $gamesLike = $statement->fetch(PDO::FETCH_ASSOC);

I know i should break up each word and search each term but im afraid it would eat up all my programs speed...

are there any specific MYSQL query tweaks i can use to achieve the result i need?

Any suggestions would be greatly appreciated

Thank you for your time

Jonathan Kuhn · Accepted Answer · 2011-09-30 20:59:01Z

6

full text searching is great for this: http://dev.mysql.com/doc/refman/5.0/en/fulltext-search.html

It essentially turns mysql into a search engine where you use match and against to get a rank back of how often the search terms show up in the column you are matching against. It can handle partial matches, multiple matches, excludes words that are small/common to help performance and allows for search keywords/symbols like +some -search will return results that must have some but can't have search.

Also it is surprisingly easy to setup as pretty much everything you need works by default.

answered Sep 30, 2011 at 20:59

Jonathan Kuhn

15.3k3 gold badges34 silver badges43 bronze badges

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

1 Comment

Dan Over a year ago

Wow, that is exactly what i needed (and was hoping mysql had) =)

Collectives™ on Stack Overflow

Searching by each word rather than complete string using MYSQL PHP

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related