Photo Organizer

Searching for 2 keywords linked with AND always gives back an empty result.
Eg.: I have 2 images that both have the keywords "abstract" and "art" attached to them. They can't be found by the search "abstract AND art".

Looking at the SQL that PO sends to the database, I could trace the error. The search above produces the SQL:

[...]
OR photo.identifier in (select k.photo from photo_keywords k where (k.keyword ~* 'abstract' and k.keyword ~* 'art'))
[...]

This seems to be wrong since k.keyword contains only ONE keyword at a time. So "k.keyword ~* 'abstract' and k.keyword ~* 'art'" will never find a corresponding line in the database.

The SQL-part about keywords should be

photo.identifier in(select k.photo from photo_keywords k where k.keyword ~* 'abstract') and photo.identifier in(select k.photo from photo_keywords k where k.keyword ~* 'art')

The "AND" must be between the two "in(select... )"-Subqueries, not in them.

The problem in the code is in search.text.general.php, line 160, where a string plus the build_sql_search_string-function are used to generate $sql_search_keyword. I would suggest to write a new function, which is the same as build_sql_search_string() exept the part that handles "AND". This should be something like:


elseif (strncmp($keyword[operator], "AND", 3) == 0) {
if ($sql_search_string == "")
$sql_search_string = "photo.identifier in (select k.photo from photo_keywords k where " .sprintf("%s %s", $sql_field_name, $keyword[keyword]);
else
$sql_search_string .= " and photo.identifier in (select k.photo from photo_keywords k where " .sprintf("%s %s", $sql_field_name, $keyword[keyword]).")";
}

In line 160 in search.text.general.php ")" should be added to the return of this new function at the end, but no text at the beginning.
That should fix the simple search.
I have not investigated into the complex search, which doesn't find keywords linked by "AND" either.