[RESOLVED] searching for occurrence of string(s)

[jennyp]

I set up a very simple search for a MySQL database. It returns a result if the search string occurs anywhere in the database record, and that's about it. I'd like to tweak it so that it when more than one word is entered, it will return a result if any of the words are present in the database.

I've tried exploding the search string by spaces and modifying the query, but I'm all messed up. Can anyone point me in the right direction? (or better give me an example!). Thanks!

My little script:

$Continue = 'OK';
$find = $_POST['find'];
$searching = $_POST['searching'];

if ($searching =='yes')
{

if ($find == "")
{
echo '<p>You didn\'t enter anything!</p>';
$Continue = 'NO';
}

if ( ($find !== "") && (strlen($find)) <= 3)
{
$Continue = 'NO';
echo '<p>Your search term must be longer than 3 characters!</p>';
}


if ( ($find !== "") && ($Continue == 'OK') )

{
// Database Connection
mysql_select_db('database_name', mysql_pconnect('location','user','password')) or die (mysql_error()); 

$find = strtoupper($find);
$find = strip_tags($find);
$find = trim ($find);

$data = mysql_query("SELECT * FROM table_name WHERE field_name LIKE'%$find%'");
$hits=mysql_num_rows($data);

while($result = mysql_fetch_array( $data ))
	{
$current = $result['field_name'];
echo '<br />'.$current;
	}
mysql_close();
	}

}

[bradgrafelman]

You could probably replace all spaces with a vertical bar ( | ) and use some regular expression like so:

$data = mysql_query("SELECT * FROM table_name WHERE field_name REGEXP '$find'");

so that your query would look like:

SELECT * FROM table_name WHERE field_name REGEXP 'word1|word2|word3|word4'

[jennyp]

Thanks... just after posting I came up with something (I hate that!) but I wonder if your suggestion is better:

$terms = explode(' ', $find);

$sql_query = 'SELECT * FROM database_name WHERE field_name LIKE \'%'.$find.'%\'';

foreach($terms as $key => $term)
{
$sql_query .= ' OR field_name LIKE \'%'.$term.'%\'';
}

$data = mysql_query($sql_query);
$hits=mysql_num_rows($data);

while($result = mysql_fetch_array( $data ))
	{
$current = $result['field_name'];
echo '<br />'.$current;
	}

[bradgrafelman]

No clue. Do some MySQL benchmarks and find out which one is faster. Probably depends on how many "words" you have... run some benchmarks, and start increasing the number of "words" each time.

[jennyp]

Thyanks for those tips