Multiple Keyword search

[hemi519]

Hi All,

I am having search functionality in my website, it allows user to type multiple keywords separated by comma(ex: php,3years,bangalore).

I am having 4 to 5 fields(skills,location,experience,title,qualification,etc.,) which user will search for. when user search for with 3 or 4 keywords(or more, similar to naukri or monster search).i want to know how that query will be.

I don't want to run that query in foreach row for every string. Is there anyway i can do it?

[djr33]

You must do a lot of string manipulation to create the right query. You can do this by using a foreach (or just while/for) loop and adding in more text to the string (or using the implode() function). Then you will have a single query.

I wrote a script for this a while ago and you might find it helpful, although it's a long thread and the script is pretty complicated.

Here's the thread, and the last page(s) have the full code:
http://www.dynamicdrive.com/forums/showthread.php?54348-Theories-of-a-search-algorithm
Whether or not you'd want to use that, it does have examples of building a dynamic query.

(Of course, remember to be very careful about security. Escape everything carefully!)

[james438]

I tried writing some example code for what you are looking for to make it easier for you, but it ended up too long. The best starter advice I can give you is to look up the concat() function for MySQL. That should put you on the right track. concat() is used to treat multiple fields as one. There are still so many caveats to the concat() (http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_concat) function that I'll let you play around with it and ask questions if you get stuck.

The thread djr33 suggested is one of the best around, but it can certainly get overwhelming looking at all of that information. We are happy to help if you need any help writing a search program for your site.

[djr33]

I might have missed something in the original post:
1) To search multiple MySQL columns, use concat() as James said.
2) To search with multiple input keywords, use the strategy I suggested.
From your original description, it sounds like you may need to use both.

[hemi519]

Thanks to both of you, I will try those methods and will come back with result ASAP

[hemi519]

I have found an alternative to do all my kind of search(multiple keywords). I am using sphinxsearch. This solved all my issues.

But first i have to concat required fields as you mentioned. Thanks Again

[james438]

Thanks for getting back to us on your solution. It is hard to find good search engines and from what I have read so far it looks like you found a good one.

I wouldn't use it, but that's just me. The problem for me is that it relies heavily on full text indexing. The problem with full text indexing is that it is not always customizable and full text indexing "helps" the user find what they are looking for. Searching for "fight" will also turn up "fought". Searching for "cats" will also pull up "cat". The searches are never in real time.

Full text indexing is also significantly faster. For many people faster searches and searches that help the user are quite handy. I prefer a greater deal of control over my searches. sphinxsearch does look legitimate, but whenever you use third party code you need to be careful that malicious code is not in there somewhere. With sphinxsearch this looks very unlikely.

[hemi519]

Thanks For ur suggestions.

Sphinxsearch is having its own custom rankers. It will give results according to "index weights".

Searching for "cats" will also pull up "cat". But first results will be only which are matching exactly to "cats" and then "cat" results will be followed.

Yes, there might be some issues while using third party tools and as u said they have to be taken care.

[james438]

Thanks for pointing that out. I had forgotten about weighted results with full text indexing. Overall I think you found a good and trustworthy search engine.

I'm partly biased towards the one I made since I made it ;)