searching mysql database with multiple words

[tsinc80697]

Heylo

Ive created a search which Ive used in the past and it works fine, you put in 1 word like "blue" and it searches the title and description columns for that word. If it finds "blue" anywhere it shows them in the results.

But if I put in "blue metal" I want it to look for everything that has "blue metal", "blue" and "metal".

Any ideas to get me going ?
The search entry could be exploded on a space so it would become 2 seperate words and then they are each searched for, is that a good option or are there better ways to do this?


Thanks!

 

[tsinc80697]

Hi,

Im writing the SQL query's myself from scratch.

To do a single word search this is what I have done:

<?php
$search = $_GET['search'];
// FILTERING
$search = strtoupper($search);
$search = strip_tags($search);
$search = trim($search); 


// CHECK FOR EMPTY SEARCH AND DISPLAY ERROR MESSAGE
if ($search == "")
  {
 	echo "<div id=\"test\">";
  echo "<p>The search field was empty, please enter a search...</p>";
  echo "</div>";
  exit;
  }
  
$query = "SELECT DISTINCT name, price, package.package_ID FROM package, package_picture WHERE name LIKE \"%$search%\"";
$result = mysql_query($query);
$count = mysql_num_rows($result); 
echo "<div id=\"test\">";
echo "<b>Searched For:</b> " .$search;
echo "<br/><br/>";

        while($row = mysql_fetch_array($result))
				{
					echo "<a href=package.php?id=" . $row['package_ID'] . ">" . $row['name'] . "</a>";
					echo "<br/>";
					//echo $row['picture_ID'];
					echo "<br/>";
					echo "&pound;" . $row['price'];
					echo "<br/><br/>";
				}     


if ($count == 0)
{
echo "Sorry, but we can not find an entry to match your query<br><br>";
}
  
echo "</div>";
?>

Kronologic, I'd be looking for anything that contains either of the two words, or maybe more, could be anyhting between 1 and 5 words probably so how would I define how many Values to search for ?

select field1, field2, field3 from table where upper(field1) like upper('%Value1'||'%Value2%');

How would I make the Value1 || Value2 dynamic so that it adjusts to how ever many works people have entered?


Thanks!

 

[tsinc80697]

$pieces = explode(" ", $search);

$num_pieces = count($pieces);

That explodes whatever was put into the search box into an array of how ever many words.
On each loop I have the num_pieces -- so that it goes 2 1 0 and on 0 it stops looping.

Just need to figure out how to get it to loop through the number of times needed, ie 2 words in the search split up in an array which then query's the database twice but changes which word it searches.

Got it to loop through twice or three times, just cant figure out how to get it to search for the first line in the array and then the second

edit: mysql doesnt seem to be case sensitive on the search so the UPPER isn't essential as far as I can tell.

 

[tsinc80697]

thanks a lot works perfectly, just adding in a tick box for exact words and search by name, description or both