Take the 2-minute tour ×
WordPress Development Stack Exchange is a question and answer site for WordPress developers and administrators. It's 100% free, no registration required.

I am trying to to use $wpdb to retrieve a list of posts from the Database where the meta_value in the wp_postmeta table contains the current post_ID in inverted commas. eg. "10"

The inverted commas are the make sure that 10 doesn't also match 100 etc.

I can get it work work when I paste the exact of the meta value ie: a:1:{i:0;s:2:"10";}, however it is also returning all the revisions, not just the most recent post.

Here is the code I am currently using:

   $id = get_the_ID();
   $rows = $wpdb->get_results($wpdb->prepare( 
                  "
                  SELECT * 
                  FROM wp_postmeta
                  WHERE meta_key LIKE %s
                      AND meta_value = %s
                  ",
                  'roles_%_production',
                  '%"' . $id . '"%'
              ));

        // loop through the results
        if( $rows ) {
        ......
        }

Any ideas would be greatly appreciated.

Thanks

share|improve this question
add comment

2 Answers

In code you posted you do not retrieve 'list of posts' as you say, but retrieve a list of rows in the meta table. If you want really retrieve a list of posts rely on WP_Query using meta_query param.

Something like:

$id = '10'; // unserialized value

$args = array(
  'post_type' => 'post',
  'post_status' => 'publish',
  'posts_per_page' => -1,
  'meta_query' => array(
    array(
      'key' => 'roles_%_production',
      'value' => $id,
      'compare' => 'LIKE'
    )
  )
);
$query = new WP_Query( $args );
$rows = $query->get_posts();

If you want to use $wpdb (I don't know why) right query is something like:

<?php
$id = '10'; // unserialized value

global $wpdb;
$rows = $wpdb->get_col( $wpdb->prepare(
  "SELECT DISTINCT $wpdb->posts.ID FROM $wpdb->posts, $wpdb->postmeta
  WHERE $wpdb->posts.ID = $wpdb->postmeta.post_id AND
  $wpdb->posts.post_status = 'publish' AND
  $wpdb->posts.post_type = 'post' AND
  $wpdb->postmeta.meta_key = %s AND
  meta_value = %s",
  'roles_%_production',
  $id
) );
?>

$rows will contain an array of posts IDs. I've done this change to make a sense to the use of $wpdb. If you want retrieve all the fields use SELECT * instead of SELECT DISTINCT $wpdb->posts.ID, use $wpdb->get_results instead of $wpdb->get_col and add the line GROUP BY $wpdb->posts.ID to the query.

share|improve this answer
add comment

Two notes:

Searching meta data

The rule is simple: Serialized meta data (like an array converted to a:1:{i:0;s:2:"10";}) is not meant to be searchable. You'll have to convert your dataset to single values, so you can do proper meta_query searches.

The only really working way to search this is to query all the data, then deserialize it, render it if it matches your criteria, or skip it. There're enough discussions and Q/As on SO that cover that topic.

Prepared statements

There's like_escape(), which has to be used like this:

"%".like_escape( $string )."%"

The reason why you need to pre- and append the % chars is simple: You can decide your self if the LIKE should happen on both ends or only a single one (start, end, both sides).

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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