PHP - Wordpress - Search - wordpress custom search function that encompasses ACF/advanced custom fields and taxonomies and split expression before request

custom-search-acf-wordpress.php

<?php

/**
 * [list_searcheable_acf list all the custom fields we want to include in our search query]
 * @return [array] [list of custom fields]
 */
function list_searcheable_acf(){
  $list_searcheable_acf = array("title", "sub_title", "excerpt_short", "excerpt_long", "xyz", "myACF");
  return $list_searcheable_acf;
}


/**
 * [advanced_custom_search search that encompasses ACF/advanced custom fields and taxonomies and split expression before request]
 * @param  [query-part/string]      $where    [the initial "where" part of the search query]
 * @param  [object]                 $wp_query []
 * @return [query-part/string]      $where    [the "where" part of the search query as we customized]
 * see https://vzurczak.wordpress.com/2013/06/15/extend-the-default-wordpress-search/
 * credits to Vincent Zurczak for the base query structure/spliting tags section
 */
function advanced_custom_search( $where, &$wp_query ) {

    global $wpdb;
 
    if ( empty( $where ))
        return $where;
 
    // get search expression
    $terms = $wp_query->query_vars[ 's' ];
    
    // explode search expression to get search terms
    $exploded = explode( ' ', $terms );
    if( $exploded === FALSE || count( $exploded ) == 0 )
        $exploded = array( 0 => $terms );
         
    // reset search in order to rebuilt it as we whish
    $where = '';
    
    // get searcheable_acf, a list of advanced custom fields you want to search content in
    $list_searcheable_acf = list_searcheable_acf();

    foreach( $exploded as $tag ) :
        $where .= " 
          AND (
            (wp_posts.post_title LIKE '%$tag%')
            OR (wp_posts.post_content LIKE '%$tag%')
            OR EXISTS (
              SELECT * FROM wp_postmeta
	              WHERE post_id = wp_posts.ID
	                AND (";

        foreach ($list_searcheable_acf as $searcheable_acf) :
          if ($searcheable_acf == $list_searcheable_acf[0]):
            $where .= " (meta_key LIKE '%" . $searcheable_acf . "%' AND meta_value LIKE '%$tag%') ";
          else :
            $where .= " OR (meta_key LIKE '%" . $searcheable_acf . "%' AND meta_value LIKE '%$tag%') ";
          endif;
        endforeach;

	        $where .= ")
            )
            OR EXISTS (
              SELECT * FROM wp_comments
              WHERE comment_post_ID = wp_posts.ID
                AND comment_content LIKE '%$tag%'
            )
            OR EXISTS (
              SELECT * FROM wp_terms
              INNER JOIN wp_term_taxonomy
                ON wp_term_taxonomy.term_id = wp_terms.term_id
              INNER JOIN wp_term_relationships
                ON wp_term_relationships.term_taxonomy_id = wp_term_taxonomy.term_taxonomy_id
              WHERE (
          		taxonomy = 'post_tag'
            		OR taxonomy = 'category'          		
            		OR taxonomy = 'myCustomTax'
          		)
              	AND object_id = wp_posts.ID
              	AND wp_terms.name LIKE '%$tag%'
            )
        )";
    endforeach;
    return $where;
}
 
add_filter( 'posts_search', 'advanced_custom_search', 500, 2 );

thank you for sharing!

Thanks!

wow!!!!! one plugin less :)

legit! thanks!!

Works Perfectly!

Nice one, thanks!

Make sure to escape each $tag though as it's currently open to SQL injection! ( Line 43: $tag = esc_sql($wpdb->esc_like($tag)); )

Amazing! Thanks

put this code in functions.php?

wow - inspires me to learn how to code better- amazing piece of coding skills here.

You seriously need to escape your inputs, this isn't safe to use as-is and is vulnerable to SQL injection.

In sharing this vulnerable gist you've opened up a bunch of sites to SQL injection, and that's definitely bad.

incredible!! just added some escape to prevent sql injection!

