Custom Fields Select Query – multiple fields, plus category

  • globalkris

    (@globalkris)


    15 years, 3 months 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.

Viewing 5 replies - 1 through 5 (of 5 total)
  • MichaelH

    (@michaelh)

    15 years, 3 months ago

    Have you seen Query based on Custom Field and Category?

    Thread Starter globalkris

    (@globalkris)

    15 years, 3 months 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.

    MichaelH

    (@michaelh)

    15 years, 3 months ago

    WHERE (meta_key = 'cf1'
AND <code>meta_value</code> = 'cf1value')
OR (meta_key = 'cf2'
AND <code>meta_value</code> = 'cf2value')
    artsaround

    (@artsaround)

    15 years, 1 month 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.

    artsaround

    (@artsaround)

    15 years, 1 month 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);

	?>
Viewing 5 replies - 1 through 5 (of 5 total)
  • The topic ‘Custom Fields Select Query – multiple fields, plus category’ is closed to new replies.