Searching Multiple Tables for Search Results

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
mcyphers
Forum Newbie
Posts: 2
Joined: Fri May 02, 2003 9:40 am

Searching Multiple Tables for Search Results

Post 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.
[]InTeR[]
Forum Regular
Posts: 416
Joined: Thu Apr 24, 2003 6:51 am
Location: The Netherlands

Post by []InTeR[] »

You don't need a UNION, you need a JOIN.
mcyphers
Forum Newbie
Posts: 2
Joined: Fri May 02, 2003 9:40 am

I still need to figure out how to parse the keywords

Post 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";
Post Reply