match ALL keywords search query

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
mr_griff
Forum Commoner
Posts: 64
Joined: Tue Sep 17, 2002 11:11 am
Location: Bozeman, Montana

match ALL keywords search query

Post 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).
User avatar
louie35
Forum Contributor
Posts: 144
Joined: Fri Jan 26, 2007 8:40 am
Location: Dublin
Contact:

Post 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));
	}
User avatar
mr_griff
Forum Commoner
Posts: 64
Joined: Tue Sep 17, 2002 11:11 am
Location: Bozeman, Montana

Post 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".
User avatar
mr_griff
Forum Commoner
Posts: 64
Joined: Tue Sep 17, 2002 11:11 am
Location: Bozeman, Montana

Post 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.
Post Reply