Search Multiple Custom Fields in WordPress

UPDATE: 06-15-2012
See This comment to see a simple way to use WP_Query() to search multiple custom fields without having to deal with raw SQL.


Ever need to search across multiple custom fields in WordPress at once? In a regular WordPress loop it is only possible (afaik) to identify one custom field's meta key and value. This tutortial explains how to create a custom search form and results loop for your needs.

Here is a demo
Here is the code behind the demo

The first step is to recognize what custom fields you want to use. In this example, let's assume that your blog is a movie review site. You have the following custom fields on your posts.

1) Rating (4 out of 5 stars...)
2) Audience (G, PG, etc)
3) Movie Length

Here's what your search form would look like. You can either just place it like this in your HTML, or create is asearchform.php and use the template tag get_search_form();

<form method="get" id="searchform" action="<?php bloginfo('url'); ?>/movies/">
        <p>
                <label>Rating:</label> <input type="text" name="rating" id="rating" value="" />
        </p>
        <p>
                <label>Audience:</label> <input type="text" name="audience" id="audience" value="" />
        </p>
        <p>
                <label>Length:</label> <input type="text" name="length" id="length" value="" />
        </p>
        <input type="submit" id="searchsubmit" value="GO" />
</form>

Now we need to create a function in functions.php that will handle the results of the custom search form above. It takes the query string and breaks it up into its respective peices to handle each component individually. It first checks to see which inputs have been filled out. It then begins to build a custom sql query that joins the wp_posts table with the wp_postmeta table as many times as it needs to. In this case it is 3 as that how many custom fields are being used in the custom search form. This number is completely up to you to determine how many you have per post or how many you want to include in the custom search.

UPDATE: 04-11-2012
Remember to always validate/sanitize your data before putting the vars into the SQL statements. You can use preg_replace, or you can use filter_var().
http://php.net/manual/en/function.preg-replace.php
http://php.net/manual/en/function.filter-var.php
http://www.php.net/manual/en/filter.filters.sanitize.php

<?php
function get_reviews_by_custom_search() {
global $wpdb;
$rating = preg_replace( '/[^0-9]/', '', $_GET['rating'] ); //only allow numbers
$audience = preg_replace( '/^[0-9a-zA-Z-]/', '', $_GET['audience'] ); // only allow letters, numbers and dashes...ie PG-13 and not PG#13
$length = preg_replace( '/[^0-9]/', '', $_GET['length'] ); // only allow numbers        

        if ($rating != '') {
                $rating_sql = " AND wpostmeta.meta_key = 'rating' AND wpostmeta.meta_value = '$rating' ";
        }
        if ($audience != '') {
                $audience_sql = " AND wpostmeta2.meta_key = 'audience' AND wpostmeta2.meta_value = '$audience' ";
        }
        if ($length != '') {
                $length_sql = " AND wpostmeta3.meta_key = 'length' AND wpostmeta3.meta_value = '$length' ";
        }       

        // This is based on a post from wordpress.org forums where the search terms were predefined in the query whereas mine pulls from a custom search form.  http://wordpress.org/support/topic/custom-fields-search#post-909384.
        $querystr = "
                SELECT DISTINCT wposts.*
                FROM $wpdb->posts wposts, $wpdb->postmeta wpostmeta, $wpdb->postmeta wpostmeta2, $wpdb->postmeta wpostmeta3
                // The select creates aliases of the wp_postmeta table to be hooked up with the wp_posts table for each meta_key that is uses
                WHERE wposts.ID = wpostmeta.post_id
                AND wposts.ID = wpostmeta2.post_id
                AND wposts.ID = wpostmeta3.post_id
                // These next lines are built based on the first part of the function.  If there were values in the inputs of the search form.  So we only create extra "WHEREs" if needed
                " . $rating_sql . "
                " . $audience_sql . "
                " . $length_sql . "
                // This orders by most recent posts.  You can pick what you like for this
                ORDER BY wposts.post_date DESC
        ";

        $searched_posts = $wpdb->get_results($querystr);

        return $searched_posts;
        // By returning the seaerched posts, we can create a custom foreach loop on search.php

}
?>

So now on search.php you can use the following foreach loop to generate your results

<?php

// If you still want to have a regular search form in your header or where ever on your site, you can put this into your normal search.php and check the following at the top

if ( ($_GET['rating'] == '') && ($_GET['audience'] == '') && ($_GET['length'] == '') ) {

        // do your normal search loop here

} else {

        // this means that someone put something into your custom search form.  We do not have the typical "?s=" in our new search form, so this is a good enough check to get to the results of our new search form

        $searched_posts = get_reviews_by_custom_search();

        foreach ($searched_posts as $searched_post) {

                echo "<h2>" . $searched_post->post_title . "</h2>";

                echo $searched_post->post_content;

                $tags = wp_get_post_tags($searched_post->ID);
                        echo "Tags";
                        foreach ($tags as $tag) {
                                echo $tag . ", ";
                        }

                echo "<a href="" . $searched_post->post_name . "">Read More</a>";

}
Post a comment
  1. Milan Santos

    Is there a way to display everything if the form was submitted blank? Also How can i add a scale between 2 values in it? lets say in your sample, will add year. then you want to display the movies from year 2010 to 2013 using your code, not the wp_query Thanks buddy

    • Matthew Price

      you would have to check the results and if there are non, then you could do a get_posts(); with the default parameters that will get you all of the results you expect.

      • Milan Santos

        Nice it wokred! Thanks Buddy, Your Awsome!

        • Matthew Price

          Glad I could help!

    • Matthew Price

      so you would make one of your sql queries look like this $year_sql = " AND wpostmeta3.meta_key = 'year' AND wpostmeta3.meta_value BETWEEN '$start_year' AND '$end_year' "; remember to replace the "3" of the wpostmeta with whatever number join you are doing

  2. Abiola Salako

    form: `&lt;form method=&quot;get&quot; id=&quot;searchform&quot; action=&quot; I want to buy: From: ` single-resturants.php ` 'resturant', 'posts_per_page' =&gt; 10 ); ?&gt; have_posts() ) : $loop-&gt;the_post(); ?&gt; &lt;div id=&quot;post-" &gt; &lt;a href=&quot;" title="" rel="bookmark"&gt; </a> &lt;a href=&quot;"&gt;</a> <!-- #post-## --> `

  3. Abiola Salako

    Hi math, thanks for this great tips, after several google search, I am so happy i finally got here. please can you help me out. I just created a custom post type called resturants and i have the following as my custom form in a page template: form method="get" id="searchform" action=" I want to buy: From: wantbuy text field will search for post type title from resturant post type fromstore text field will search for post type taxonomy (categories) from resturant post type I also have this in my single-resturants.php 'resturant', 'posts_per_page' =&gt; 10 ); ?&gt; have_posts() ) : $loop-&gt;the_post(); ?&gt; &lt;div id=&quot;post-" &gt; &lt;a href=&quot;" title="" rel="bookmark"&gt; </a> &lt;a href=&quot;"&gt;</a> <!-- #post-## --> but all this are not searching for anything. After reading your post for several day, i decided to ask you to bail me out. I dont mind to donate to your great work. Regards






Real Time Web Analytics ^