Using MySQL LIKE to match a whole string

I have 开发者_高级运维been doing a bit of searching round StackOverflow and the Interweb and I have not had much luck.

I have a URL which looks like this...

nr/online-marketing/week-in-review-mobile-google-and-facebook-grab-headlines

I am getting the article name from the URL and replacing the '-' with ' ' to give me:

week in review mobile google and facebook grab headlines

At this point this is all the information that I have on the article so I need to use this to query the database to get the rest of the article information, the problem comes around but this string does not match the actual headline of the article, this this instance the actual headline is:

Week in review: Mobile, Google+ and Facebook grab headlines

As you can see it include extra punctuation, so I need to find a way of using MYSQL LIKE to match the article.

Hope someone can help, a standard SELECT * FROM table WHERE field LIKE $name does not work , im hoping of finding a way of doing it without splitting up each individual word but if that what it comes down to then so be it!

Thanks.

---

Try MySQL MyISAM engine's full-text search. In your case the query will be:

SELECT * FROM table
WHERE MATCH (title) AGAINST ('week in review mobile google and facebook grab headlines');

That requires you to convert the table to MyISAM. Also depending on the size of the table, test the performance of the query.

See more info under: http://dev.mysql.com/doc/refman/5.0/en/fulltext-search.html

---

This really seems more like a database design issue... If you're using large texts with different fields as forms of primary keys it could lead to duplicates or synchronization problems.

One potential solution is to give each entry a unique identifier (perhaps an int or uniqueidentifier field if MSQL supports that), and use that field to map the actual healdine to the URL.

another potential solution is to create a table that will associate each headline with its URL and use that table for lookups. This will incur a little extra overhead, but will ensure that special characters in the title will never effect the lookup process.

As for a way to do this with your current design, you may be able to do some kind of regular expression search by tokenizing each word individually and then searching for an entry that includes all tokens, but I'm fairly certain that MSQL doesn't provide this functionality in a basic command.