Mysql advanced search

Question

Could anyone help me with some advanced search functionality in MySQL, using PHP?

I need to perform search in specific fields (title, description) and some special characters could be used as:

for several words starting with given text (eg. search term is 'north*', should return entries containing northern, northwest etc.)
to exclude words (e.g. search term is 'vacation -bahamas', should return entries containing vacation, but not those that also contain bahamas)

There's some comparisons of indexed-text search systems [in this old question](http://stackoverflow.com/q/2271600/472495). — halfer, Nov 04 '13 at 19:13

score 2 · Answer 1 · answered Nov 04 '13 at 18:37

2

You probably need a more specialized search technology. Check out Sphinx Search.

Lots of developers store data in MySQL, and then use a search engine like Sphinx Search as a companion technology to index the data and search it efficiently.

Use the right tool for the job.

answered Nov 04 '13 at 18:37

Bill Karwin

538,548
86
673
828

Thanks for the input. I'll hae a look at Sphinx Search. – mikaint Nov 05 '13 at 12:22

score 0 · Accepted Answer · answered Nov 04 '13 at 19:51

I'm a little unclear what your are asking. If you want to implement this yourself to achieve a specific functionality in PHP, here's the SQL knowledge you need:

SELECT * FROM table
WHERE title LIKE '%north%'

this finds all entries where the title contains 'north'

SELECT * FROM table W
HERE title LIKE 'north%'

this finds all entries where the title starts with 'north'

SELECT * FROM table W
WHERE (title LIKE 'north %' OR title LIKE '% north %' OR title LIKE '% north')

this finds all entries where the title contains the word north

SELECT * FROM table 
WHERE (description LIKE '% vacation %' OR description LIKE 'vacation %' OR description LIKE '% vacation')
AND (description NOT LIKE '% bahamas %' AND description NOT LIKE 'bahamas %' AND description NOT LIKE '% bahamas')

this finds all entries where the description contains the word 'vacation' and not the word 'bahamas'

However, if you need a more complicated (that handles case-insensitive queries), robust or efficient solution, go with Bill Karwin's answer.

Thanks for the helpful information. I think those queries will do the job. I'll try them. Is good to see that some people willing to help instead of some other "geniuses" that just giving -1. — mikaint, Nov 05 '13 at 12:23

Mysql advanced search

2 Answers2