WordPress.org

1. jdhcreative
   Member
   Posted 1 year ago #

   Is it possible to write a search that asks for various different Meta_Keys?

   I have posts with multiple custom field entries.

   Lets say a user wants to search for posts where I was reading a specific book (entered in the custom field) and only display the ones where I was happy when posting (another custom field).

   More Background:

   I figured out how to modify the search form. I entered all the inputs just to test. Then when you set the form action to home and request the variables you just set, you can call them (did a few echos to test. said "you serached for $var1 $var2, etc)

   Got it hooked up, but it doesn't work when I go trying to define the same thing over and over.

   Any suggestions on an approach here?

   <?PHP 
   
     $candy = $_REQUEST['candy'] ;
     $mood = $_REQUEST['mood'] ;
     $booktype = $_REQUEST['booktype'] ;
     $bookpgs = $_REQUEST['bookpgs'] ; 
   
    ?> 
   
    <?php
   
    $querystr = "
       SELECT wposts.*
       FROM $wpdb->posts wposts, $wpdb->postmeta wpostmeta
       WHERE wposts.ID = wpostmeta.post_id
   	AND wpostmeta.meta_key = 'candy'
   	AND wpostmeta.meta_value = '$candy'
   	AND wpostmeta.meta_key = 'mood'
   	AND wpostmeta.meta_value = '$mood'
   	AND wpostmeta.meta_key = 'booktype'
   	AND wpostmeta.meta_value = '$booktype'
   	AND wpostmeta.meta_key = 'bookpgs'
   	AND wpostmeta.meta_value = '$bookpgs'
       AND wposts.post_status = 'publish'
       AND wposts.post_type = 'post'
       ORDER BY wposts.post_date DESC
    ";
   
    $pageposts = $wpdb->get_results($querystr, OBJECT); ?>

   Thanks :-)

2. Kafkaesqui
   Moderator
   Posted 1 year ago #

   See my plugin:

   http://guff.szub.net/search-custom-fields/

3. jdhcreative
   Member
   Posted 1 year ago #

   Kafkaesqui,

   I appreciate your spirit in promoting your plugin. ;-)
   But I'm afraid it doesn't do what I'm looking for.

   Thanks for your suggestion. It's very close. If you could search multiple keys for their values respectively... then bam.

   I'm still working on an alternative where I just add columns to the database for each key field and make it a much easier search. I added the fields, but am having trouble having the values write to the database. Kind of a sql writing newbie. But it's coming along!

   any other suggestions?

4. Kafkaesqui
   Moderator
   Posted 1 year ago #

   Just fingering a plugin that may provide a solution is hard to call promotion, even when it is mine. I point to a lot of plugins here, often ones I had no hand in creating. ;)

   Anyway, I thought you may benefit from its source since its goal is to search custom fields. Note that modifying it for multiple custom fields key/value matching may not be *that* difficult, all depending on exactly what we're talking about here. For example, are all key/value fields required for your 'search'? Your original query (with the numerous AND clauses) shows that to be the case.

