php / mysql search query string

Question

i'm trying to search terms that may or may not have a space... ex term: iPod Black 30. the results would be everything except, "ipod black used 30" and "used". so basically its finding all complete matching terms individual matching terms and skipping over any entry that may match but has a non-match word in it and of course the entry where nothing matches.

table:
prod
-- ipod black 30
-- ipod 30
-- ipod black used 30

types
-- ipod
-- 30
-- black
-- used

Answer 1

Break you search term into individual strings. Then use the individual strings in your query for an exact match across the tables.

Sample Code:

$terms = array();
$terms = explode(" ",$searchstr);

$sql = "select fields from tables where";

$first = true;

foreach($terms as $term)
{

  if(!$first) $sql .= " OR "

  $sql .= "fieldA like ('%".trim($term)."%')";

  $first = false;

}

// execute query!

Answer 2

Can you create new tables? For example, you could create a "terms" table which has many to many relationships with prod and types tables. Then, you could implement this logic like this:

-- get the set of all products which contain your terms
select p.id from prod p
inner join prod_terms pt on (p.id = pt.prod_id)
inner join terms t on (pt.term_id = t.id)
where t.term IN (<dynamically built list of terms>)
-- exclude products which contain terms not in your query
and not exists (
    select pt.id from prod_terms pt2
    inner join terms t2 on (pt.term_id = t.id)
    where t2.term NOT IN (<dynamically built list of terms>)
) other_terms

Then you can do something similar for the types table.

caveats:

• I'm more familiar with SQL Server, the syntax may require tweaking for MySQL
• The query illustrates the general idea but may result in a slow query plan, so be sure to do perf tuning