WordPress.org

Ready to get started?Download WordPress

Forums

Custom Fields Select Query - multiple fields, plus category (6 posts)

  1. globalkris
    Member
    Posted 5 years ago #

    I'm using the code below to select posts from a particular category that matches a chosen custom field key and value. I'm trying to figure out how I can change the code so that it selects posts that match a particular category, as well as multiple chosen custom field keys and values.

    SELECT * FROM $wpdb->posts
    LEFT JOIN $wpdb->postmeta ON($wpdb->posts.ID = $wpdb->postmeta.post_id)
    LEFT JOIN $wpdb->term_relationships ON($wpdb->posts.ID = $wpdb->term_relationships.object_id)
    LEFT JOIN $wpdb->term_taxonomy ON($wpdb->term_relationships.term_taxonomy_id = $wpdb->term_taxonomy.term_taxonomy_id)
    WHERE $wpdb->term_taxonomy.term_id = 2
    AND $wpdb->term_taxonomy.taxonomy = 'category'
    AND $wpdb->posts.post_status = 'publish'
    AND $wpdb->postmeta.meta_key = 'gametype'
    AND $wpdb->postmeta.meta_value= '$gametype'
    ORDER BY $wpdb->postmeta.meta_value ASC

    Any help would be greatly appreciated.

  2. MichaelH
    Member
    Posted 5 years ago #

  3. globalkris
    Member
    Posted 5 years ago #

    Yes - that's pretty much where I grabbed the code above. I just don't know how to have it select posts that match category and multiple custom fields, as opposed to just one metakey/value pair.

  4. MichaelH
    Member
    Posted 5 years ago #

    WHERE (meta_key = 'cf1'
    AND <code>meta_value</code> = 'cf1value')
    OR (meta_key = 'cf2'
    AND <code>meta_value</code> = 'cf2value')
  5. artsaround
    Member
    Posted 5 years ago #

    Were you ever able to come up with a solution to this problem, I have the exact same one. I tried MichaelH's approach, and although the page compiles I don't get any results returned.

  6. artsaround
    Member
    Posted 5 years ago #

    If anyone stumbles across this post the following code, which is a slight modification of danbutcher's original from this forum post works well for me.

    <?php
    	 $restriction1 = 'geographic_area';
    	 $restriction2 = 'species';
    	 $restriction3 = 'donor';
    
    	 $value1 = 'India';
    	 $value2 = 'Calisaya';
    	 $value3 = 'India Museum';
    
    	 $querystr = "
    		SELECT * FROM $wpdb->posts
    		LEFT JOIN $wpdb->postmeta AS $restriction1 ON(
    		$wpdb->posts.ID = $restriction1.post_id
    		AND $restriction1.meta_key = '$restriction1'
    		)
    		LEFT JOIN $wpdb->postmeta AS $restriction2 ON(
    		$wpdb->posts.ID = $restriction2.post_id
    		AND $restriction2.meta_key = '$restriction2'
    		)
    		LEFT JOIN $wpdb->postmeta AS $restriction3 ON(
    		$wpdb->posts.ID = $restriction3.post_id
    		AND $restriction3.meta_key = '$restriction3'
    		)
    		WHERE $wpdb->posts.post_status = 'publish'
    		AND $restriction1.meta_value = '$value1'
    		AND $restriction2.meta_value = '$value2'
    		AND $restriction3.meta_value = '$value3'
    		ORDER BY species.meta_value ASC
    	 	";
    
    	 $pageposts = $wpdb->get_results($querystr, OBJECT);
    
    	?>

Topic Closed

This topic has been closed to new replies.

About this Topic