How to Query Posts and Postmeta by keywords (for custom search)

  • Resolved ralphonz

    (@ralphonz)


    12 years, 11 months ago

    Hi Everyone,

    I’ve been tearing my hair out trying to solve this so I hope someone can help.

    I’m building a custom search to search a custom post type title and excerpt and it’s associated meta at the same time. I have it nearly there but i just can’t get it to work properly.

    Here is what I have in my functions.php (this is called by an AJAX function and displayed when the user searches)

    function display_all_products() {

			global $term;
			$term = trim(strip_tags($_POST['s']));

				if(!empty($_POST['s']))
				{
					add_filter( 'posts_where' , 'product_posts_where' );
				}

				$args = array(
					'posts_per_page' => 50,
					'paged' => 1,
					'post_type' => 'product',
					'order' => 'DESC',
				);

				$the_query = new WP_Query( $args );

				remove_filter('posts_where', 'product_posts_where');

				if($the_query->have_posts()) {
					while ( $the_query->have_posts() ) {
						$the_query->the_post();

						$pid = get_the_ID();
						$refno = get_post_meta( $pid, 'refno', true );
						$yr = get_post_meta( $pid, 'yr', true );
						$pieces = get_post_meta( $pid, 'pieces', true );
						$figures = get_post_meta( $pid, 'figures', true );

						?>

						<div class="my_box3">
							<h3><?php the_title(); ?></h3>
							<div class="padd10"><?php the_post_thumbnail(); ?></div>
							<div class="padd10">
								<ul>
									<li> <?php _e('Referance Number', 'AuctionTheme');?>: <?php  echo $refno; ?></li>
									<li> <?php _e('Year', 'AuctionTheme'); ?>: <?php echo $yr; ?></li>
									<li> <?php _e('Pieces', 'AuctionTheme'); ?>: <?php echo $pieces; ?></li>
									<li> <?php _e('Figures', 'AuctionTheme'); ?>: <?php echo $figures; ?></li>
								</ul>
							<label for="product">Select Product: <input type="radio" name="product" value="<?php the_ID(); ?>" /></label>
						</div>

						<?php
					}
				}

				wp_reset_query();
				//wp_reset_postdata();

			die();
	}

	add_action('wp_ajax_show_all_products', 'display_all_products');
	add_action('wp_ajax_nopriv_show_all_products', 'display_all_products');

	function product_posts_where( $where ) {

			global $wpdb, $term;
			$searchTerms = explode(' ', $term);

			$i = 1;
			$where .=" AND (";

			foreach ($searchTerms as $word) {
				if($i === 1) {
					$where .= " ({$wpdb->posts}.post_title LIKE '%$word%' OR {$wpdb->posts}.post_excerpt LIKE '%$word%')";
				} else {
					$where .= " OR ({$wpdb->posts}.post_title LIKE '%$word%' OR {$wpdb->posts}.post_excerpt LIKE '%$word%')";
				}
				$i++;
			}

			$where .=")";

		return $where;
	}

    I have tried using a seperate query to query the postmeta, but that means you can get multiple results of the same product.

    I have tried adding 'meta_value' =>$term to the WP_Query array but that doesn’t work as the posts_where filter gets in the way and i get no results.

    I have tried adding OR {$wpdb->postmeta}.meta_value LIKE '%$word%' to the end of the statement in the posts_where filter but that causes no results to show.

    I feel that the answer lies in adding to the posts_where filter but I cannot work it out.

    Or perhaps a completely different approach – any ideas are appreciated!

    If anyone can please help, i’ve looked at so many solutions but just can’t work out how to to do this.

    Thanks in advance,

    Ralph

    Ralph

Viewing 10 replies - 1 through 10 (of 10 total)

  • Hi,

    I’m familiar with PHP on WordPress, but I don’t understand what you want to do, I’m french, so I’m not so familair with English !

    Can you just explain what you want to do, without speak about what you already done to do that ?

    Thread Starter ralphonz

    (@ralphonz)

    Sure, Thanks for looking:)

    I am creating an AJAX powered search to search for my custom post type which is called ‘product’

    The search needs to find matching products by each word typed into the search box if any of the words match either: the post title, the post excerpt or the post meta fields – refno or year.

    So if some one searches for “blue 1989 6090” it will return products which match any of those words in any of it’s fields and order them by the most relevant – so products which match all three words come at the top, but products which only match one still show too.

    Does that make sense?

    Thanks ??

    I only understood that you want a simple search form, users have to fill it with a word and have to click “Go”.
    Then, you want to display the list of posts that matches the word, by displaying the title + excerpt + their meta data.
    Isn’t it ?

    If yes, I don’t think that a function in function.php may be the best solution.

    If you have a file called ‘search.php’ in your theme folder, you have just to customize it with the proper WordressCodex functions.
    If you don’t have this file called ‘search.php’ in your theme folder, create it by yourself or use the following example of code :

    https://pastebin.com/hb99f0LL

    The code above will displays this kind of thing :
    https://www.riversatile.fr/?s=surf

    I think it’s exactly what you want !

    Thread Starter ralphonz

    (@ralphonz)

    Hi thanks for the input. What I am trying to do It is more complicated than that.

    I can display the posts with the details no problem. My question is not about displaying the data that the search returns but is about modifying the search function itself. It is the search query that is not working right – I cannot get worpress to search the database for posts and postmeta at the same time and combine the results.

    It works if you just want to search for posts with a matching title or content.

    It works if you just want to search for posts with matching metadata.

    It doesn’t work if you want to search for posts with matching post title or matching metadata in the same query.

    To complicate things further the user should be able enter more than one word in the search and it should find posts that match any one word in the post or metadata rather than the whole string.

    Whaawww ! Effectively !

    So you should create a search form that contains more than 1 field !
    In this cas, you need 2 fields. Like that, 2 query at the same time, then return the resulting posts that matches the search.

    Could it work like that ?

    Thread Starter ralphonz

    (@ralphonz)

    Hahahahahahahaha!

    Amazing,

    That’s exactly what i’ve just done and it works!! Thankyou ??

    Well done !

    Thread Starter ralphonz

    (@ralphonz)

    Just in case anyone wants to know how here it is in all it’s glory:

    The HTML for the search boxes:

    <label for="product_select"><?php echo __('Search our database for your product')?></label><br/>
	    <label for="my-s">Keyword: </label><input type='text' name='my-s' id='my-s' /><br/>
		<label for="ref">Reference/Catalog Number: </label><input type='text' name='ref' id='ref' /><br/>
		<label for="yr">Year </label><input type='text' name='yr' id='yr' /><br/>
		<input type="submit" name="product_search_submit" id="product_search_submit" value="<?php _e('Find Products', 'AuctionTheme'); ?>">

    The Javascript for the AJAX call:

    [ Code moderated. For more than 10 lines of code please use pastebin.com instead. ]

    and the functions to respond to the call (functions.php for me):

    [ Code moderated. For more than 10 lines of code please use pastebin.com instead. ]

Viewing 10 replies - 1 through 10 (of 10 total)
  • The topic ‘How to Query Posts and Postmeta by keywords (for custom search)’ is closed to new replies.