Using $wpdb to query posts with meta value containing current post_id

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

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.