How to perform a variable word count search?

I have an input box where users can input word(s) to search through the database and I'm using Perl DBI and placeholders for the WHERE LIKEs for each field, but how would you get this to work for multiple word searches?

For example, as I have it set up right now, if a user searched for "John Doe" (and a record exists that has "John" in the firstName field and "Doe" in the lastName field, 0 matches will be returned because not one of the fields contains the entire string of "John Doe".

Obviously, this is not good. Is there any way around this?

Thanks,
RyanP

[This message has been edited by RyanP (edited June 29, 2000).]

Why don't you just put two search field: firstName & lastName?


------------------
---------
Stories OnLine
http://www.stories-online.net/

WARNING: adult stories!

There are several other fields in the table too and I have included an optional pull down menu to select a particular field to search in, but I also want to provide the option to search in all fields with multiple word searches.

if the $searchstring is "John Doe"....

# split $searchstring between occurrence(s) of spaces

@search_list=split(/ +/, $searchstring);

$sql="select * from table_name where ";

for($i=0; $i<@search_list; $i++)
{
$sql.="field_name like '%$search_list[$i]%' OR ";
}

# now remove trailing "OR "

$sql=substr($sql, 0, -3);

execute the query....

[This message has been edited by Kyuzo (edited June 30, 2000).]

Thanks Kyuzo, that is just what I was looking for. Although I would prefer to use placeholders and bind_params, this is probably the only way to do this. Because I will be inserting variables directly into the query statement, what characters do I need to filter out of the search string? Just single quotes with ' ?

[This message has been edited by RyanP (edited June 30, 2000).]

Actually there is one other better way to do this using regular expressions.
$string = "John Doe";
$string =~ s/ /|/g;
this replaces spaces with the | character. You end up with John|Doe
Then the query is:
$sql = "SELECT * FROM table_name WHERE lcase(field_name) REGEXP lcase($string)";
The query can be extended to search multiple fields by using OR AND etc.