Struggling with keyword searching of text columns

[dancahill]

I'm a little confused.....I'm trying to implement a search page for an employee database.

My problem is that I need to search a text field in a mySQL DB using a search string on the HTML form. I'm trying to pass a text field from the form but it only works correctly when the keywords entered are in exactly the same order as those stored in the DB text column. If the same words are entered in a different order then it doesn't recognise the similarity. I guess I need to break down the words from the DB & compare them to all the keywords submitted???????

Basically I want to return a result if some or all of the search terms are in the DB entry regardless of the order in which they are stored.

NB I'm using mySQL 3.23.22beta for MS so cannot use FULLTEXT searching (which I'm beginning to regret).

Reeeeeallly grateful for any help on this guys.

Thanks.

[Mega]

str_replace(' ', '%', $formdata);

mysql_query("SELECT * FROM table WHERE field LIKE '%$formdata%'");

[dancahill]

Thanks Mega,

still not working though - here's a snippet:

If I pass 'dogs cats' through from 'key_word' on the form

<?
$key_word = $HTTP_POST_VARS["key_word"];

$key_word = str_replace(' ', '%', $key_word);

//just checking
echo $key_word;

//Prints dogs%cats

$query = "SELECT DISTINCT......
WHERE.......AND column_name LIKE '%$key_word%';

?>

Still gives me the same problem in that it will only return the result if the order of the words matches the order as stored in the DB. Similarly if you submit 3 words in the form & only 2 are in a particular row/record it won't bring it up as a valid result.

Thanks,

Dan.

[TGixer]

How about taking your key words
and breaking them each up into a separate key word

key_word1 = dog
key_word2 = cat

you would have to find the space and make word one, from char 1 to first ' ', and 2nd word from 1 ' ' +1, til 2nd ' '

then for your select

select * from table
where text like '%key_word1%'
OR '%key_word2%'
OR and so on.

just an idea....

[Julian_Pedley]

How about this:

<?
/*
1. Split the search string into n words with explode, which gives you an array... $search[n]
2. Trim any unwanted characters
3. Read the whole db field into a variable.. $field
4. Make a regex pattern string $pattern=$search[0].'|'.$search[1] (put this in a loop for unlimited number of search words)
5 $result= preg_match($pattern, $field)

I roughly tried it..

*/

$search[0]="dog";$search[1]="fox";
$field="the quick brown fox jumps over the lazy dog";
$pattern= '['.$search[0].'|'.$search[1].']';
$result= preg_match($pattern, $field);
echo "$result matches were found";

?>

Gives

1 matches were found

Use preg-match_all if you want to count the total number of matches.

Does this do what you want? For sure, somebody can think of a better way 🙂

[Mega]

Originally posted by Julian Pedley
How about this:

<?
/*
1. Split the search string into n words with explode, which gives you an array... $search[n]
2. Trim any unwanted characters
3. Read the whole db field into a variable.. $field
4. Make a regex pattern string $pattern=$search[0].'|'.$search[1] (put this in a loop for unlimited number of search words)
5 $result= preg_match($pattern, $field)

I roughly tried it..

*/


$search[0]="dog";$search[1]="fox";
$field="the quick brown fox jumps over the lazy dog";
$pattern= '['.$search[0].'|'.$search[1].']';
$result= preg_match($pattern, $field);
echo "$result matches were found";


?>

Gives

1 matches were found

Use preg-match_all if you want to count the total number of matches.

Does this do what you want? For sure, somebody can think of a better way 🙂

then if me and my 20 buddies all searched for:

a a a a s s s d d d f f f g gg h h h hhkh k k k k k k k k k k k o o o o o o i o io i oi oi i i i i i i i i i i ia a a a s s s d d d f f f g gg h h h hhkh k k k k k k k k k k k o o o o o o i o io i oi oi i i i i i i i i i i ia a a a s s s d d d f f f g gg h h h hhkh k k k k k k k k k k k o o o o o o i o io i oi oi i i i i i i i i i i ia a a a s s s d d d f f f g gg h h h hhkh k k k k k k k k k k k o o o o o o i o io i oi oi i i i i i i i i i i ia a a a s s s d d d f f f g gg h h h hhkh k k k k k k k k k k k o o o o o o i o io i oi oi i i i i i i i i i i ia a a a s s s d d d f f f g gg h h h hhkh k k k k k k k k k k k o o o o o o i o io i oi oi i i i i i i i i i i ia a a a s s s d d d f f f g gg h h h hhkh k k k k k k k k k k k o o o o o o i o io i oi oi i i i i i i i i i i i

what would your server do?

die most likely

$text = '';
$the_array = explode(' ', $formdata);
$i = 0;

foreach( $the_array AS $t ){
if( $i == 0 ){
$text .= " '%$t%' ";
$i = 1;
}else{
$text .= " OR field LIKE '%$t%' ";
}
}

mysql_query("SELECT * FROM table WHERE field LIKE $text");

obviously some length/number of elements checks would be needed 🙂

[dancahill]

Thanks alot for the help guys - will try this today & let you know.

Dan 🙂

[dancahill]

Originally posted by Mega


then if me and my 20 buddies all searched for:



what would your server do?

die most likely

$text = '';
$the_array = explode(' ', $formdata);
$i = 0;

foreach( $the_array AS $t ){
if( $i == 0 ){
$text .= " '%$t%' ";
$i = 1;
}else{
$text .= " OR field LIKE '%$t%' ";
}
}

mysql_query("SELECT * FROM table WHERE field LIKE $text");

obviously some length/number of elements checks would be needed 🙂 [/B]

Thanks alot Mega - worked nicely. Onwards & upwards!!

[netmastan]

$text = '';
$the_array = explode(' ', $formdata);
$i = 0;

foreach( $the_array AS $t ){
if( $i == 0 ){
$text .= " '%$t%' ";
$i = 1;
}else{
$text .= " OR field LIKE '%$t%' ";
}
}

mysql_query("SELECT * FROM table WHERE field LIKE $text");

How to light these words in the search result ??.