5. jdhcreative
   Member
   Posted 1 year ago #

   Kafkaesqui,

   Thanks again for the link to your plugin. It has given me a few good ideas to start with.

   In this bit of your code:

   function szub_search_custom_where($where) {
   	global $wp_query, $wp_version, $wpdb;
   	if( !empty($wp_query->query_vars['s']) && szub_is_search_key() ) {
   		$search = $wp_query->query_vars['s'];
   		$key = $_GET['key'];
                   $value = $_GET['value'];
   
   		$status = ($wp_version >= 2.1) ? 'post_type = \'post\' AND post_status = \'publish\'' : 'post_status = \'publish\'';
   		$where = " AND $wpdb->postmeta.meta_key = '$key' AND $wpdb->postmeta.meta_value = '$value' AND $status ";
   	}
   	return $where;
   }

   Is it possible to take $where re-run it through the process but this time with a different Key and Value that was specified in the search form?

   I also modified my own code to try achieving this. Do you see what I am trying to do?

   <?PHP 
   
     $candy = $_REQUEST['candy'] ;
     $mood = $_REQUEST['mood'] ;
     $booktype = $_REQUEST['booktype'] ;
     $bookpgs = $_REQUEST['bookpgs'] ; 
   
    ?> 
   
    <?php
   
    $querystr = "
       SELECT wposts.*
       FROM $wpdb->posts wposts, $wpdb->postmeta wpostmeta
       WHERE wposts.ID = wpostmeta.post_id
   	AND wpostmeta.meta_key = 'candy'
   		AND wpostmeta.meta_value = '$candy'
   		AND wposts.post_status = 'publish'
       AND wposts.post_type = 'post'
   	    ORDER BY wposts.post_date DESC
    ";
    $querystr .= "
       SELECT wposts.*
       FROM $wpdb->posts wposts, $wpdb->postmeta wpostmeta
       WHERE wposts.ID = wpostmeta.post_id
   	AND wpostmeta.meta_key = 'mood'
   		AND wpostmeta.meta_value = '$mood'
   		AND wposts.post_status = 'publish'
       AND wposts.post_type = 'post'
   	    ORDER BY wposts.post_date DESC
    ";
   
     $querystr .= "
       SELECT wposts.*
       FROM $wpdb->posts wposts, $wpdb->postmeta wpostmeta
       WHERE wposts.ID = wpostmeta.post_id
   	AND wpostmeta.meta_key = 'booktype'
   		AND wpostmeta.meta_value = '$bookpgs'
   		AND wposts.post_status = 'publish'
       AND wposts.post_type = 'post'
   	    ORDER BY wposts.post_date DESC
    ";
   
    $pageposts = $wpdb->get_results($querystr, OBJECT); ?>

   If I ask it for just one thing at a time she works like a charm... but I need to somehow filter my results. Time to put on another pot of coffee and keep sifting through my PHP book.... *jitters*

   Thanks for any insight.

6. jdhcreative
   Member
   Posted 1 year ago #

   Someone else out there has to be trying to get this to work, no?

