0

I'm trying to put together a list of all posts where the metadata matches a specific query. It appears to work fine until I try to output the data, at which point it is repeated continuously.

I expected it to display 16 23 but instead it shows 16 23 16 23 16 23 16 23 16 23 16 23 16 23 16 23 16 23 16 23

Here's the code:

<?php
global $wpdb;
$results =$wpdb->get_results("SELECT post_id FROM $wpdb->posts , $wpdb->postmeta where  $wpdb->postmeta.meta_value = '2015/06/03'

AND meta_key='mymvkey'", OBJECT);

foreach ($results as $result){
print_r ($result->post_id);
}
?>

I'm still trying to get the hang of handling arrays (and not just in php) so that's probably where I'm going wrong. But every post I've read suggests that this should work. Is there any way to stop it repeating and just display the data once?

1

The SQL you want would be:

$sql = "
  SELECT DISTINCT post_id 
  FROM $wpdb->posts, $wpdb->postmeta 
  where $wpdb->postmeta.meta_value = '2015/06/03'";
$results =$wpdb->get_results($sql);

But there is no reason to be involving the $wpdb->posts table at all, since the post_id value is a $wpdb->postmeta table column.

$sql = "
  SELECT DISTINCT post_id 
  $wpdb->postmeta 
  where $wpdb->postmeta.meta_value = '2015/06/03'";
$results =$wpdb->get_col($sql);

Note that I changed $wpdb->get_results to wpdb->get_col as you are only returning a single column of data. The results will come out needing less manipulation afterward.

You could also skip the DISTINCT and use PHP's array_unique():

$sql = "
  SELECT post_id 
  $wpdb->postmeta 
  where $wpdb->postmeta.meta_value = '2015/06/03'";
$results =$wpdb->get_col($sql);
$results = array_unique($results);

However, the far more correct way would be to use WP_Query. I was going to suggest that but then noticed that you are not searching for a meta_key at all, but are searching the whole table. That is going to cause trouble eventually. Suppose some plugin stores data in the $wpdb->postmeta table with that date format? Then your code will retrieve those values as well, which is certainly not intended behavior. Someone could even enter such dates as a custom field right from the stock post edit panel. You should specify a key, something like:

$args = array(
  'fields' = 'ids',
  'ignore_sticky_posts' => true,
  'meta_query' => array(
    array( 
      'key' => 'my_date_key',
      'value' => '2015/06/03',
    )
  )
);
$pids = new WP_Query($args);
  • I thought I had specified the meta key: $wpdb->postmeta.meta_value = '2015/06/03' AND meta_key='mymvkey'", OBJECT); – CharlesStamp Jun 23 '15 at 9:18
  • Sorry, that might've seemed a little brusque. But I'm still unclear on whether or not I'd already specified the meta_key. In the end, all I had to do was remove the $wpdb->posts part as you suggested, and changed print_r to printf. I'm not sure why it worked, but it did. – CharlesStamp Jul 9 '15 at 10:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.