Page 1 of 1

match ALL keywords search query

Posted: Fri Feb 09, 2007 2:55 pm
by mr_griff
I am having trouble writing the SQL query for this situation:

I have two tables. One has a list of photos and the other has keywords for photos. Each photo can have multiple keywords.

photos
----------
photoID

photo_keywords
-------------------
photoID
keyword

When someone search for a single keyword, the query is easy. My problem is matching multiple keywords. Using the example "yellowstone river" I need to match photos that have both the keyword "yellowstone" and "river" or the keyword "yellowstone river".

Basically it's a match ALL keywords search (vs match ANY).

Posted: Fri Feb 09, 2007 3:12 pm
by louie35

Code: Select all

//get query
$searchQ = trim($_GET['form input']);
$searchQ = str_replace("  "," ",$searchQ);
$search_type = " AND ";

// split the search query
$kt = split(" ",$searchQ);
  while(list($key,$val)=each($kt)){
	if($val <> " " and strlen($val) > 0){
	$searchWhere = " (`table`.`tbl_field` LIKE '%$val%'  ";
	$searchWhere .= ") $search_type ";
			}
  }// end of while
	if($search_type == 'OR'){
	  $searchWhere = substr($searchWhere,0,(strLen($searchWhere)-3));
	}elseif($search_type == 'AND'){
	  $searchWhere = substr($searchWhere,0,(strLen($searchWhere)-4));
	}

Posted: Fri Feb 09, 2007 4:27 pm
by mr_griff
This code only returns results when a photo has the keyword "yellowstone river". But it should also return results for photos that have both keywords "yellowstone" and "river".

Posted: Fri Feb 09, 2007 4:41 pm
by mr_griff
I can get the desired results with this subselect query:

Code: Select all

SELECT photoID FROM photo_keywords WHERE keyword LIKE '%river%' AND photoID IN (SELECT photoID FROM photo_keywords WHERE keyword LIKE '%yellowstone%')
The problem is that with more than 2 keywords the queries are going to get quite complicated.