search DB based on strings n words

[sol01]

I am doing a database search based on the entire string as well as the words in the string. I believe you have to break up the string in order for the select statement to work with single words (not the full string)

I need to break up the string into all the words contained (in a variable per word)but how can I shove those variables into my select statement.

say I do $query = "my blue dog";

I want to have 4 variables

$a = $query; //origonal string
$b = "my";
$c = "blue";
$d = "dog";

then I can search the database for those. However what if the query is longer then three words... How can I have the select statement dynamically change to acomodate for more words?

Say $query = "my blue dog eats black fish every day";

I need to split that to 9 variables:

$a = $query; //origonal strring
$b = "my";
$c = "blue";
$d = "dog";
$e = "eats";
$f = "black";
$g = "fish";
$h = "every";
$i = "day";

Then my select statement would need to auto update to account for the extended amount of search words.

Any detailed help would be much appreciated.

[Anon]

This probably isn't as detailed as you'd like - but I don't have a database to test on right here (and I'm too lazy to ssh to a machine where I do).

1) Take your string $query.

2) Might there be multiple consecutive spaces? Collapse them to single spaces: $query=preg_replace('/\s+/',' ',$query);

3) $query_array=explode($query);

And there's your array of words. Better yet:

4) $query_array=array_unique($query_array);

Now, when you're asking for a "search word" I guess right now you're doing something like "WHERE message LIKE '%word%'", right? Okay: we'll take each word in the array and turn it into "message LIKE '%word%'". Note: at this point one might have to worry about apostrophes in the words - deal with those whoever you're doing it now. Anyway:

foreach($query_array as $entry=>$word)
{ $query_array[$entry] = "message LIKE '%$word%'";
}

Now the array has been turned into SQL clauses - we want to join them together into one because clause with OR if I'm not mistaken:

$query=join(' OR ',$query_array);

and now $query is seomthing that can be stuck into a SQL SELECT query following the WHERE.

That's just a rough-and-ready guide. Please, anyone else with improvements (or replacements) feel free to chip in.

[sol01]

TX for the advice, I tried testing your code and I got errors. this is what I did:

$expo=preg_replace('/\s+/',' ',$query); 
$query_array=explode($expo); 

$query_array = array_unique($query_array); 

foreach($query_array as $entry=>$word) {
$query_array[$entry] = "comp_name LIKE '%$word%'"; 
	}

$search_query = join(' OR ',$query_array);

$result = mysql_query("SELECT * from company_data WHERE $search_query");

errors i got:

Warning: Wrong parameter count for explode() in index.php on line 84

Warning: Wrong datatype in array_unique() call in index.php on line 86

Warning: Invalid argument supplied for foreach() in index.php on line 88

Warning: Bad arguments to join() in index.php on line 92

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in index.php on line 102

[Anon]

The first error is the giveaway - I miswrote the explode() call. (It's strangely difficult to write in this textbox).

A 15-second peek at the manual would have revealed that the line should read:

$query_array=explode(' ',$expo);

[sol01]

Yeh thats excelent!! Yeah I know what you mean about checking the manual. I checked it right after posting and I found the answer, thanks for taking the time with it.

I had another question and that was in regards to searching through more then one feild.

$expo=preg_replace('/\s+/',' ',$query);
$query_array=explode(' ', $expo);

$query_array = array_unique($query_array); 

foreach($query_array as $entry=>$word) {
$query_array[$entry] = "comp_name LIKE '%$word%'"; 
	}

$search_query = join(' OR ',$query_array);

$result = mysql_query("SELECT * from company_data WHERE $search_query");

INitially I would think it needs to be somewhere in the foreach loop. But then Join is an issue. Can anyone help me figure out how to search through more tables then comp_name? Thanks in advance....

[Anon]

You mean more columns than comp_name?

"comp_name LIKE '%$word% OR other_column LIKE '%$word%"

I'm still wondering if there's a cleaner approach.