7. jdhcreative
   Member
   Posted 1 year ago #

   !Update

   After much play, I feel like I'm so close but so far.

   Eventually I tried calling a SELECT within a SELECT. MySQL wasn't liking that too much. So now I'm trying to store one result as a variable, reorder the results, and then search my second series of requirements against them.

   But... No luck. Does the following code make sense to anyone?

   If I could get this to work I know a lot of happy word press boys and girls who would go nuts.

   <?php get_header(); ?>
   
   <?PHP 
   
     $area = $_REQUEST['area'] ;
     $bookpgs = $_REQUEST['bookpgs'] ;
     ?> 
   
   <?php
     $subresult = mysql_query("SELECT wposts.*
       FROM $wpdb->posts wposts, $wpdb->postmeta wpostmeta
       WHERE wposts.ID = wpostmeta.post_id
   	AND wpostmeta.meta_key = 'area'
   		AND wpostmeta.meta_value >= '$area'
   		AND wposts.post_status = 'publish'
       AND wposts.post_type = 'post'");
   
       $num = mysql_Num_Rows($subresult);
   
       $i=0;
   
       $subcriteria="";
   
       while ($i < m)  {
           $subcriteria .= $wpdb->get_results($subresult,$i,"area");
           $subcriteria .=",";
           $i++;
       }      
   
       $subcriteria=substr($subcriteria,0,-1); 
   
       $result = mysql_Query(
           "SELECT wposts.*
   	    FROM $wpdb->posts wposts, $wpdb->postmeta wpostmeta
       	WHERE wposts.ID = wpostmeta.post_id
   		AND wpostmeta.meta_key = 'bookpgs'
   		AND wpostmeta.meta_value >= '$bookpgs'
   		AND wposts.post_status = 'publish'
    	    AND wposts.post_type = 'post'
   		IN ($subcriteria)");
          $pageposts = $wpdb->get_results($result, OBJECT);
   	     ?>
   
   	<div id="primary" class="twocol-stories">
   		<div class="inside">
         <?php if ($pageposts): ?>
    <?php foreach ($pageposts as $post): ?>
    <?php setup_postdata($post); ?>
   
    <div class="post" id="post-<?php the_ID(); ?>">
    <h2><a href="<?php the_permalink() ?>" rel="bookmark" title="Permanent Link to <?php the_title(); ?>">
       <?php the_title(); ?></a></h2>
       <small><?php the_time('F jS, Y') ?> <!-- by <?php the_author() ?> --></small>
       <div class="entry">
          <?php the_content('Read the rest of this entry »'); ?>
       </div>
       <p class="postmetadata">Posted in <?php the_category(', ') ?> | <?php edit_post_link('Edit', '', ' | '); ?>
       <?php comments_popup_link('No Comments »', '1 Comment »', '% Comments »'); ?></p>
    </div>
    <?php endforeach; ?>
    <?php else : ?>
       <h2 class="center">Search For Postings</h2>
       <p class="center"></p>
       <?php include (TEMPLATEPATH . "/searchform.php"); ?>
    <?php endif; ?>
   
   			</div>
   
       </div>
   
   <?php get_sidebar(); ?>
   
   <?php get_footer(); ?>

8. noelgreen
   Member
   Posted 1 year ago #

   This is something a lot of us have been trying to do for some time. If you figure it out PLEASE post the answer here!

   Thanks!

9. jdhcreative
   Member
   Posted 1 year ago #

   Yeah. If I figure it out I'll be sure to share the answer.
   Otherwise you can find me with my Ruby on Rails book and a 12 pack over there ---> ... starting over.

10. mariostella
    Member
    Posted 1 year ago #

    Hello Jdhcreative, any luck?

11. jrgd
    Member
    Posted 1 year ago #

    hi
    (bumping the thread) - any news on this?
    I'm desperately trying to get something similar: i would like to provide on post & pages the custom field content linked to a search result page.
    I am using Kafkaesqui's plugin but i can't make it return any results (on wp2.6.2)

    I have also difficulties understanding at which point the SQL query is being formed on a search page. would be nice if anyone could point that out (i'm working on a mini plugin which echoes the sql query used for the loop)

    Any ideas or insight much much welcom - thanks in advance

12. maxaud
    Member
    Posted 1 year ago #

    I'm looking for the ability to search multiple custom field variables.

    Anyone accomplish this yet?

13. yoyo
    Member
    Posted 11 months ago #

    I think a solution for this problem might be:

    The result from the first query is used to filter the results in the second query.

    I am saying that in the second query you don't select all the posts
    

    SELECT wposts.*

    you will select only the posts that resulted from the first query.

14. yoyo
    Member
    Posted 11 months ago #

    <?php
    
     $querystr1 = "
        SELECT wposts.*
        FROM $wpdb->posts wposts, $wpdb->postmeta wpostmeta
        WHERE wposts.ID = wpostmeta.post_id
        AND wpostmeta.meta_key = 'candy'
        AND wpostmeta.meta_value = 'candy_value'
        AND wposts.post_status = 'publish'
        ORDER BY wposts.post_date DESC";
    
     $pageposts1 = $wpdb->get_results($querystr1, ARRAY_A);
     foreach($pageposts1 as $key){ $newarray1[$i] = $key["ID"]; $i++; }
    
     $querystr2 = "
        SELECT wposts.*
        FROM $wpdb->posts wposts, $wpdb->postmeta wpostmeta
        WHERE wposts.ID = wpostmeta.post_id
        AND wpostmeta.meta_key = 'book'
        AND wpostmeta.meta_value = 'book_value'
        AND wposts.post_status = 'publish'
        ORDER BY wposts.post_date DESC
     ";
    
     $pageposts2 = $wpdb->get_results($querystr2, ARRAY_A);
     foreach($pageposts2 as $key){ $newarray2[$i] = $key["ID"]; $i++; }
    
     $result = array_intersect($newarray1, $newarray2);
     foreach( $result as $key){	$where .= "$key OR ID = "; }
    
     $querystr ="
       SELECT $wpdb->posts.* FROM $wpdb->posts
       WHERE ID = $where 0
       AND $wpdb->posts.post_type = 'post'
       ORDER BY $wpdb->posts.post_date DESC";
     $pageposts = $wpdb->get_results($querystr, OBJECT);
    ?>
    
    <?php if ($pageposts): ?>
      <?php foreach ($pageposts as $post): ?>
        <?php setup_postdata($post); ?>
        <h2><a href="<?php the_permalink() ?>" rel="bookmark" title="Permanent Link to <?php the_title(); ?>">
             <?php the_title(); ?></a></h2>
      <?php endforeach; ?>
      <?php else : ?>
        <h2> Not Found</h2>
    <?php endif; ?>

    It's still in development, I will make some changes later to prevent some errors, but you can play with it and make it better.

15. StephenCronin
    Member
    Posted 11 months ago #

    Going back to the original post, you should be able to do this using the following:

    <?PHP
      $candy = $_REQUEST['candy'] ;
      $mood = $_REQUEST['mood'] ;
      $booktype = $_REQUEST['booktype'] ;
      $bookpgs = $_REQUEST['bookpgs'] ;
     ?> 
    
     <?php
     $querystr = "
        SELECT DISTINCT wposts.*
        FROM $wpdb->posts wposts, $wpdb->postmeta wpostmeta, $wpdb->postmeta wpostmeta2,
    	$wpdb->postmeta wpostmeta3, $wpdb->postmeta wpostmeta4
        WHERE wposts.ID = wpostmeta.post_id
    	AND wpostmeta.meta_key = 'candy'
    	AND wpostmeta.meta_value = '$candy'
    	AND wpostmeta2.meta_key = 'mood'
    	AND wpostmeta2.meta_value = '$mood'
    	AND wpostmeta3.meta_key = 'booktype'
    	AND wpostmeta3.meta_value = '$booktype'
    	AND wpostmeta4.meta_key = 'bookpgs'
    	AND wpostmeta4.meta_value = '$bookpgs'
        AND wposts.post_status = 'publish'
        AND wposts.post_type = 'post'
        ORDER BY wposts.post_date DESC
     ";
    
     $pageposts = $wpdb->get_results($querystr, OBJECT); ?>

    Note, rather than just setting one wpostmeta alias for $wpdb->postmeta, I've actually set 4, one for each key / value I want to search. This would result in a whole bucket of duplicates rows, so we need to add the DISTINCT keyword.

    I've used the technique before, but that was back in 2006 and not related to WordPress and using slightly different syntax, so I made sure to test it! I'm working on something similar and it works fine for me.

    Try it out and leave a comment here indicating whether it worked for you. I hope it helps.

16. StephenCronin
    Member
    Posted 11 months ago #

    Whoops, the code I posted above is wrong! I missed some WHERE conditions to tie the IDs together. The code should be:

    <?PHP
      $candy = $_REQUEST['candy'] ;
      $mood = $_REQUEST['mood'] ;
      $booktype = $_REQUEST['booktype'] ;
      $bookpgs = $_REQUEST['bookpgs'] ;
     ?> 
    
     <?php
     $querystr = "
        SELECT wposts.*
        FROM $wpdb->posts wposts, $wpdb->postmeta wpostmeta, $wpdb->postmeta wpostmeta2,
    	$wpdb->postmeta wpostmeta3, $wpdb->postmeta wpostmeta4
        WHERE wposts.ID = wpostmeta.post_id
    	AND wposts.ID = wpostmeta2.post_id
    	AND wposts.ID = wpostmeta3.post_id
    	AND wposts.ID = wpostmeta4.post_id
    	AND wpostmeta.meta_key = 'candy'
    	AND wpostmeta.meta_value = '$candy'
    	AND wpostmeta2.meta_key = 'mood'
    	AND wpostmeta2.meta_value = '$mood'
    	AND wpostmeta3.meta_key = 'booktype'
    	AND wpostmeta3.meta_value = '$booktype'
    	AND wpostmeta4.meta_key = 'bookpgs'
    	AND wpostmeta4.meta_value = '$bookpgs'
        AND wposts.post_status = 'publish'
        AND wposts.post_type = 'post'
        ORDER BY wposts.post_date DESC
     ";
    
     $pageposts = $wpdb->get_results($querystr, OBJECT); ?>

    Note, now that its written correctly, there's no need for the DISTINCT keyword, as it won't be selecting duplicates in the first place! Sorry if I led any of you down the garden path.

17. WordpressChina
    Member
    Posted 11 months ago #

    @StephenCronin: thanks for the code!! I have been waiting and looking for this too for a loooong time.

    But how should we implement the code in your last reply? Is that for generating a search form or for generating the output?

18. jdhcreative
    Member
    Posted 11 months ago #

    @StephenCronin

    Dude, that is awesome.

    Yea, it's been 9 long grueling months since I first posted this (and I had been working on it long before I asked for help) I finally just picked up a book, learned php/mysql, and made up my own damn solution outside of WP.

    @the world

    What you see here is a solution. I haven't put my real idea up to apply this method to LOL(candy and bookpages?) but yea, if this is something you're looking to do read this post carefully, PRINT TO PDF! And there's more answers then one. This is powerful. A php book wouldn't hurt either.

    *Cracks a victory beer* (and one for Stephen as well)

    @Kafkaesqui -- if you happen to read this, sorry again for the prior rudeness and thanks again for your past help. Reading back, that was just me in a dry, losing money on projects frenzy. Code and empty pizza boxes everywhere. Hope you know how it goes!

19. WordpressChina
    Member
    Posted 11 months ago #

    I am still confused, deeply...

    Ok, I tried to duplicate this as much as possible: I created fields for candy, mood, booktype, bookpgs.

    And then I took the code above from StephenCronin's last message wit his code, and when I add that to my index.php, the code disappears -- I see the normal page without anything different (yes, I already cleared the cache).

    Am I using this wrong? I want to create a search for the four fields of candy, mood, booktype, bookpgs. Does this not do that? Am In interpreting this thread totally incorrect? :(

    tks for your help.

20. Dunkkan
    Member
    Posted 11 months ago #

    Yes please, we need more info : where must we place de code ? in functions.php, in search.php ? :)

21. WordpressChina
    Member
    Posted 11 months ago #

    I tried adding it to my index.php and search.php, but nothing appears -- I see the rest of the page, but the code does not appear and when I View Source, it also does not show. This means that at least I know the PHP is written well and it's not causing problems :) but it also means that something is not being called to display.

22. jdhcreative
    Member
    Posted 11 months ago #

    The computer I wrote all my original code and did my WP 'hacking' on was stolen a month ago, otherwise I'd post the code right up. The 'lifelong' stuff of course I had backed up elsewhere however I lost a big chunk of my work across the board. :-/

    I still plan on finishing the problem, and I will post my solutions (and example of different implementations) on my personal playground with code for all. www.bradswebplayground.com

    -Brad

23. jdhcreative
    Member
    Posted 11 months ago #

    (and I'll post it here too! lol I just thought of that walking away... would be nice huh - duh. But it will be around Jan 1st before I get to it, so I don't want to just leave the thread dead. )

24. WordpressChina
    Member
    Posted 11 months ago #

    @jdhcreative: Thanks!! :)

    I will be sure to bump this in a few weeks to get your attention and I have already bookmarked your site and will look forward to Jan 1, 2009 (or thereabouts).

25. WordpressChina
    Member
    Posted 10 months ago #

    It's January 1... any word on your progress? ;-)

26. joehamilton
    Member
    Posted 10 months ago #

    this is rad!
    Just what I have been looking for..

    @ jdhcreative
    Thanks for the work so far.. looking forward to seeing more info on how to implement it.

27. Thomas1988
    Member
    Posted 9 months ago #

    I support all your cause ;-)

    I need to know how to use this code! Please help!

    Im ready to give a donation as sure.

28. Hip-Hop-Fan
    Member
    Posted 9 months ago #

    why you guys just dont use google custom search like i do...?

29. WordpressChina
    Member
    Posted 9 months ago #

    Google Custom Search has a few drawbacks and it's not as tightly integrated into WordPress as a plugin or script might be. most importantly, we want to search specific fields and some of those fields might be stored in the database and not obvious to Google... ;)

30. Thomas1988
    Member
    Posted 9 months ago #

    @WordpressChina : +1!

Topic Closed

This topic has been closed to new replies.