Search Multiple Custom Fields in WordPress
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>";
}
84 Responses to “Search Multiple Custom Fields in WordPress”
Leave a Reply
Matthew,
Thanks for this post. I am a newbie to WordPress and PHP and was looking to implement a custom search for a new website I am creating which would allow me to search posts of a specific category for multiple tags (i.e. Industry, City, State/Province).
Your code looks to put me on the right path. Any hints on how I use the built-in Tag fields instead of your example of custom fields?
Thanks again.
Hi Kevin
So in order to search tags it is a bit more complicated. I actually have a query that gets you the ability to search on a specific category and tags within that category. I can get that to you within the next week
Matt
I would like to see your solution for tags within a category as well. Is that possible?
Thanks – Kathleen
Hi Kathleen
To search more than 2 custom fields and tags is a bit more complicated. I have done something like this in the past. i just have not made any sort of generic tutorial version yet. I can work something up in the next week or so.
If you have less than or equal to 2 custom fields, a taxonomy, and some tags, you can use WP_Query(); and use meta_query, tax_query, and tag__in together to accomplish this. The following below would retrieve posts that are PG, Between 100 and 150 minutes, are in the category with a slug of “adventure”, and has been tagged with the tags with the IDs of 5 and 6.
so…
<?php
$args = array(
'meta_query' => array(
'relation' => 'AND',
array(
'key' => 'pg',
'value' => 'audience',
'compare' => '='
),
array(
'key' => 'length',
'value' => array( 100, 150 ),
'type' => 'numeric',
'compare' => 'BETWEEN'
)
),
'tax_query' => array(
array(
'taxonomy' => 'category',
'field' => 'slug',
'terms' => 'adventure'
)
),
'tag__in' => array( 5, 6 )
);
$movies = new WP_Query( $args );
while ( $movies->have_posts() ) : $movies->the_post();
// do your loop
endwhile;
?>
here are two reference links:
http://codex.wordpress.org/Class_Reference/WP_Query#Custom_Field_Parameters
http://codex.wordpress.org/Class_Reference/WP_Query#Taxonomy_Parameters
Matt
Hi Matt,
I was wondering if this would work for search custom fields only within a defined custom post type?
Using your movie example, I’d have the reviews set up as custom posts. And the customs fields tied exclusively to these posts only (I use the “More Fields” plugin to set these up – nice easy to use GUI).
Of the three search fields, two would be made up of checkboxs to allow for multiple selections and the third would be a dropdown. This dropdown would need to search for fields greater then (<) the chosen option (ie < PG).
Any help would be appreciated.
Hi Tony
So this should definitely work with a custom post type.
you can change the sql to read as follows. this will refine the posts to the post type and then do all the postmeta queries
Change this part
WHERE wposts.ID = wpostmeta.post_id
AND wposts.ID = wpostmeta2.post_id
AND wposts.ID = wpostmeta3.post_id
TO…
WHERE wposts.post_type = ‘YOUR_CUSTOM_POST_TYPE_HERE’
AND wposts.ID = wpostmeta.post_id
AND wposts.ID = wpostmeta2.post_id
AND wposts.ID = wpostmeta3.post_id
If you need anything else, don’t hesitate to ask
Matt
This is exactly the functionality I’ve been looking for. I’m surprised there’s no plugin for this.
Try as I might, though, I can’t get it to work.
1) Created a form with the custom fields I want to search
2) Created the function in functions.php (changing the custom field names to my names
3) Created a custom page template with the results code on it (to avoid mucking with the existing search results loop)
The search-results page is blank, no matter what I try.
Any suggestions on what I should try?
I will try this from scratch again and see if there is some syntax error in my code here
Matt
Disregard. I found my error.
A $wpdb->show_errors(); made my realize that I was trying to include $trades where I wanted $trades_sql
All is well. Now I just have to try to get the search results to match the rest of my search results (which are highly customized) and I’m good to go.
Thanks for the code.
I have updated the code a bit as i had some remnants from the property management code that this originated from.
here is a live demo too that i created: http://dev.matthewaprice.com/
you can see searchable options as post meta displayed in the posts below
Thanks Matt.
I’m still playing with it. I’ve got it mostly working now, but some of the query strings don’t seem to be working correctly.
I’ll take a break and revisit it when I have more time.
One last question and I should be all set:
Is there an (easy) way to add category filtering to the queries?
field1 AND field2 and field3 AND category ? That might make my search a lot more targeted.
Hi Mike
I will take a look that this over the weekend and see.
Matt
And feel free to send over some sample code. I will email you with my personal email address right now.
Matt
Hi Mike,
Very nice bit of code. I am working on simplifying my search engine on a property site. I have been able to get my code working, but I cannot seem to address the issues with pagination.
I am limiting the “posts_per_page” to 10, so if there are more that 10 results, the initial query will produce a correct number of results and pages. However, as soon as I click on “Next Page” or another page of results, it resets the $wp_query->query to the default value. (prior to setting $wp_query-query($list_args) ).
Any suggestions?
Thanks,
E.
Hi Eric
So i would write a simple pagination function that takes the page number and creates an offset. you can use “offset” as an argument for query_posts(); so if you are using 10 posts per page then, page 1 would have an offset of “0″ and page 2 would have an offset of “10″.
so…here is a snippet adapted from a pagination class that i have written.
function create_offset() {
if ($_GET['pagi'] == ’1′) || (!isset($_GET['pagi'])) ) {
$offset = ’0′;
} else {
$offset = 10 * ($_GET['pagi'] – 1) );
}
return $offset;
}
you can replace the “$_GET['pagi']” with whatever GET you have set. Then the returned value could be used like so…
$args = array( ” posts_per_page” => 10, “offset” => create_offset() );
query_posts($args);
this should get you in the right direction. lemme know if you need some more help
matt
Thanks Matt, very helpful. The most critical issue I am having is saving the original query statement as I will need to pass that along as well as the newly generate offset parameters.
Any thoughts on how that might be done?
I am not clear as to the reasons why WordPress is destroying it when I click on the pagination.
My first choice is to create a query string as I build the $list_args array. But I am not clear on how a meta_query array would get passed in to the query string along with the rest of the information.
I have tried setting my $list_args to global but it does not seem to be working.
First of all thanks for share your code, i’ve just used it for a project, but i need to filter even on category, so i’ve change the query in:
$querystr = ”
SELECT wposts.*
FROM $wpdb->posts wposts
LEFT JOIN $wpdb->postmeta wpostmeta ON wposts.ID = wpostmeta.post_id
LEFT JOIN $wpdb->postmeta wpostmeta2 ON wposts.ID = wpostmeta2.post_id
LEFT JOIN $wpdb->postmeta wpostmeta3 ON wposts.ID = wpostmeta3.post_id
LEFT JOIN $wpdb->postmeta wpostmeta4 ON wposts.ID = wpostmeta4.post_id
LEFT JOIN $wpdb->postmeta wpostmeta5 ON wposts.ID = wpostmeta5.post_id
LEFT JOIN $wpdb->postmeta wpostmeta6 ON wposts.ID = wpostmeta6.post_id
LEFT JOIN $wpdb->postmeta wpostmeta7 ON wposts.ID = wpostmeta7.post_id
LEFT JOIN $wpdb->postmeta wpostmeta8 ON wposts.ID = wpostmeta8.post_id
LEFT JOIN $wpdb->term_relationships ON (wposts.ID = $wpdb->term_relationships.object_id)
LEFT JOIN $wpdb->term_taxonomy ON ($wpdb->term_relationships.term_taxonomy_id = $wpdb->term_taxonomy.term_taxonomy_id)
WHERE wposts.ID = wpostmeta.post_id
AND wposts.post_status = ‘publish’
AND wposts.post_type = ‘post’
AND $wpdb->term_taxonomy.taxonomy = ‘category’
AND $wpdb->term_taxonomy.term_id = ’7′
” . $rating_sql . ”
” . $audience_sql . ”
” . $length_sql . ”
ORDER BY wposts.post_date DESC
“;
Hope it can help someone out!
I keep getting a 404 page. I am trying to search post in a the parent category that has 2 custom fields. Any tips?
Thanks
Thanks a lot for the code, it’s looking good and I’ve had partial results though I can’t figure out how you’re posting your form to /movies/ and getting whatever page is showing up in /movies/ to display the search results.
I can hack the URL (adding ?s= before the terms) to see my results successfully.
I was hoping to see your working example, but that seems to be offline.
Thanks
Hi
So i had mistakenly taken down the dev site with the demo. I apologize. It is back up and you should be able to view it.
I have a page template that takes the variables and feeds them into the custom query. You can add query string variables to any page in WordPress and if the template knows what to do with them (ie you place some custom code on it), it will do what it is supposed to
Matt
Hey Matt,
I too am having some trouble with getting my search results. As far as action=”" goes, I am simply pointing it back to my original post, just as you have done.
You mentioned:
“I have a page template that takes the variables and feeds them into the custom query. You can add query string variables to any page in WordPress and if the template knows what to do with them (ie you place some custom code on it), it will do what it is supposed to”
Do you still have the code for this?
Hi.
Thanks for sharing your code.
I don’t know if I am the only one getting this problem or if I am missing something obvious, but when I do a search on a custom field, the search.php file is not being called at all by WordPress. Hence I can’t manipulate the results.
It seems that the WordPress doesn’t recognize it as a search and just displays the home page or same page.
How did you make WordPress call the search.php file on your searches?
Thanks
Vayu
Hi
You are not missing anything…my code makes some assumptions that i did not explain
The search form does not have to have anything to do with “search.php”
Because it checks to see if the custom query variables are empty or not, it can exist anywhere. So you can have a loop.php run if you are in the custom query and have a different loop if you are not.
so this section is important…
< ?php if ( ($_GET['rating'] == '') && ($_GET['audience'] == '') && ($_GET['length'] == '') ) { ?>
<!– do your regular loop ->
< ?php } else { ?>
<!– do your custom search results loop and include the –>
< ?php } ?>
Because we are doing this check and it has nothing to do with the “s” query string variable, it can exist anywhere
Make sense?
Thanks for reading!
Matt
Hi.
Thanks for sharing your code. It’s really helpful.
Could you please share as well the whole code (or better yet files, if that is not too much to ask) that you use to show the loop on your demo site and/or explain how did you do that? I would be really really grateful, because I am a php beginner and am not able still, after many days of trying, to make it work on my own.
Hi Darko
I have updated the post with a zip file link below the demo link. It has the index.php and functions.php file that drives the demo site. it is all that i used to get it going
Hope this helps, and if you have any further questions, please let me know!
Matt
ok, so ive added this code and it works, but as soon as i added a 4th meta field, the database comes back saying its empty.. its lying to me but i dont know why?
Hi Aaron
Could you paste in your code sample? I would be happy to take a look at it and see if i see something
Matt
Hello,
Thanks for this great and useful piece of code,
My custom search works fine only when I put the search form and the third part of code of your example that begins with
if ( ($_GET['rating'] == ”) && ($_GET['audience'] == ”) && ($_GET['length'] == ”) ) {
)
….
Together,
But when I put the third part of code in search.php, my search not working, the data not appear and not redirect to search.php … How I can solve this?
Any help is very appreciated
Thanks in advance
Hi Cristian
This code is independent of the default WordPress search. it does not rely on “?s=”
so… all this does is filter by custom field only. if you want it integrated with your search.php it needs a bit more tweaking. You would also need to include the “?s=” in your loop and section that you have highlighted above.
Matt
Hi Matt,
I managed to get the search working fine without the ?s= query. But one problem i realised is when there is a few search function for different posts.
e.g Books posts can search by book title
but if it is a real estate when there is a need to query like bedrooms etc, i duplicated the function and name it to something else, it does not work.
In fact the page freezes after hitting the search button.
Is there any methods to solve this?
Hi there,
is it possible to perform the same type of search for pages rather than posts?
My properties are listed on pages (custom templates). What changes to the code should be made to make it work?
Thanks in advance,
Simon
Hi Simon
Because pages are post types you do not have to do anything to alter this for pages : >
There is a table join for posts and postmeta that joins on the post’s ID, so it just works
Matt
Hi Matthew,
I really need some help here, What if I want to make the movie length form become two forms:
So people can search for movie that has length, for example 30 to 40 minute;
movie length: from [_] to [_]
Could you please tell me how to do it?
I also need check box functions, please help me out.
many thanks
Arya
Hi Arya
So you can change the MySQL statement for length to look like this instead. Note the addition of two extra pieces that are added with the greater than and less than.
$length_sql = ” AND wpostmeta3.meta_key = ‘length’ AND wpostmeta3.meta_value > ’30′ AND wppostmenta3.meta_value < ’40′ “;
You can make the meta_values dynamic by offering the option of a high and low value input field and use those as the values. So you could do something like this:
<label for=”length_min”>Min Length</label>
<input type=”text” name=”length_min” id=”length_min”>
<label for=”length_max”>Max Length</label>
<input type=”text” name=”length_max” id=”length_max”>
Then in the php you can have access to $_GET['length_min'] and $_GET['length_max']
You can feed those into the sql where the 30 and 40 are hardcoded above. I guess you could also have some sort of javascript slider that will feed the values too.
Hope this helps!
I am currently working on a complicated checkbox solution, but you can check out the basic demo fo what i have here: http://dev.matthewaprice.com/?page_id=16
Thanks for the comment
Matt
Hi Matthew,
That really help me. Thank you very much. problem solved.
I saw the demo of your checkbox, yes that is what I need,
If you don’t mind, could you please send me the source code?
Many Thanks
Arya
Hi this Search-checkbox code is what I need!!!
Where can I find the plugin or code to insert this into my website??
Really need your help on this one!!
Waiting you reply.
William
Hi
It is not really a plugin, but i can look into making it one where you can pick from your custom fields available to make the checkboxes. right now it is just a theme mod. it uses a page template, an included php file that returns the results and some javascript that loads the php file when the boxes are checked.
Matt
That’s cool.
Is it possible to download it somewhere or mail it to me?
It’s for a presentation at work so that would be nice If I can integrate it.
Hi
I can probably get you something tonight
Matt
Hi Everyone
Here are the files needed to run the ajax checkboxes. It assumes the 2011 theme, but can be adapted to any theme. it is commented with instructions/explanations
http://matthewaprice.com/assets/ajax-checkboxes.zip
If you need anything else, please let me know
Matt
Thank you so much !!
Big kisses all around !!! And have a great 2012 Matthew!
Hey Matt,
Thx again for the code.
I’m trying to make it a whole section with countries and states (=categories) that can be selected using checkboxes. Is this also possible with this code?
I uploaded the functions and my whole site went blank. I prob. did something wrong…..
any ideas?
Hi William
It may not be completely drop-in for your setup, but the code is the same as it is on my dev site. it can definitely be adapted to handle multiple custom fields. if you want to email me more information, please feel free. matthew@matthewaprice.com
Matt
Hi, don’t worry about the checkbox, I think I got the answer.
Thank you very much.
Good Job
I’m sorry
It’s me again, what if I got 8 checkbox option.
There will be so many ‘if’ is there any way to avoid using too many ‘if’ ?
Please reply
Thanks
Hi Arya,
I have emailed you a bit more info. I am basically using a jQuery method that pushes the checked values on a javascript array. I then pass that comma separated list to a php file loaded via ajax. i then use the “IN” method in the MySQL statement to just get the posts that are in the array. This way you do not need “if” statements
Matt
I have created a custom search form and a custom search page, but the thumbnail and the excerpt doesn’t appeared. anyone can give sugestion?
this code I used :
<?php
$searched_posts = get_reviews_by_custom_search();
foreach ($searched_posts as $searched_post) {
echo "”;
echo “”;
if ( has_post_thumbnail() ) {
the_post_thumbnail();
} else {
echo ”;
}
echo “ID) . “\”>” . $searched_post->post_title . ““;
echo $the_post_thumbnail;
echo “” . $searched_post->post_excerpt;
echo “Bedroom: ” . get_post_meta($searched_post->ID,’bedroom’,true);
//echo “Price – ” . get_post_meta($searched_post->ID,’price’,true);
echo “, Location: ” . get_post_meta($searched_post->ID,’location’,true) . “”;
echo “ID) . “\”>View Detail“;
echo “ID) . “\”>Make an Enquery“;
echo “”;
}?>
Hi,
So the code in this post does not allow for template tags like the_post_thumbnail()
You need to use “get_the_post_thumbnail( $post_ID )”
$thumbnail = get_the_post_thumbnail( $searched_post->ID );
echo $thumbnail;
You also have the post thumbnail as a variable in the code and that is not correct.
The excerpt looks correct. There are some code pieces that got omitted when you submitted the comment, so i cannot say for sure on the excerpt, but it looks right.
On a side note, you can feel free to rename the function “get_reviews_by_custom_search()” to be more applicable to your needs. for example, where the function is defined and where it is called. You could use (it looks like this is a property based search…) get_properties_by_custom_search()
I hope this helps
Matt
Matt
Thank You Matt
Now the thumbnail is a apeared, but the excerpt is not, any idea for that?
this code I used:
<?php
$searched_posts = get_reviews_by_custom_search();
foreach ($searched_posts as $searched_post) {
echo "”;
echo “”;
$thumbnail = get_the_post_thumbnail( $searched_post->ID );
echo $thumbnail;
echo “ID) . “\”>” . $searched_post->post_title . ““;
echo “” . $searched_post->post_excerpt;
echo “Bedroom: ” . get_post_meta($searched_post->ID,’bedroom’,true);
//echo “Price – ” . get_post_meta($searched_post->ID,’price’,true);
echo “, Location: ” . get_post_meta($searched_post->ID,’location’,true) . “”;
echo “ID) . “\”>View Detail“;
echo “ID) . “\”>Make an Enquery“;
echo “”;
}?>
Thanks before!!
Hey Matt,
Great piece of code, however I seem to be having some problem with it.
Here is the code in use AutoMarket
It works fine when I fill information in all the fields but when I search one field individually it doesn’t work at all
I have altered the code slightly so it half works.
////////////////Search form////////////
Car Make:
Engine Size:
Transmission:
<?php
$searched_posts = get_reviews_by_custom_search();
foreach ($searched_posts as $searched_post) {
echo "ID) . “\”>” . $searched_post->post_title . ““;
echo “Car Make – ” . get_post_meta($searched_post->ID,’Car Make’,true) . “”;
echo “Engine Size – ” . get_post_meta($searched_post->ID,’Engine Size’,true) . “”;
echo “Transmission – ” . get_post_meta($searched_post->ID,’Transmission’,true) . “”;
echo “ID) . “\”>Read More“;
}
}
?>
////////////////////////////////////////////////
/////////////Functions.php//////////////
function get_reviews_by_custom_search() {
global $wpdb;
$carMake = $_GET['Car Make'];
$EngineSize = $_GET['Engine Size'];
$Transmission = $_GET['Transmission'];
if ($carMake != ”) {
$carMake_sql = ” AND wpostmeta.meta_key = ‘Car Make’ AND wpostmeta.meta_value = ‘$carMake’ “;
}
if ($EngineSize != ”) {
$EngineSize_sql = ” AND wpostmeta2.meta_key = ‘Engine Size’ AND wpostmeta2.meta_value = ‘$EngineSize’ “;
}
if ($Transmission != ”) {
$Transmission_sql = ” AND wpostmeta3.meta_key = ‘Transmission’ AND wpostmeta3.meta_value = ‘$Transmission’ “;
}
$querystr = ”
SELECT wposts.*
FROM $wpdb->posts wposts, $wpdb->postmeta wpostmeta, $wpdb->postmeta wpostmeta2, $wpdb->postmeta wpostmeta3
WHERE wposts.ID = wpostmeta.post_id
AND wposts.ID = wpostmeta2.post_id
AND wposts.ID = wpostmeta3.post_id
” . $carMake_sql . ”
” . $EngineSize_sql . ”
” . $Transmission_sql . ”
ORDER BY wposts.post_date DESC
“;
$searched_posts = $wpdb->get_results($querystr);
return $searched_posts;
}//////////////////////////////////////////////////
Thanks in advance Matt
Bookmarked, i am a practice web developer i want to develop and site similar yellowpages.com this might help a lot in performing queries. What do you think?
Hello!
Thanks Matt!
My solution (Matt!) for multiple custom field search.
function.php
function wpti_get_search_by_custom_fields() {
global $wpdb;
$wpti_manufacturer = $_GET['manufacturer'];
$wpti_product_type = $_GET['product_type'];
$wpti_product_kind = $_GET['product_kind'];
$wpti_product_year = $_GET['product_year'];
$wpti_product_hours = $_GET['product_hours'];
$wpti_product_stock = $_GET['product_in_stock'];
if ($wpti_manufacturer != ”) {
$wpti_manufacturer_sql = ” AND wpostmeta.meta_key = ‘manufacturer’ AND wpostmeta.meta_value = ‘$wpti_manufacturer’ “;
}
if ($wpti_product_type != ”) {
$wpti_product_type_sql = ” AND wpostmeta2.meta_key = ‘product_type’ AND wpostmeta2.meta_value = ‘$wpti_product_type’ “;
}
if ($wpti_product_kind != ”) {
$wpti_product_kind_sql = ” AND wpostmeta3.meta_key = ‘product_kind’ AND wpostmeta3.meta_value = ‘$wpti_product_kind’ “;
}
if ($wpti_product_year != ”) {
$wpti_product_year_sql = ” AND wpostmeta4.meta_key = ‘product_year’ AND wpostmeta4.meta_value = ‘$wpti_product_year’ “;
}
if ($wpti_product_hours != ”) {
$wpti_product_hours_sql = ” AND wpostmeta5.meta_key = ‘product_hours’ AND wpostmeta5.meta_value = ‘$wpti_product_hours’ “;
}
if ($wpti_product_stock != ”) {
$wpti_product_stock_sql = ” AND wpostmeta6.meta_key = ‘product_in_stock’ AND wpostmeta6.meta_value = ‘$wpti_product_stock’ “;
}
$querystr = ”
SELECT DISTINCT wposts.*
FROM $wpdb->posts wposts, $wpdb->postmeta wpostmeta, $wpdb->postmeta wpostmeta2, $wpdb->postmeta wpostmeta3, $wpdb->postmeta wpostmeta4, $wpdb->postmeta wpostmeta5, $wpdb->postmeta wpostmeta6
WHERE wposts.post_type = ‘page’
AND wposts.ID = wpostmeta.post_id
AND wposts.ID = wpostmeta2.post_id
AND wposts.ID = wpostmeta3.post_id
AND wposts.ID = wpostmeta4.post_id
AND wposts.ID = wpostmeta5.post_id
AND wposts.ID = wpostmeta6.post_id
” . $wpti_manufacturer_sql . ”
” . $wpti_product_type_sql . ”
” . $wpti_product_kind_sql . ”
” . $wpti_product_year_sql . ”
” . $wpti_product_hours_sql . ”
” . $wpti_product_stock_sql . ”
ORDER BY wposts.post_date DESC
“;
$searched_posts = $wpdb->get_results($querystr);
return $searched_posts;
}
search.php and searchform.php typical, no problem wih it.
it so slowly on both local and online site!
There is fast open page search.php after submitting form, but only with header and sidebar. After 5-10 minutes display searched results.
Does anybody know how to speed up search?
Hi
With this many tables being joined to fetch the results there is a tradeoff for speed.
So you can try using the WP_Query class like so…
This allows you to grab your $_POST vars and feed them into the arrays below. You can also pick the “relation” as “and” or “OR”…and you can optionally set the “compare” values to be any normal type of SQL Operator…ie “IN”, “NOT IN”, “!=”, “=”, “>=”, etc… if the meta_value is numeric, you can also set ‘type’ => ‘numeric’ and it will use that for the “compare” value. I have found this to be faster in real world tests
$args = array(
'post_type' => 'post',
'meta_query' => array(
'relation' => 'AND',
array(
'key' => 'rating',
'value' => $_POST['rating']
),
array(
'key' => 'audience',
'value' => $_POST['audience']
),
array(
'key' => 'length',
'value' => $_POST['length'],
'compare' => '>=',
'type' => 'numeric'
),
array(
'key' => 'genre',
'value' => $_POST['genre']
)
)
);
$filtered_posts = new WP_Query( $args );
while ( $filtered->have_posts() ) : $filtered->the_post();
// normal template tags here...
endwhile;
The basis for the WP_Query() meta_query comes from here: http://codex.wordpress.org/Class_Reference/WP_Query#Custom_Field_Parameters
here is the resulting SQL that the class generates:
SELECT SQL_CALC_FOUND_ROWS wp_posts.* FROM wp_posts INNER JOIN wp_postmeta ON (wp_posts.ID = wp_postmeta.post_id)
INNER JOIN wp_postmeta AS mt1 ON (wp_posts.ID = mt1.post_id)
INNER JOIN wp_postmeta AS mt2 ON (wp_posts.ID = mt2.post_id)
INNER JOIN wp_postmeta AS mt3 ON (wp_posts.ID = mt3.post_id) WHERE 1=1 AND wp_posts.post_type = 'post' AND (wp_posts.post_status = 'publish') AND ( (wp_postmeta.meta_key = 'rating' AND CAST(wp_postmeta.meta_value AS CHAR) = 'PG-13')
AND (mt1.meta_key = 'audience' AND CAST(mt1.meta_value AS CHAR) = 'PG-13')
AND (mt2.meta_key = 'length' AND CAST(mt2.meta_value AS CHAR) = '120')
AND (mt3.meta_key = 'genre' AND CAST(mt3.meta_value AS CHAR) = 'action') ) GROUP BY wp_posts.ID ORDER BY wp_posts.post_date DESC LIMIT 0, 10
Hope this helps!
Just wanted to say thanks for putting this out there. I came across it today while pounding my head on my desk trying to figure out how to search across a number of custom fields.
Thank so much!
This is just what I have been looking for, but I’m coming across a problem.
I am looking to use this here: http://dw-testing.com/wordpress/certificate-of-analysis-3/
The custom field would be added to posts which then would like to this “Certificate” for the customers to download.
I am using the genesis framework and I can’t figure out how to add the function to the search so it doesn’t interfere with the search bar on the header.
Any advice?
Hi There
So i have not used the genesis framework, my only experience with frameworks on top of WordPress is with Thesis and I only used it for a short while.
In my example, i just placed a form on a page and then had the form post to itself. I then just used conditionals to see if the search vars were in the query string. Based on that, I then showed the results of the search on that same page. Fyi, you cannot use WordPress’ “s” in this otherwise, you will get some odd behavior.
So sort of like
<?php
if ( isset( $_GET['movie_genre'] ) ) {
// display results with custom loop (either with WP_Query or query_posts(). they both have meta queries)
} else {
// display the form or other content
}
?>
Matt
Hey there,
Your code is working perfectly for the project I’m working on.. Thank you so much!
One thing I’m trying to figure out is how to display all of the results before a search has taken place. Looks like that’s how you have it set up on your demo page. All of the possible results are displayed before using the form.
I’m going to try to figure this out on my own (I’m not very good at php.. yet!) but if you wanted to tell me how to do this, I would much appreciate it.
Thanks!
How do you set up something where “nothing found” messages appears if there is no records in the db?
Figured it out using if ($array == NULL) then do this statement
thanks for this scrip…saved a lot of time!
Cool!
I was just about to write as much for you. : >
Glad I could help
Matt
Hey I have one more questions. I am using Types plugin to create custom meta boxes for my custom post type called “Test Results”.
I have custom fields for Month, Day, Year, and 2 radio buttons.
Then I use your script to look through these custom meta boxes to find results. That works fine, however onces I get the results and wrap an a tag around the custom title using “the_permalink(); “the page just refreshes into itself, it doesn’t actually go to the single post page.
You have any ideas how I can set it up so the custom search that I did actually goes to the post itself?
I appreciate the help, thank you!
Vadim
aa. I’m sorry I keep bothering you and keep figuring it out. Actually I should have just reviewed your script above before asking, you have it right there
Thanks again, all good!
Glad you figured it out : >
Can someone write a if null statement for me? I’m still a bit new when it comes to writing php scripts! Thanks so much, guys!
Hey! I used & modified your code to search my WP but now I’m having trouble displaying the results on search.php :/ Seems for whatever reason the search.php template only gets triggered when $_GET['s'] is in set :/
OK, I figured it out. I simply named all the fields s[something] and then used $_GET['s'] as an array in PHP.
Hi Matt,
I’ve tried a bunch of WordPress plugins and none of them do the job like this code of yours! The only thing missing here is ability to search between FROM and TO values from select fields. For example I have this code:
…
Width from
50
60
80
iki
60
80
90
…
If a person chooses 50 in one field and 90 in the other one, I need to show the results in that range only. I must say, that I totally suck at PHP when it comes to writing my own code, so basically I’m asking for your help to show how it’s done.
Thanks once again!
Hi
So some of the code got removed from your comment, but i think i understand what you mean
so you could replace the following
AND wpostmeta.meta_key = ‘rating’ AND wpostmeta.meta_value = ‘$rating’ “;
with:
AND wpostmeta.meta_key = ‘rating’ AND wpostmeta.meta_value BETWEEN ‘$select_one’ AND ‘$select_two’
where the select vars are:
$select_one = $_GET['select_opt_1'];
$select_two = $_GET['select_opt_2'];
Hi Matt,
Thanks for a quick reply. Could try this piece of code earlier, but just did it a moment ago and it’s not showing me any results. Have you tried it out?
My bad, messed up the code in the beginning. Everything seems to be working perfectly!
Cheers!
Hi there,
I am trying to use your code in slightly different way.
Lets say I have posts with
custom-field1
custom-field2
custom-field3
custom-field4
And have a wordpress deafult search bar where I want users to be able to input any keyword and it will be searched in the custom fields as well.
[ search for custom-field1, custom-field2,custom-field3,custom-field4 ]
Hope you wont mind letting me know what would be the steps to achieve what I am trying. I am very new to wordpress.
Thanks
Hi Matt, thanks for the great tutorial. Could you help how to implement if frontend search are dropdown options like 10 for one meta field while data is stored numbers. Thanks.
Hi
The tutorial works for whatever kind of input field that you want. so if you had a select field like this…
then…
your check like this…
// only numeric value…
if ($rating != ”) {
$rating_sql = ” AND wpostmeta.meta_key = ‘rating’ AND wpostmeta.meta_value = $rating “;
}
will just go ahead and look for the result of the dropdown menu on search
make sense?
of course with the sql method it would be nice to also use the $wpdb->prepare method too, but the regex at the top of the main example does some checking on input types
—–
if you were to use the WP_Query method it would look like this…
‘meta_query’ => array(
array(
‘key’ => ‘length’,
‘value’ => $rating,
‘type’ => ‘numeric’
‘compare’ => ‘=’ //here you can do “=,!=,LIKE,BETWEEN(between requires an array for the ranges for the value like array( 0,100 ).
)
);
Hi Matthew,
I a really new at WordPress.. and I am dev this:
http://migre.me/8VMoM with some of your code
Thks a lot for the tip.. very helpful. But I have some questions if it`s possible to you help-me.
1 – Can I do something to work with page-navi plugin?
2 – How can I get the number of total results?
Hi
If you were to use the WP_Query version of this tutorial, you could use the ‘paged’ => $paged argument for the class. This is in the comment from November 27, 2011. after “tag__in” => array(5,6), you could put the paged bit.
so…
‘tag__in’ => array( 5,6 ),
‘paged’ => $paged
the $paged variable holds the page number that you are on. it will use the normal /post/page/2 format and will grab the posts from the correct page.
Matt
Hey man, thks a lot again. Now the final question..
The search filter return the posts that are in trash of wp-admin.
What kind of filter I need to put in the query for this? See, i`m not a coder my job is the front end until now..
Hi
You would want to put “post_status” => “publish” into your query args
Matt
Hi Matt;
Brilliant code and Perfect for the WordPress site I am working on.
I am building a realestate site and was wondering is it possible to somehow set the form so it runs with 5x dropdown boxes and 1x text field? i.e.
Type: Dropdown
Price: Dropdown
Beds: Dropdown
Baths: Dropdown
Cars: Dropdown
PostCode: Text Box
And ability to search either 1, multiple or all fields.
Would be brilliant if this was a plugin…;p
You are brilliant at your coding, and all the responses i have seen are great.
Well done!
Hi
So there are a couple of items to take into consideration here. The first being that i have found that the query gets rather slow after 4-5 meta fields being searched. I have tried writing the queries myself and also to use WP_Query.
You can see this comment to see how to have a bunch of fields searching at once by using WP_Query. it is definitely the simplest as it does not require you to write the table joins and aliases (which is what WP_Query does for you).
So you can replace your field names for what you are looking for. You can also for price have two drop downs, or just do a switch if your prices are “>= $200 and <= $300″
so your meta query for this would look like…
switch ( $_POST['price'] ) {
case ‘option_1′ : $low_point = 200; $high_point = 300; break;
case ‘option_2′ : $low_point = 400; $high_point = 500; break;
}
array(
‘key’ => ‘price’,
‘value’ => array( $low_point, $high_point ),
‘compare’ => ‘BETWEEN’,
‘type’ => ‘numeric’
)
make sense? this way you can pass a unique value in your
and have it coded out to the processor
<select name=”price”>
<option value=”option_1″>Between $200 & $300</option>
<option value=”option_2″>Between $400 & $500</option>
</select>
Matt
I have built a website with a seach box, I am using a database in the background, how do i link the seach box to 6 fields in a table, so that when more than 1 thing is searched at a time, it will refine the database information, with an outcome which shows only things that have been entered into the search box
Hi
So you can test for whether or not the post variables are set and then build the query based on whichever variables are set
WordPress does something where they start every “WHERE” clause with 1=1 so that they can then have an undetermined amount of “AND” clauses that will follow.
so if a post variable “isset()”, you build the query statement something like this…
$query = “SELECT columns FROM table_name WHERE 1=1 “;
if ( isset( $some_post_variable ) ) {
$query .= “AND column = ‘$some_post_variable’ “;
if ( isset( $some_other_post_variable ) ) {
$query .= “AND other_column = ‘$some_other_post_variable’ “;
$results = mysql_query( $query );
while ( $row = mysql_fetch_array( $results ) ) {
// loop through and do what you need to with the results
}
Hi Matt,
Thanks for the code, got it working pretty easily. Im posting to a new page template searchresults.php with _POST as the form method.
Is it ok to have undefined variables in the query string. Is there a way to avoid the following?
if ($suburb != ”) {
$suburb_sql = ” AND wpostmeta.meta_key = ‘suburb_value’ AND wpostmeta.meta_value = ‘$suburb’”;
if the above validation results as true, the following part of the query string will be undefined
” . $suburb_sql . ”
thanks in advance.
Hi
You could do the following….
if ($suburb != ”) {
$suburb_sql = ” AND wpostmeta.meta_key = ‘suburb_value’ AND wpostmeta.meta_value = ‘$suburb’”;
} else {
$suburb_sql = ”;
}
this would result in the $suburb_sql variable holding nothing
does this answer your question?
Matt