PHP/MySQL/searching for multiple keywords

Once again, I can't find a solution that works for what I'm doing.

[Ernos]

I know this was just brought up in another thread, but the suggested solution (MATCH AGAINST) doesn't seem to be working out for me. I'm trying to use explode() to separate and search for keywords in articles on my little search page, but instead of results for only pages with the keywords, I'm getting results for every page on my site, whether or not it has the keywords in it. Really weird.

This was my little proto-search engine (where you search for a single word or a phrase, works great):

<?php
$searchterm= trim($searchterm);
if (!$category ¦¦!$searchterm)
{
echo 'You have not entered search details. Please go back and try again.';
exit;
}
$category = addslashes($category);
$searchterm = addslashes($searchterm);
$dbh=mysql_connect ("localhost", "username", "password") or die ("Cannot connect to the database");
mysql_select_db ("db_name", $dbh);
$query = "select * from $category where article like '%".$searchterm."%'";
$result = mysql_query($query) or die('Error on page');
$num_results = mysql_num_rows($result);
echo '<P>Number of articles found: '.$num_results.'<BR>';

if ($category == 'categoryone') {
$cat='a';
}
elseif ($category == 'categorytwo') {
$cat='b';
}
elseif ($category == 'categorythree') {
$cat='c';
}
elseif ($category == 'categoryfour') {
$cat='d';
}

for ($i=0; $i <$num_results; $i++)
{
$row = mysql_fetch_array($result);
echo '<P><STRONG>'.($i+1).'. Date:</STRONG> ';
echo stripslashes($row['date']);

echo '<BR><STRONG>Article:</STRONG> ';
echo stripslashes('<A HREF=http://mysite.com/index.php?'.$cat.'='.$row['id'].'>');
echo stripslashes($row['title'].'</A><BR>');
echo '<P>';
}
?>

(I've got four separate tables of articles, named categoryone, etc., for categories -- the user chooses one category from a dropdown menu before searching for keywords, hence those elseif statements.)

I've tried using some examples of explode I've found, like so:

$search_array=explode(" ", $searchterm);
$search_num=(integer)count($search_array);
... ... ... ... ...
$query = "select * from $category ";
if (isset($searchterm))
$query .= " WHERE article LIKE";
for($i=0; $i < count($searchterm);$i++)
{
$query .= " '%".$searchterm[$i]."%'";
if ($i < count($searchterm)-1)
{
$query .= " AND article LIKE ";
}
}

I've also tried this:

if ($searchterm) {
$searchterm = trim($searchterm);
$searchterm = explode(" ", $searchterm);
}
... ... ... ... ...
$query = "SELECT * FROM $category ";
if (isset($searchterm)) {
$query .= " WHERE "; for($i = 0; $i < count($searchterm); $i++) {
$query .= "article LIKE '%" . $searchterm[$i] . "%'"; if ($i < count($searchterm)-1) {
$query .= " AND "; }
}
}

But for some reason, whenever I search for even a single word with this, the results page comes up with a list of every article in the category, rather than just the articles with the keyword.

I'm still a newbie at this and I'm trying to keep it as simple and uncomplicated as possible; I don't really care about search "relevance," I've looked around at the MATCH AGAINST and FULLTEXT stuff on mysql.com and it didn't seem to fit with what I'm trying to do. Much of the Googling I've done seems to show people having a whole separate table for keywords in their articles, and I really don't wanna do that either. Any suggestions?

[coopster]

At first glance, it seems as though your code is correct. You may also consider a

foreach

loop as opposed to the

for

loop to simply your code:

foreach ($searchterm as $term) { 
 $query .= " AND article LIKE '%$term%'"; 
} 

The one change I did notice is you are no longer adding slashes to your search terms, and I don't know if this is necessary or not since I don't know where the search terms are coming from. The browser? If so, are magic quotes on? Then you won't need to add slashes.