Page 1 of 1
Searching Multiple Tables for Search Results
Posted: Fri May 02, 2003 9:40 am
by mcyphers
Here is my problem. I have a site that has a collection of art. Users can search the site by putting in keywords and phrases that need to search 2 tables (Artists, Prints).
Example: User types in "Monet Waterloo". I need the search results to come back with any Artist/Print with keyword "Monet" and any Artist/Print with keywork "Waterloo".
It seems I need to first parse the keywords, and then write the SQL statement. I am using MYSQL so I can't use the UNION command. Any ideas.
Posted: Fri May 02, 2003 10:05 am
by []InTeR[]
You don't need a UNION, you need a
JOIN.
I still need to figure out how to parse the keywords
Posted: Fri May 02, 2003 10:09 am
by mcyphers
The current code is this, but as you can see it wouldn't parse keywords.
// Put the Search Items into an array for processing
/******************************************************/
if($criteriaString)
{
$collectionCriteria = "(".$criteriaString.")";
}
if($search)
{
$search = urldecode($search);
$search = str_replace(" ","%",$search);
$criteriaItems[] = "ap_prints.printName LIKE '%".addslashes($search)."%'";
$criteriaItems[] = "ap_prints.keywords LIKE '%".addslashes($search)."%'";
$criteriaItems[] = "ap_artists.artistName LIKE '%".$search."%'";
}
/******************************************************/
// Encode the Search Items and run the query
/******************************************************/
$criteriaString = makeSQLString($criteriaItems, "OR");
if($criteriaString)
{ $criteriaString = " WHERE ($criteriaString)"; }
if($collectionCriteria)
{
if($criteriaString)
{ $criteriaString .= " AND $collectionCriteria"; }
else
{ $criteriaString = " WHERE $collectionCriteria"; }
}
$sql = "select distinct ap_prints.height, ap_prints.width, ap_artists.artistName, ap_prints.r_printGroup, ap_prints.printID, ap_prints.printName, ap_prints.printCode from ap_prints LEFT JOIN ap_artists ON ap_prints.artistID=ap_artists.artistID LEFT JOIN print_collections ON ap_prints.printID = print_collections.printID ".$criteriaString. " ORDER BY ap_prints.printCode";