I have to build a search form where I search members of the company by their Full Name (First Name + Middle Initial + Last Name). All the names are in this form: John B. Doe.
The below code is working on these cases: John, John B., Doe, B., John B. Doe, but not working the query search is: John Doe
.
if (isset($_POST['search']) && $_POST['search'] != -1) {
$args['meta_query']['name_search']['key'] = "_full_name";
$args['meta_query']['name_search']['compare'] = "LIKE";
$args['meta_query']['name_search']['value'] = $_POST['search'];
}
How should I improve the query in order to work with both: the name and surname (John Doe) and the name, surname plus the middle initial (John B. Doe)?
First off, if only WordPress doesn't escape the
%
characters in thevalue
(i.e. the search keyword), you could've simply replaced spaces in the search keyword with%
, hence you'd get a clause likemeta_value LIKE '%John%Doe%'
which would matchJohn B. Doe
.So because the
%
in the search keyword is being escaped (which is a good thing, BTW), then you can instead useREGEXP
(regular expression search) and then replace the spaces with a.*
which is equivalent to the%
in aLIKE
clause.Working Example
Replace this in your code:
with this:
Tried & tested working in WordPress 5.7.2, but do take note of the "not multibyte safe" warning in the MySQL reference manual.
Alternate Solution (equivalent to the one above)
If you want to use
LIKE
and notREGEXP
, but don't want to end up with potentially lots of meta queries for the same key, then you can:Use three search fields, namely first name, last name, and middle initial, and three meta clauses, one for each search field, but all having
key
set to_full_name
. E.g.