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>";

}