[RESOLVED] Searching for multiple word phrases

Iain

This is more of an SQL question I guess - but the two seem to go hand in hand, and so hopefully someone can help.

At the moment I'm using Tom Muck's search extension for dreamweaver, that searches on any word, all words, or exact phrase.

However, it doesn't seem to manage multiple phrase searches.

For example, if I wanted to search on 'Aviation Experience' and 'Technical Knowledge' it would bring up results where any or all of the four keywords were found, rather than the two distinct phrases.

The closest I've gotten to doing this is to use the SQL :

SELECT *
FROM table_name
WHERE field_name LIKE '%search_field%'

So if the search was on Aviation Experience%Technical Knowledge it would work - but only if the phrases appeared in that order, and fail otherwise.

So what I'm looking for is the ability to find the multiple phrases regardless of the order in which they appear.

Hope that makes sense, and someone can help.

Cheers,
Iain

Roger_Ramjet

All you need is multiple where conditions with AND/OR depending on your requirements

SELECT *
FROM table_name
WHERE field_name LIKE '%phrase1%' OR field_name LIKE '%phrase2%'

SELECT *
FROM table_name
WHERE field_name LIKE '%phrase1%' AND field_name LIKE '%phrase2%'

First one will find either phrase, second will find both phrases in any order.

Iain

Cheers for that - is that still using % as the separator in the search field -

ie 'phrase1%phrase2' will find them regardless of the order in which they appear?

And how does the SQL know how many phrases to look for - ie if

'phrase1%phrase2%phrase3'

Was the search string?

Iain

Roger_Ramjet

you will need to parse the seperate phases into php vars, which i have called phrase1 phrase2. I should have actually written it as

$sql = "SELECT *
FROM table_name
WHERE field_name LIKE '%" . $phrase1 . "%' AND field_name LIKE '%" . $phrase2 . "%'";

but there is some assumption here that you know how to write php code correctly.

Now how you parse your input depends on your form design and input processing - and I'm not going to guess at that. If you are dealing with phrases then you must already have some method in place to define what is a phrase as opposed to seperate words.

Iain

Cheers Roger - in the end I've just invested in WebAssist's Database Search extension for Dreamweaver, which has gotten the job done pretty quickly and effortlessly.

It doesn't seem to include a way of highlighting the search terms in the results tho'.

Iain