Wildcard with LIKE operator in Meta query

Question

I have a form on a page that search property with a location field and I used the following meta query to process the search;

$location = preg_replace('/^-|-$|[^-a-zA-Z0-9]/', '', $_GET['location']); 

    $meta_query = array( 'relation' => 'AND' );
    if($location) {
        $meta_query[] = array(
            'relation' => 'OR',
            array(
                'key' => 'property_country',
                'value' => $location,
                'compare' => 'LIKE'
            ),
            array(
                'key' => 'property_city',
                'value' => $location,
                'compare' => 'LIKE'
            ),
            array(
                'key' => 'property_location',
                'value' => $location,
                'compare' => 'LIKE'
            )
        );
    }

However, it works fine if I search for New but returns no results if I search for New York. How can I resolve this? Is there a way to add a wildcard before and after the $location? I tried adding '*'.$location.'*' but that did nothing.

Answer 1

Have you var_dumped $location?

WP_Query's meta_query uses the class WP_Meta_Query, which automatically appends and prepends '%' to any string passed to it:

// From wp-includes/class-wp-meta-query.php:610 
switch ( $meta_compare ) {
            // ...
            case 'LIKE' :
            case 'NOT LIKE' :
                $meta_value = '%' . $wpdb->esc_like( $meta_value ) . '%';
                $where = $wpdb->prepare( '%s', $meta_value );
                break;
            // ...
}

So maybe try using (string) $location as the sql statement is prepared, and expecting a string -- that's what the %s means in the line $where = $wpdb->prepare( '%s', $meta_value );. You can cast your variable to a string like this:

$location = (string) preg_replace('/^-|-$|[^-a-zA-Z0-9]/', '', $_GET['location']);

Answer 2

Try joining the string with wildcards:

$location = implode('%', explode($location, ' '));