Get posts/pages with certain custom meta value

  • Resolved jasondrey13

    (@jasondrey13)


    12 years, 9 months ago

    I’m trying to structure a query to get all posts/pages with a value of “on” in the custom meta field “event_important”. The query is used to output a special menu.

    I think I’m fairly close, but I’m still getting empty result arrays. Am I missing something?

    Thanks!

    $querystr = "
    SELECT wposts.*
    FROM $wpdb->posts wposts, $wpdb->postmeta wpostmeta
    WHERE wpostmeta.meta_key = 'event_important'
    AND  wpostmeta.meta_value = 'on'
    ";

 $primary_events = $wpdb->get_results($querystr, OBJECT);

 if ($primary_events): ?>
 <h1>Events</h1>
	<ul>
  <?php global $post; ?>
  <?php foreach ($primary_events as $post): ?>
    <?php setup_postdata($post); ?>
<li><a href="<?php the_permalink() ?>"><?php the_title(); ?><span><?php echo get_pretty_date() ?></span></a></li>
  <?php endforeach; ?>

  <?php else : ?>
    <h1 class="center">No Events</h1>
    <p class="center">Sorry, there are no events yet.</p>
 <?php endif; ?>
Viewing 2 replies - 1 through 2 (of 2 total)

  • As far as the query goes, this should work. I haven’t got your data set up, so I can’t test with that, but this does return a correct result here.

    $query = “SELECT
    wposts.*
    FROM “.$wpdb->posts.” AS wposts
    INNER JOIN “.$wpdb->postmeta.” AS wpostmeta
    ON wpostmeta.post_id = wposts.ID
    AND wpostmeta.meta_value = ‘event_important’
    AND wpostmeta.meta_value = ‘on'”;

    In your query, you didn’t give MySQL any idea on how to join the two tables, so I’m sure that it would have gotten very confused about just what you wanted. The INNER JOIN that I’ve added into it tells MySQL to join the two tables on the right columns to match the ID correctly, and will only return posts that have that value set in the postmeta table.

    Thread Starter jasondrey13

    (@jasondrey13)

    That works! Thank you!

    (I only had to change the first meta_value to meta_key.)

    Here’s the final code:

    $querystr = "SELECT wposts.*
FROM ".$wpdb->posts." AS wposts
INNER JOIN ".$wpdb->postmeta." AS wpostmeta
ON wpostmeta.post_id = wposts.ID
AND wpostmeta.meta_key = 'event_important'
AND wpostmeta.meta_value = 'on'";

 $primary_events = $wpdb->get_results($querystr, OBJECT);

 if ($primary_events): ?>
 <h1>Events</h1>
	<ul>
  <?php global $post; ?>
  <?php foreach ($primary_events as $post): ?>
    <?php setup_postdata($post); ?>
<li><a href="<?php the_permalink() ?>"><?php the_title(); ?><span><?php echo 'Get the Date'; ?></span></a></li>
  <?php endforeach; ?>

  <?php else : ?>
    <h1 class="center">No Events</h1>
    <p class="center">Sorry, there are no events yet.</p>
 <?php endif; ?>
Viewing 2 replies - 1 through 2 (of 2 total)
  • The topic ‘Get posts/pages with certain custom meta value’ is closed to new replies.