Partial Matches

phuggett

Hi Guys,

I have a problem that is driving me crazy.

I am trying to write a PHP query that returns matches, but also partial matches to a user entered query.

E.g (not full code, just snippets.

User enters 'blue widgets' into a form and the form sets $query = "blue widgets".

My current query goes something like;

SELECT * FROM table_name WHERE 1st_field LIKE $query

This is great as it will bring back exact matches, but how do I also get it to bring back things such as;

blue (on it's own)
widgets (on its own)
blue round widgets (contains both terms but not exact match.

If anyone could point me in the direction of the right info I would be extremely grateful.

Cheers
Paul

bpat1434

SELECT * FROM table_name WHERE 1st_field LIKE '%$query%'

cahva

bpat1434 wrote:
SELECT * FROM table_name WHERE 1st_field LIKE '%$query%'

Well that doesnt help if you want matches also from different words in query. I think the best solution would be something like this:

// Change space to |-character(for use with regex)
$tmp = str_replace(' ','|',$query);

// Then just do the sqlquery
$sql = "SELECT * FROM table_name WHERE 1st_field REGEXP '($tmp)';

That sql-line would become:
SELECT * FROM table_name WHERE 1st_field REGEXP '(blue|widgets)'

And it would return every row with "blue" or "widget" in it.

This is just an example and you should tune it better(for example if someone puts doublespaces etc.)

Roger_Ramjet

This is yet another job for FULL TEXT SEARcH

It is what all the searches that you have seen use and does exactly what you want. You will have to read the manual thoroughly to learn what it does and how you use it; and I do mean thoroughly, especially the part where it tells you that there is no point trying to test it with 3 or 4 records.

When you have read up on it, experimented with it, and coded some scripts to use it, come back with any remaining problems. But don't come back here tomorrow like half the folks moaning that it does not work, cos it does work PERFECTLY. You just have to read the manual properly.