Search for exact whole word within a string of words

I need to add a textbox to search the exact whole word, within a string of words. But only if it has that exact whole word and not be part of another word. Basically I need to set the spaces as the word boundary when looking for the search term.

For example if I have a name column that stores the entire name and I need to search for all names that have “Alan” in them.

John Alan Doe
Johnny Smith
Alana Miller
Sally Johnson
Alan Alda

If I use Includes to compare, it would return 3 names including Alana Miller. If I use = to compare, it would return nothing.

The desired return would be the 2 names (ignoring Alana Miller):
John Alan Doe
Alan Alda

Is this possible or should I be using preg_match some how? I see that preg_match is already built into WA.

Thanks,
TroyD

What are you using for the search? Is this mysqli? are you using the recordset directly or the search server behavior? Is that the only search option? I need more information in order to set you in the right direction.

Sorry Ray. I'm not sure why I didn't include all the details.

It's mysqli. The recordset queries the entire list of records to begin with, then a search behavior was added that will currently contain just the one textbox, but another might be added later.

Thanks,
Troy

Add three searches to the search server behavior.

Begins with, Ends with, and includes

For the begins with search add a space at the end
For the ends with search add a space in the beginning
For the includes search add a space on both sides.

That way it would find:
"Alan "Alda
with the begins with search

and
John" Alan "Doe
with the includes search

and
Barry" Alan"
with the ends with search

Thanks Ray,

Very clever idea. So I created the search behavior with the 3 searches and bound them to the text box post value, but I'm not sure where you meant to add the spaces. Do I add them to the post value in the behavior?

Here is the behavior currently without any spaces added.

$searchrsNameList_1 = new WA_MySQLi_Search("rsNameList","0=0");

if ((((isset($_POST["name_search"]))?$_POST["name_search"]:"") != "")) {

  $searchrsNameList_1->clearSearch();

  $searchrsNameList_1->setSearch(array("type"=>"Key", "comparison"=>"Begins With", "join"=>"AND", "and"=>", ", "or"=>" ", "start_encap"=>"\"", "end_encap"=>"\""), array("name"), "s", "".((isset($_POST["name_search"]))?$_POST["name_search"]:"")  ."");

  $searchrsNameList_1->setSearch(array("type"=>"Key", "comparison"=>"Ends With", "join"=>"AND", "and"=>", ", "or"=>" ", "start_encap"=>"\"", "end_encap"=>"\""), array("name"), "s", "".((isset($_POST["name_search"]))?$_POST["name_search"]:"")  ."");

  $searchrsNameList_1->setSearch(array("type"=>"Key", "comparison"=>"Includes", "join"=>"AND", "and"=>", ", "or"=>" ", "start_encap"=>"\"", "end_encap"=>"\""), array("name"), "s", "".((isset($_POST["name_search"]))?$_POST["name_search"]:"")  ."");

}

Thanks,
TroyD

Try this:

$searchrsNameList_1 = new WA_MySQLi_Search("rsNameList","0=0");

if ((((isset($_POST["name_search"]))?$_POST["name_search"]:"") != "")) {

  $searchrsNameList_1->clearSearch();

  $searchrsNameList_1->setSearch(array("type"=>"Key", "comparison"=>"Begins With", "join"=>"AND", "and"=>", ", "or"=>" ", "start_encap"=>"\"", "end_encap"=>"\""), array("name"), "s", "".((isset($_POST["name_search"]))?$_POST["name_search"] . " ":"")  ."");

  $searchrsNameList_1->setSearch(array("type"=>"Key", "comparison"=>"Ends With", "join"=>"AND", "and"=>", ", "or"=>" ", "start_encap"=>"\"", "end_encap"=>"\""), array("name"), "s", "".((isset($_POST["name_search"]))?" ".$_POST["name_search"]:"")  ."");

  $searchrsNameList_1->setSearch(array("type"=>"Key", "comparison"=>"Includes", "join"=>"AND", "and"=>", ", "or"=>" ", "start_encap"=>"\"", "end_encap"=>"\""), array("name"), "s", "".((isset($_POST["name_search"]))?" ".$_POST["name_search"]." ":"")  ."");

}

Thanks again Ray,

That worked once I changed the join from "AND" to "OR".
I noticed that if I commented out 2 of the searches, it would work to find the one search, whether it was the begins with our ends with. It would find records that matched that position. Then it dawned on me, it was looking for all of those conditions. I changed it to "OR" and it works. It's doing exactly what I need it to. Appreciate the help.

Thanks,
TroyD

Ray,

I spoke too soon and found that there is still one issue. It does a good job finding the names in a group of names but records are also returned that have that searched name within a sub string.

Using my list of example names again, it finds the "Alan" but it also returns "Alana". It's the third search I think where includes seems to find the "Alan" within "Alana" even though I have a blank space before and after the compared post value. Can you see anything I'm missing?

Currently this is what the search behavior looks like:

$searchrsNameList_1 = new WA_MySQLi_Search("rsNameList","0=1");

if ((((isset($_POST["name_search"]))?$_POST["name_search"]:"") != "")) {

  $searchrsNameList_1->clearSearch();

  $searchrsNameList_1->setSearch(array("type"=>"Key", "comparison"=>"Begins With", "join"=>"OR", "and"=>", ", "or"=>" ", "start_encap"=>"\"", "end_encap"=>"\""), array("name"), "s", "".((isset($_POST["name_search"]))?$_POST["name_search"]." ":"")  ."");

  $searchrsNameList_1->setSearch(array("type"=>"Key", "comparison"=>"Ends With", "join"=>"OR", "and"=>", ", "or"=>" ", "start_encap"=>"\"", "end_encap"=>"\""), array("name"), "s", "".((isset($_POST["name_search"]))?" ".$_POST["name_search"]:"")  ."");

  $searchrsNameList_1->setSearch(array("type"=>"Key", "comparison"=>"Includes", "join"=>"OR", "and"=>", ", "or"=>" ", "start_encap"=>"\"", "end_encap"=>"\""), array("name"), "s", "".((isset($_POST["name_search"]))?" ".$_POST["name_search"]." ":"")  ."");

}

Thanks,
TroyD

This is the issue: "or"=>" ",

That makes spaces automatically convert to "or" statements... just change that to: "or"=>"",

in all three locations.

Thanks Ray,

That fixed it.

TroyD