Searching for posts with custom data in a numeric range (price)

  • Resolved zoen

    (@zoen)


    14 years, 6 months ago

    Hi

    I’ve been stuck on this for ages and am hoping someone can help me as I am not a programmer!

    I have a website where i use posts as products and i store additional data about the product, such as price in custom fields.

    I have set up the searchform.php to include several dropdowns, for selecting category, tag and price range in addition to the keyword text search to allow better searching for the products. The dropdown selections are being added to the URL or blank if not selected,
    An example would be: https://www.mysite.com/index.php?category=mycategory&tag=&price=10-20&s=+

    In my search.php template I am using the query_posts and customising it for the additional dropdown selections, but it seems i can’t use the meta compare for the price custom data, as I want to do a between range and also the prices are stored as strings not numbers.

    I’ve been searching around and in a roundabout way gleaned that I may be able to use the posts_join and posts_where and include some sql for the price bit of the query with a CAST, but not being a programmer I have tried and failed, producing all sorts of undesirable results.

    Can anybody tell me if I am along the right lines with this and quite how I go about it, or another way of searching for posts in a price range.

    Many thanks.

    This is what I have for building up my new query_post statement if this helps clarify what I am trying to do?

    <?php
	$selectedCategory='';
	$selectedPrice='';
	$newsearch='';

	$newsearch=$query_string;
	$newsearch.="&posts_per_page=14";
	$newsearch.="&post_type=post";
	$newsearch.="&author=3";

	if(isset($_GET["category"]) && $_GET["category"]!='') {
		$selectedCategory=$_GET["category"];
		$selectedCatId = get_term_by( 'slug', $selectedCategory, 'category' );
		$selectedCatId = $selectedCatId->term_id;
		$newsearch.="&cat=".$selectedCatId;
	}

	if(isset($_GET["price"])&& $_GET["price"]!='') {
		$selectedPrice=$_GET["price"];
		$parts = explode("-",$selectedPrice);
		$pricemin = $parts[0];
		$pricemax = $parts[1];

		**how do i add price to the new query string?***
	}

	query_posts($newsearch);

?>

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

  • Add this code in your functions.php:

    <?php
// Define the function that will create the join string
function mam_custom_field_range($keyname='',$min=false,$max=false) {
  global $mam_global_join,$wpdb;
  if ($keyname && ($min !== false || $max !== false)) {
    $mam_global_join =
      " JOIN $wpdb->postmeta acfr_$keyname ON
      ( $wpdb->posts.ID = acfr_$keyname.post_id
        AND acfr_$keyname.meta_key = '$keyname'";
    if ($min !== false) $mam_global_join .= " AND acfr_$keyname.meta_value >= $min";
    if ($max !== false) $mam_global_join .= " AND acfr_$keyname.meta_value <= $max";
    $mam_global_join .= ')';
  }
}
// Define the join filter function
function mam_posts_join ($join) {
   global $mam_global_join;
   if ($mam_global_join) $join .= $mam_global_join;
   return $join;
}
// Add the filter to the hooks
add_filter('posts_join','mam_posts_join');
?>

    And use this to set the join (assuming the meta_key name is ‘Price’):

    if(isset($_GET["price"])&& $_GET["price"]!='') {
   $selectedPrice=$_GET["price"];
   $parts = explode("-",$selectedPrice);
   $pricemin = $parts[0];
   $pricemax = $parts[1];

   mam_custom_field_range('Price',$pricemin,$pricemax);
}
    Thread Starter zoen

    (@zoen)

    Fantastic! Works like a dream.
    Thank you so much for your help!

    You are welcome!

    In regard to the solution above… I ran into the same problem, however my custom field data starts with a currency symbol. I tried to use substr to start after the $/€, without success.

    Additionally, I’m not sure how I should fill a variable with the click of a link, as my intention was to create several pages with a custom query, but this search-template-solution seems to be easier&better!
    Till now I’ve a row of simple links, how can they transmit a variable, or do I need to use buttons ?

Viewing 4 replies - 1 through 4 (of 4 total)
  • The topic ‘Searching for posts with custom data in a numeric range (price)’ is closed to new replies.