Custom Fields Search

See my plugin:

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

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?

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.

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.

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

!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(); ?>

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!

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.

Hello Jdhcreative, any luck?

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

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

Anyone accomplish this yet?

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.

<?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.

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.

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.