WordPress.org

1. tprod
   Member
   Posted 2 years ago #

   Hi everyone,

   I try to do a custom search form and I have a problem to do a between width custom fields for this Real Estate website. I try to put a filter where in functions.php like this :

   function filter_where($where = '') {
   	if(!empty($_REQUEST['prix_mini'])) $where .= " AND wposts.ID = wpostmeta.post_id AND wpostmeta.meta_key = 'mprix' AND wpostmeta.meta_value >= '".$_REQUEST['prix_mini']."'";
   	if(!empty($_REQUEST['key'])) $where .= " AND (post_title LIKE '%".$_REQUEST['key']."%' OR post_content LIKE '%".$_REQUEST['key']."%')";
   	return $where;
   }
   add_filter('posts_where', 'filter_where');

   But I have no results

   Any ideas ?

   Thanks for your help.

2. vtxyzzy
   Member
   Posted 2 years ago #

   We do not have enough information. I suspect that you need to add a join to the postmeta table. Please post the query and parameters, and $wp_query->request after the query.

   This thread may also provide some help.

3. tprod
   Member
   Posted 1 year ago #

   Thanks for your answer and sorry about lake of information about my request.

   Here is my searchform :

   <form id="searchform" method="get" action="<?php echo $_SERVER['REQUEST_URI']; ?>">
   		<input id="key" name="key" type="text" onfocus="this.value='';" value="<?php if(!empty($_REQUEST['key'])): ?><?php echo $_REQUEST['key']; ?><?php else: ?>Recherche par mots cl&eacute;s<?php endif; ?>" size="25" />
   		<select name="nb" id="nb">
   		<option value="" <?php if(empty($_REQUEST['nb'])):?>selected="selected"<?php endif; ?> onfocus="this.value='';">Nombre de pi&egrave;ces</option>
   		<option <?php if(!empty($_REQUEST['nb']) && $_REQUEST['nb'] == 1):?>selected="selected"<?php endif; ?>>1</option>
   		<option <?php if(!empty($_REQUEST['nb']) && $_REQUEST['nb'] == 2):?>selected="selected"<?php endif; ?>>2</option>
   		<option <?php if(!empty($_REQUEST['nb']) && $_REQUEST['nb'] == 3):?>selected="selected"<?php endif; ?>>3</option>
   		<option <?php if(!empty($_REQUEST['nb']) && $_REQUEST['nb'] == 4):?>selected="selected"<?php endif; ?>>4</option>
   		<option <?php if(!empty($_REQUEST['nb']) && $_REQUEST['nb'] == 5):?>selected="selected"<?php endif; ?>>5</option>
   		<option <?php if(!empty($_REQUEST['nb']) && $_REQUEST['nb'] == 6):?>selected="selected"<?php endif; ?>>6</option>
   		</select>
   		<input id="prix_mini" name="prix_mini" type="text" onfocus="this.value='';" value="<?php if(!empty($_REQUEST['prix_mini'])): ?><?php echo $_REQUEST['prix_mini']; ?><?php else: ?>Prix mini<?php endif; ?>" size="10" />
   		<input id="prix_maxi" name="prix_maxi" type="text" onfocus="this.value='';" value="<?php if(!empty($_REQUEST['prix_maxi'])): ?><?php echo $_REQUEST['prix_maxi']; ?><?php else: ?>Prix maxi<?php endif; ?>" size="10" />
   		<input id="searchsubmit" name="searchsubmit" type="submit" value="Trouver" onclick="if(key.value == 'Recherche par mots cl&eacute;s')key.value='';if(prix_mini.value == 'Prix mini')prix_mini.value='';if(prix_maxi.value == 'Prix maxi')prix_maxi.value='';" />
   </form>

   Then my request :
   

   <?php global $query_string; ?>
   <?php
   if(!empty($_REQUEST['nb'])) $search = '&meta_key=qpieces&meta_compare=&meta_value='.$_REQUEST['nb'];
   if(!empty($_REQUEST['prix_mini'])) $search = '&meta_key=mprix&meta_compare=>=&meta_value='.$_REQUEST['prix_mini'];
   if(!empty($_REQUEST['prix_maxi'])) $search = '&meta_key=mprix&meta_compare=<=&meta_value='.$_REQUEST['prix_maxi'];
   ?>
   <?php query_posts($query_string.'&orderby=meta_value&meta_key=mprix&order=ASC'.$search); ?>

   I'm doing a filter where but I only want to search with all these elements at the same time et not one a time.

   And the $wp_query -> request

   SELECT SQL_CALC_FOUND_ROWS wp_posts.* FROM wp_posts INNER JOIN wp_term_relationships ON (wp_posts.ID = wp_term_relationships.object_id) INNER JOIN wp_term_taxonomy ON (wp_term_relationships.term_taxonomy_id = wp_term_taxonomy.term_taxonomy_id) JOIN wp_postmeta ON (wp_posts.ID = wp_postmeta.post_id) WHERE 1=1 AND wp_term_taxonomy.taxonomy = 'category' AND wp_term_taxonomy.term_id IN ('7') AND wp_posts.post_type = 'post' AND (wp_posts.post_status = 'publish' OR wp_posts.post_status = 'private') AND wp_postmeta.meta_key = 'mprix' GROUP BY wp_posts.ID ORDER BY wp_postmeta.meta_value ASC LIMIT 0, 5

   How can I do an inner join in the filter where with custom fields (wppostmeta table) ? Any ideas ?

   I see also the link of the post you give me. It's very interesting but how can I add a supplementary search with : search by terms (LIKE on title or content), number of rooms (meta compare with egal)

   Thanks for your help

