searching mysql database with multiple words

tsinc80697 · 3 May 2007 at 12:00

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!

Manlove is my forte · 3 May 2007 at 13:41

are you writing the SQL manually or are you using a program to dynamically generate the query?

If I was doing something like this in Oracle my queries would be:

This would work if you are looking for either word but that was the only word in the field
select field1, field2, field3 from table where upper(field1) in upper(('Value1','Value2'));

If I was looking for an exact string :
select field1, field2, field3 from table where upper(field1) = upper('word word2');

If I was looking for rows that contained any reference to the words I would use something like
select field1, field2, field3 from table where upper(field1) like upper('%Value1'||'%Value2%');

|| is a concatenate function.

If I was looking for any combinations of the words I would use
select field1, field2, field3 from table where upper(field1) like upper('%Value1%') or upper(field1) like upper('%Value2%');

JIMA · 3 May 2007 at 14:02

Hi,

I believe MySQL can perform selects based on regular expressions. You could construct a number of regular expressions dynamically to match different search criteria and then combine them in a select either using "or" conditions in the regexp or the Select statement. For example:

'^.*blue\s+metal.*$'
Search for the exact string with at least one space between the words.

'^.*blue.*metal.*$'
Searches for the words blue metal in that order but placed anywhere in the string.

Searching on the words in any order would require different regexps.

Hope that helps,
Jim

tsinc80697 · 3 May 2007 at 14:19

Hi,

Im writing the SQL query's myself from scratch.

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

PHP:

<?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!

Manlove is my forte · 3 May 2007 at 15:11

tsinc80697 said:
Hi,

Im writing the SQL query's myself from scratch.

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

PHP:

<?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 "£" . $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!

I think the query that would return what you want is

If I was looking for any combinations of the words I would use
select field1, field2, field3 from table where upper(field1) like upper('%Value1%') or upper(field1) like upper('%Value2%');

You would need to use either PHP or java script (probably PHP) to determine the number of values entered into the search field and then use a loop to itterate through each word adding it into the query

you have to be kind of clever about it as if you have only one word you will not need the word "or" and if you have more than one word you will not need the word "or" after the last word.

I'm afraid learning PHP is still on the todo list so I cant give you any code examples.

BTW I use the upper() to turn the field and search criteria into upper case to remove any case sensitivity in the query. Results are displayed in the correct case. I'm not sure if MySQL is case sensitive or if it uses the upper() syntax. All my SQL knowledge is oracle based I'm afraid.

tsinc80697 · 3 May 2007 at 15:25

PHP:

$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.

JIMA · 3 May 2007 at 22:00

Hi,

If you wanted to follow the Regular Expression solution up you could use a regexp like this to match words in a string that occur in any order:

'^(?=.*?\bblue\b)(?=.*?\bmetal\b).*$'

The above would look for the words blue and metal anywhere in a string and in any order, matching if both words are found.

Assuming MySQL supports lookaheads in regexps you should be able to do something like:

SELECT mystring
FROM mystringtable
WHERE mystring REGEXP("^(?=.*?\bblue\b)(?=.*?\bmetal\b).*$");

For each word you look for construct a (?=.*?\mywordhere\b) piece of regexp and concatenate them together, putting the finished thing in your SQL.

Unfortunately I can't test this as I can't get my MySQL to run for some reason. However, it does work in a Perl script I wrote so it's possibly worth a shot.

Jim

robswan · 3 May 2007 at 23:56

JIMA's solution should work, although there's no real need to use regular expressions for something like this.

To find any results with "blue metal", "blue" or "metal" you just have to search for either of the words. There's no need to specifically search for "blue metal" because that will already be found using the individual words.

One SQL query you could compile looks like this:

SELECT DISTINCT name, price, package.package_ID FROM package, package_picture WHERE (`name` LIKE '%blue%') OR (`name` LIKE '%metal%')

You just need to compile that search string

Hopefully this snippet should help explain one approach to compiling such a query from a string:

PHP:

<?php

// This is emulating your search string
$search = "blue metal";

// We explode the search string based on the spaces, and then loop through our array
foreach(explode(" ", $search) as $value) {
	//For each individual word we are searching for we write an SQL query
	$critera[] = "name LIKE '%-$value-%'";
}

// Then we recompile those SQL queries with an 'OR' in between
$searchCritera =  'WHERE (' . join(' or ',$critera) . ')'; 

// Append that search criteria to the query and you're done
$query = "SELECT DISTINCT name, price, package.package_ID FROM package, package_picture $searchCritera";

// This is just echoing it out to show you what it's done
echo $query ;

?>

This will work for an unlimited number of search queries provided they are sperated by spaces. Hope that's of some help!

tsinc80697 · 4 May 2007 at 09:51

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