Narrow search results from mysql database keywords

I have a large database containing part numbers for a filter company. Several of the products have similar years and model numbers. I have a php script that is searching a text keyword section for each part.

So for a 1997-1999 Honda Crf 250 090987 19.95 the keywords are: 1997 1998 1999 honda crf 250 090987

I got the code from a tutorial and it's written to search the keywords section for everything separated by a space. The problem is that a lot of the listings have honda or similar year spreads.

I need to figure out how to alter the php to narrow the results so if someone types "1997 honda", all of the hondas and '97s don't show up but only ones that are 1997 hondas.

I'm new to php and I've looked all around and can't figure it out. Any help will be thoroughly appreciated.

here's the php:

$keywords = $_GET['keywords'];
$terms = explode(" ", $keywords);
$query = "SELECT * FROM search WHERE ";

foreach ($terms as $each){
    $i++;
    if ($i == 1)
        $query .= "keywords LIKE '%$each%' ";
else
    $query .= "OR keywords LIKE '%$each%' ";

}


// connect
mysql_connect("localhost", "root", "root");
mysql_select_db("catalog");

$query = mysql_query($query);
$numrows = mysql_num_rows($query);
if ($numrows > 0){

    while ($row = mysql_fetch_assoc($query)){
        $id = $row['id'];
        $year = $row['year'];
        $manufacturer = $row['manufacturer'];
        $product = $row['product'];
        $partnumber = $row['partnumber'];
        $price = $row['price'];

        echo "<div id=\"results\">
        <div class=\"words\">$year</div><div class=\"words\">$manufacturer</div><div class=\"words\">$product</div><div class=\"words\">$partnumber</div>$price<br /></div>";
    }
}
else
    echo "<div class=\"no_results\"><center>No results found for \"<b>$keywords</b>\"</center></div>";


// disconnect
mysql_close();

Solution

Interesting problem.

I thought of 2 approaches:

(1) if you have control over the database structure, you could SELECT WHERE (make = 'Honda' AND Year IN (1997,1998,1999)) AND the "like" stuff you already have (without the make/year). But you would need to separate out the model years and make into separate fields.

(2) otherwise, if we can assume the input is always "year(s) make [other keywords], we could do what izuriel suggested while I was typing this, just "AND 'keywords = '1997' AND keywords = 'honda' AND (our existing list of "like" clauses)".

that way you'd get only 1997 Hondas that have any or all of the other keywords.