I've been having this problem for a while now...
I managed to make a search engine that searches a sql db. It works fine. If I just put in one word in the search field, it finds it no prob. But the problem comes up as soon as I type in more than word. It's supposed to find both words and print them both out, even if there's only one of those words were found, or if one word was found in one of the rows and the other was found in another.

I managed to get some help from some friends. But now it's returning EVERYTHING...even if the word is not in the table, it just prints out ALL the rows instead of just the ones which have the words.

This is how the code ended up:

PHP Code:

// database settings 
$Host = 'localhost'; 
$User = 'user'; 
$Pass = 'password'; 
$Base = 'base'; 

    mysql_connect($Host, $User, $Pass); 
mysql_select_db ($Base);
//Post the search 


//trims the whitespace before and after the searched terms
    $search = trim($_POST['search']);
    
    //replaces all of the extra white spacing with single spacing
    $search = preg_replace('/\s+/', ' ', $search);
    
$search_words = explode(" ", $search); //and now we break the spaces between words

//If user tries to link directly to the list.php w/out proper search 

if(!$search){ 
    echo "Type in a word to search it"; 
    } 
    else{ 
     
//Make the query possible 

$conta = count($search_words); //how many words are there to search?


//OK - I GUESS THERE IS SOMETHING WRONG HERE:

for ($i = 0; $i <= $conta; $i++) {
//ok, now search for each word separately:
$result = mysql_query("SELECT * FROM empresas WHERE servicos LIKE '%$search_words[$i]%' "); 
}
//IS THAT RIGHT??? IT SHOULD SEARCH FOR EACH INDIVIDUAL WORD...

//Call this result outside of the loop 
echo 'Results:'; 

//If the content thats searched for does not exist do this 

$num_rows = mysql_num_rows($result); 

if(!$num_rows){ 
    echo " nothing found"; 

}else{
while($iRow=mysql_fetch_array($result)) 
//the tables to be printed out:
{     
   $nome=$iRow["nome"]; 
   $servicos=$iRow["servicos"]; 
   $tel=$iRow["tel"]; 
   $end=$iRow["end"]; 
   $bairro=$iRow["bairro"];
   $cidade=$iRow["cidade"];
    
//print 'em out:
   echo ' 
<br> 
   <b>'.$nome.'</b> 
   <br>'.$servicos.'<br>
<b>Telefone:</b> '.$tel.' <br>
<b>Endereço:</b> '.$end.' - '.$bairro.' - '.$cidade.' 
<br><br><center><img src="hr.jpg"></center>
   '; 
            } 
        } 
    } 


Can anybody help me figure this out?

This is a better way to search.

In regards to your problem;

PHP Code:

for ($i = 0; $i <= $conta; $i++) {
//ok, now search for each word separately:
$result = mysql_query("SELECT * FROM empresas WHERE servicos LIKE '%$search_words[$i]%' ");
} 


The code snippet above is the reason it's not working as expected. Read over it again and you should see what is going wrong. Once you see the error, it is still a bad way to do things, what if you have 20 words ? That's 20 queries. Expensive queries once the table gets some size. Read the documentation on SELECT queries, the AND OR keywords would be of particular use here.

You are also not checking/sanatizing user input and are vulnerable to SQL injection attacks.

Ok first off, your for loop reuses the $result variable, so only the last one actually gets retained. You need to do something with $result before you run the next query, other wise the next query just overwrites it. I would process the first set of results and echo them out, then run the next query.
Also your loop is going round once too many - use < instead of <=. For two search terms it will loop with i=0, then i=1 and finally i=2. $search_words[2] has no value, and this might what is causing the whole database to be returned, since you are searching for things LIKE ''.

ok...so I've changed this:

PHP Code:

for ($i = 0; $i <= $conta; $i++) {   //ok, now search for each word separately:   $result = mysql_query("SELECT * FROM empresas WHERE servicos LIKE '%$search_words[$i]%' ");   } 


for this:

PHP Code:

for ($i = 0; $i < $conta; $i++) { 
//ok, now search for each word separately: 
$result = mysql_query("SELECT * FROM empresas WHERE servicos LIKE '%$search_words[$i]%' "); 
} 


that worked fine...but it only returns if both words are in the same row. And I need it to return all results...even if only one searched word is found on a specific row.
Is there anyway anybody can help me in a easy way??

Thanks for the answers above by the way

As Tress pointed out, there is a much better way to search with mysql. If your hosts has (I believe) any version over 4 of mysql then fulltext searching is the way to go because you don't have to program these what if situations where someone might put in multiple words, or even mean to search for a phrase. Fulltext searching will help you to get rid of these headaches for good and it's very very simple.

Got it working!
Thanks big time guys!