For those of you stumbling into this script via Google, I reworked it to include XSS and SQL injection attack protection (via PHP's strip_tags() and WP's esc_sql(), respectively. I also improved the conditional notation to use brackets and added in some comments to help you understand how to use / modify it for your own ACF-powered WP site: https://gist.github.com/jserrao/d8b20a6c5c421b9d2a51

AMAZING. Thanks

@charleslouis and @jserrao: Thank you both for this, seriously! I had a plugin conflict with 'searchwp' and using your code instead worked perfectly (I credited you in the theme). All the best!

Is there a way to search the (user) type subfield in a repeater?

Would really love to know how to include Repeater fields in this.

Brilliant. Thank you!

Perfect - thank you so much!!!

If someone is looking for a solution to this problem is to recommend the plugin:
https://wordpress.org/plugins/acf-better-search/

This plugin adds to default WordPress search engine the ability to search by content from selected fields of Advanced Custom Fields plugin.

Everything works automatically, no need to add any additional code.

How to use this script for get_posts()?
// It works for this $wp_query->found_posts; // but I wanna to get smth like this $posts = get_posts( array( 'post_type' => 'cpt', 'numberposts' => - 1, // ... 'order' => 'asc', 's' => get_search_query(), ) );

Amazing, thanks! One thing I'd add is $wpdb->prefix to the query in case of custom database prefixes :)

Great code! Thanks for sharing with me @janchimugica!

Note that the original query WordPress sends includes AND (wp_posts.post_password = '')

...and this query does not. So you'll want to re-add that, unless you want protected posts to be included as well.

issues after upgrading to php7.2; Warning: Parameter 2 to advanced_custom_search() expected to be a reference, value given in wp-includes/class-wp-hook.php

issues after upgrading to php7.2; Warning: Parameter 2 to advanced_custom_search() expected to be a reference, value given in wp-includes/class-wp-hook.php

As far as I can see you can remove the & in front of $wp_query on line 21. It doesnt need to be passed by reference since it isnt actually being modified, we're just extracting the search term. For me that removes the warning and still works fine :)

Amazing. Thank you

Slim version where we don't reset the $where query:

add_filter( 'posts_search', 'search_suggestions_custom_search', 500, 2 );
function custom_search_function( $where, $wp_query ) {
    if ( empty( $where )) {
      return $where;
    }

    // get search expression
    $terms = $wp_query->query_vars['s'];

    // explode search expression to get search terms
    $exploded = explode(' ', $terms);
    if( $exploded === FALSE || count($exploded) === 0 ) {
      $exploded = array( 0 => $terms );
    }

    foreach( $exploded as $tag ) {
        $where .= " OR EXISTS (
          SELECT * FROM wp_terms
            INNER JOIN wp_term_taxonomy ON wp_term_taxonomy.term_id = wp_terms.term_id
            INNER JOIN wp_term_relationships ON wp_term_relationships.term_taxonomy_id = wp_term_taxonomy.term_taxonomy_id
            WHERE taxonomy = 'myCustomTax' AND object_id = wp_posts.ID AND wp_terms.name LIKE '%$tag%'
        )";
    }
    return $where;
}

Nifty piece of code, thank you, and thank you @jserrao for the mods to escape the inputs cleanly, and although it's 6 years old, still works like a charm :)

thank you so much :))

Amazing, thanks! One thing I'd add is $wpdb->prefix to the query in case of custom database prefixes :)

Quite right! Just in case anyone is trying to use this code and discovering it's not working: this will work great for most installs but NOT IF YOU OR YOUR HOST IS USING CUSTOM/PREFIXED TABLE NAMES. So for my customer install running at Flywheel, where the install has prefixed the tables so they look like wp_643cpbrg8g_postmeta rather than wp_postmeta, this will quietly fail.

The solution is pretty simple, and the function is mostly there and already includes the needed variable, $wpdb, which makes visible the table names. If you haven't changed them there's no harm in being compliant. If you have this is absolutely necessary.

So where the above code says

SELECT * FROM wp_comments

it instead needs to be

SELECT * FROM $wpdb->comments