4. vtxyzzy
   Member
   Posted 1 year ago #

   This is a lot of code, and I cannot test it, but it should be close to correct.

   First, add these functions to your functions.php:

   <?php function mam_posts_fields ($fields) {
      global $mam_global_fields;
      if ($mam_global_fields) $fields .= $mam_global_fields;
      return $fields;
   }
   function mam_posts_join ($join) {
      global $mam_global_join;
      if ($mam_global_join) $join .= $mam_global_join;
      return $join;
   }
   function mam_posts_where ($where) {
      global $mam_global_where;
      if ($mam_global_where) $where .= $mam_global_where;
      return $where;
   }
   function mam_posts_orderby ($orderby) {
      global $mam_global_orderby;
      if ($mam_global_orderby) $orderby = $mam_global_orderby;
      return $orderby;
   }
   add_filter('posts_fields','mam_posts_fields');
   add_filter('posts_join','mam_posts_join');
   add_filter('posts_where','mam_posts_where');
   add_filter('posts_orderby','mam_posts_orderby');
   ?>

   Then, for your nb, prix_mini, and prix_maxi, use code like this:

   <?php
   if(!empty($_REQUEST['nb'])) {
    $mam_global_fields = ', nb.meta_value nb_value';
    $mam_global_join = " JOIN $wpdb->postmeta nb
       ON ({$wpdb->posts}.ID = nb.post_id AND nb.meta_key = 'qpieces'
          AND nb.meta_value = {$_REQUEST['nb']})";
   }
   if(!empty($_REQUEST['prix_mini'])) {
     $mam_global_fields .= ', mini.meta_value mini_value';
     $mam_global_join .= " JOIN $wpdb->postmeta mini
        ON ({$wpdb->posts}.ID = mini.post_id AND mini.meta_key = 'mprix'
           AND mini.meta_value >= {$_REQUEST['prix_mini']})";
   }
   if(!empty($_REQUEST['prix_maxi'])) {
     $mam_global_fields .= ', maxi.meta_value maxi_value';
     $mam_global_join .= " JOIN $wpdb->postmeta maxi
        ON ({$wpdb->posts}.ID = maxi.post_id AND maxi.meta_key = 'mprix'
           AND maxi.meta_value <= {$_REQUEST['prix_maxi']})";
   }
   ?>

   Finally, use any other parameters you need in your query:

   <?php query_posts('posts_per_page=10');

5. reko
   Member
   Posted 1 year ago #

   This is so great i want to cry! The mysql-queries can be such a pain in the ass sometimes!

   Thanks a million!

6. vtxyzzy
   Member
   Posted 1 year ago #

   You are welcome! Now, please use the dropdown at top right to mark this topic 'Resolved'.

7. tprod
   Member
   Posted 1 year ago #

   Thanks again vtxyzzy I'm going to test you script I think it'll work.

   Thanks again for your help

8. conualfy
   Member
   Posted 1 year ago #

   Thanks vtxyzzy, it works perfectly with the custom fields.

   Still, it has a little issue: it's not taking into consideration the s= parameter, the search text input.

9. ancawonka
   Member
   Posted 1 year ago #

   Thanks a lot vtxyzzy! Your examples were really helpful.

Topic Closed

This topic has been closed to new replies.