Eliminating more false positives on partial search patterns?

[sjordan]

Can someone help remedy a little annoyance?

I have photos from New York with keyword "NY". I also have photos from Germany. When I do a search on "NY" I of course get my New York pictures but also Germany as it matches the final two characters.

Insights on how to remedy this process would be appreciated.

Thank you in advance.

version 1.48

hoping not to incur the wrath -- i did search the form on "partial" "substring" "regex" "pattern" before posting

[Nibbler]

http://forum.coppermine-gallery.net/index.php?topic=30310.0

[sjordan]

Hello Nibbler,

First thank you for the response.

I put your suggestion in place and it's not working for me. After looking at the code, your solution seems to work as long as the keywords  is just one. Meaning if the only keywords for a photo of new york is just "ny" then it would work. However, the moment I add multiple keywords, such as 'ny', 'the big apple', new york', etc. no results are found.

Allowing the wild card will find results, but then I'm back to having it find false positives like 'germany' as well.

Is this correct?

Thank you in advance.

[Nibbler]

Try

Code Select Expand

if (in_array($param, $allowed))$fields[] = "$param LIKE '%$word%'";

change to

Code Select Expand

if (in_array($param, $allowed)) $fields[] = $param == 'keywords' ? "CONCAT(' ', $param, ' ') LIKE '% $word %'" : "$param LIKE '%$word%'";

[sjordan]

Nibbler,

That seems to do the trick.

So basically it checks if the param is the literal 'keyword'. If so, slap some white space around the search target ($word) and use the wildcard. Basically, we're looking for whole words (analogous to the regex \b word boundaries). Otherwise, proceed as normal on the other params?

Thank you again.

[Nibbler]

Yup. We need to pad the value in the db too so keywords at the start and end of the list can be matched.

[sjordan]

Nibbler,

Can you help mean understand the need for mysql CONCAT?

This seems to work as well

if (in_array($param, $allowed)) $fields[] = $param == 'keywords' ? "$param LIKE '% $word %'" : "$param LIKE '%$word%'";

Nibbler, I really appreciate your help on this.

[Nibbler]

I just did explain that. If the keywords are "one two three four" then you will find ' two ' and ' three ' but not ' one ' or ' four '. That code makes the keywords into " one two three four " so they can all be found.

[sjordan]

Oh sorry, I didn't understand how slick your code is. I mistook your statement about padding the db to mean I have to manually query the db and pad the keywords before it would work flawlessly. I didn't realize that by using CONCAT, that padding is effectively happening dynamically each time the query is run.

My bad.

Thank you again.