[RESOLVED] Search oddness

Double_M

Hi all,

Well ive got a little search form searching multiple strings in all fields in a database. It seems to work but if i want to search for one word like "apple" it will not find it even though it is of course there. Or even a serial number wont work either. But i can find single letters and numbers up to 3 characters long.

Anyone know why it will not find sinlge words like "apple" or "zebra" but will find single letters and numbers no matter how many strings are entered?

if ($search)   

   { 
    //Connect to database
    mysql_select_db($database_localhost, $localhost); 

$searchwords = addslashes(htmlspecialchars($_POST['search'])); 
$words = explode(' ',$searchwords); 
$totalwords = count($words); 
$i = 0; 
$string = ""; 
foreach ($words as $key => $word)
{
	echo "$word<br>";
	//while ($i != $totalwords){ 
	if ($i != 0 and $i != $totalwords){ 
	$string .= " and ";; 
	} 
	$string .= "Partnumber LIKE '%$word%' or Description LIKE '%$word%'
			or Partnumber LIKE '$word%' or Description LIKE '$word%' 
			or Partnumber LIKE '%$word' or Description LIKE '%$word'
			or Partnumber LIKE '$word' or Description LIKE '$word'";

	// Incrementing the value 
	$i = $i + 1; 
	//}
} 

echo $string;

//mysql selection
$query_Search = "SELECT * FROM products WHERE $string"; 
$Search_set = mysql_query($query_Search, $localhost) or die(mysql_error());
$row_Search_set = mysql_fetch_assoc($Search_set);

Any help much apreciated.

bike5

All you need is
Partnumber LIKE '%$word%' or Description LIKE '%$word%'
that includes all your other variations you put in. Probably wont help the results but cleans things up some.

bradgrafelman

Look at using Full-Text Searching in MySQL.

Basically, you'd add a fulltext index on your table that encompasses both the Partnumber and Description columns, and use a query similar to:

SELECT `column1`, `column2` FROM `myTable` WHERE MATCH(`Partnumber`, `Description`) AGAINST ('apple')

Also, I notice that you currently have "SELECT *" which isn't always needed (more often that not, actually, it isn't). Are you positive you're going to use all of the columns in the table? If not, name the columns you will be using instead of SELECT'ing them all.

Double_M

Thanks for the replys.

But is there a way to just find a phrase by using the code i have? Im not sure how to go about using the full text searching you suggested. What about "strpos"?

bradgrafelman

If you don't want to utilize the fulltext searching functions, then just follow bike5's suggestion.

Double_M

I cant get the fulltext working :/. There are many duplicate fields so maybe that wont work for me? And i have just "Partnumber LIKE '%$word%'" and it still doesnt find a serial number like "HYWZ879" for example. But it will find single characters. 🙁

And select everything "SELECT *" so i can view the whole row when the result comes through.

Any help?

Double_M

Well i got it sussed now 🙂 my problem was the output and not the search function itself heh. Thanks alot for all your help.