meta_query search names when they have middle initials - PHP, WordPress

Question

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)?

Actually, WordPress meta queries support `REGEXP` as the `compare` value.. So you could use regular expression search. — Sally CJ, Jun 02 '21 at 13:16
@SallyCJ I've tried with REGEXP as a compare value but it isn't working. — ah92, Jun 02 '21 at 13:34
What is the code you tried with? You were not using the same `value` as it is currently in the question, were you? I mean, keep in mind that special characters like `.` ("dots") need to be properly escaped. — Sally CJ, Jun 02 '21 at 13:56
@SallyCJ, I've tried by replacing the "LIKE" with "REGEXP" and I left it the same as the other parts of the code. — ah92, Jun 02 '21 at 14:00
Well, no wonder it didn't work.. You need to supply a valid regex pattern like `John.*Doe` as the `value`. However, if by "members" you're referring to users in the WordPress users table (`wp_users`), then WordPress actually adds two custom fields (`first_name` and `last_name`) for each user, so you could just use those fields and as for the middle initial, you could use another field, e.g. `middle_initial`, and then you would just query those fields with an `OR` relation.. — Sally CJ, Jun 02 '21 at 14:15
@SallyCJ No, the search queries a custom field "_full_name" in which are stored the full names (including first name, last name, and middle initial, e.g John B.Doe). I can't modify the structure since there a lot of data stored in this way. — ah92, Jun 02 '21 at 15:11

score 1 · Accepted Answer · answered Jun 03 '21 at 01:55

First off, if only WordPress doesn't escape the % characters in the value (i.e. the search keyword), you could've simply replaced spaces in the search keyword with %, hence you'd get a clause like meta_value LIKE '%John%Doe%' which would match John B. Doe.

So because the % in the search keyword is being escaped (which is a good thing, BTW), then you can instead use REGEXP (regular expression search) and then replace the spaces with a .* which is equivalent to the % in a LIKE clause.

Working Example

Replace this in your code:

$args['meta_query']['name_search']['compare'] = "LIKE";
$args['meta_query']['name_search']['value'] = $_POST['search'];

with this:

// Build the regular expression pattern.
$list = preg_split( '/ +/', trim( $_POST['search'] ) );
$regex = implode( '.*', array_map( 'preg_quote', $list ) );

// Then set 'compare' to REGEXP and 'value' to the above pattern.
$args['meta_query']['name_search']['compare'] = 'REGEXP';
$args['meta_query']['name_search']['value'] = $regex;

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 not REGEXP, 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.

/*
 * Assuming your form contains these:
    <input name="first_name">
    <input name="last_name">
    <input name="middle_initial">
 */

$name_search = array(
    'relation' => 'AND',
);

// * okay, this still uses 3 clauses, but it always would be just 3 clauses
foreach ( array( 'first_name', 'last_name', 'middle_initial' ) as $name ) {
    if ( ! empty( $_POST[ $name ] ) ) {
        $name_search[ $name ] = array(
            'key'     => '_full_name',
            'value'   => sanitize_text_field( $_POST[ $name ] ),
            'compare' => 'LIKE',
        );
    }
}

$args['meta_query']['name_search'] = $name_search;

score 0 · Answer 2 · answered Jun 01 '21 at 02:31

I can suggest the following solution:

$search = $_POST['search'];
$words  = explode( $search, ' ' );
$words  = array_filter(
    $words,
    function ( $word ) {
        return ! empty( trim( $word ) );
    }
);

foreach ( $words as $index => $word ) {
    $query_name          = "name_search_$index";
    $args['meta_query'][ $query_name ] = [
        'key'     => '_full_name',
        'value'   => $word,
        'compare' => 'LIKE',
    ];
}

Code explanation:

I split search phrases into words. Words are any sequence of symbols between spaces
I remove spaces. If your search string is
```
John Doe
```
or
```
John  Doe
```
or
```
   John        Doe   
```
The search result should be the same.
I build multiple queries. Every query corresponds to a single word. If you search John Doe the query builds by 2 queries. Queries count as much as the number of words.

The problems with this solution:

Too many words are too many queries. Too many LIKE-containing queries are too slow.
You will get the same result for searching for
```
John Doe
```
and
```
Doe John
```
. The order of the words makes no difference to the result.

I recommend using this solution only as a temporary solution. Good solution - is using Full-Text Search plugins or write Full-Text Search solution yourself. More details about FullText search you could find on MySQL official page: https://dev.mysql.com/doc/refman/8.0/en/fulltext-search.html