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).
match ALL keywords search query
Moderator: General Moderators
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));
}I can get the desired results with this subselect query:
The problem is that with more than 2 keywords the queries are going to get quite complicated.
Code: Select all
SELECT photoID FROM photo_keywords WHERE keyword LIKE '%river%' AND photoID IN (SELECT photoID FROM photo_keywords WHERE keyword LIKE '%yellowstone%')