1

I have entries in wp_postmeta like so (shortened for brevity:

post_id | meta_key | meta_value

integer | _thumbnail_id | integer

integer | rafi | integer

Now it's easy to select and delete all rows containing _thumb.

Same with rows containing rafi.

What I need to do is delete the rows with a common post_id AND both _thumb and rafi.

The reason is I only want to remove the featured image from posts that have the rafi key set.

I can delete matching _thumbnail_id rows thusly:

global $wpdb;

$wpdb->query( "

DELETE FROM $wpdb->postmeta

WHERE meta_key = '_thumbnail_id'

" );

But I am not sure how to proceed in terms of doing the proper selecting of the matching rows to set up the deletion.

Thanks for any ideas!

  • Would you please elaborate this delete the rows with a common post_id AND both _thumb and rafi a little bit more ? I mean do you need to delete the rows also which has one _thumbnail_id and one rafi id, cause that will make the post_id appear twice and that makes post_id duplicate. – CodeMascot May 24 '17 at 3:23
  • 1
    post_id(s) are duplicated in this table. meta_id is unique. So I want to delete, for say post_id 22, the rows that contain rafi and thumb, only if both are present. I hope that makes things more clear, and not more confusing. – Mike May 24 '17 at 4:08
  • So the table is actually: meta_id | post_id | meta_key | meta_value Where meta_id is a unique integer. – Mike May 24 '17 at 4:09
  • The reason post_id is duplicated in this table is I'm adding a flag to note when I manually added a featured image. It's part of the "Random Auto Featured Image" plugin I recently published. This work is part my effort to add an undo capability to the plugin. – Mike May 24 '17 at 4:12
2

After 5 hours of searching finally I wrote the SQL query and that is-

DELETE FROM {$wpdb->postmeta} 
WHERE  post_id IN (SELECT post_id 
                   FROM   (SELECT post_id 
                           FROM   {$wpdb->postmeta} 
                           WHERE  meta_key LIKE '_thumbnail_id' 
                                  AND post_id IN(SELECT post_id 
                                                 WHERE  meta_key LIKE 'rafi')) 
                          AS s) 

I saw your answer above, but using this kinda SQL query for this kinda problem is best practice as well as better.

Hope this helps.

  • Thanks! I couldn't quite put together the logic to as you showed above, hence my more crude but effective solution. What you posted is what I wanted to do originally. – Mike May 24 '17 at 15:42
  • You welcome. If this thing helped you please accept the answer by checking the check sign left of the answer. – CodeMascot May 24 '17 at 15:45
  • I decided to actually test your code before accepting the answer (what a concept, eh? :-) First problem. Not sure what to do with it. It looks like a mix of PHP variables and mysql commands ... Sorry for the newbie question. – Mike May 24 '17 at 17:37
  • You need to run that as SQL command. Go to MySQL command prompt or through Adminer or PHPMyAdmin SQL command window and run it. I tested it at my localhost, worked well. But also need to know if that works on your real data. Replace {$wpdb->postmeta} with postmeta table name, most cases it is wp_postmeta. – CodeMascot May 24 '17 at 17:43
  • Thanks again. I changed the db reference AND added the missing FROM in the second select and voila! It worked. – Mike May 24 '17 at 18:04
1

Here's what I came up with. I tested by enabling the "Random Auto Featured Image" plugin, which adds the missing thumbnails and adds the meta key rafi.

I then disable the plugin, and add the following code to functions.php.

Checking the database shows the expected results.

The next step is to incorporate this code into an undo/cleanup function for the plugin.

$sql = "SELECT * from wp_postmeta";

$result = $wpdb->get_results($sql);

foreach( $result as  $results ) {

  $mid = $results->meta_id;
  $pid = (string)$results->post_id;


  if ($results->meta_key == 'rafi_RandFeat'){
    foreach( $result as  $results1 ) {
            if($results1->meta_key == '_thumbnail_id' && $results1->post_id == $pid){
                $wpdb->query( "
                DELETE FROM $wpdb->postmeta
                WHERE meta_id = $mid;
                ");

                $wpdb->query( "
                DELETE FROM $wpdb->postmeta
                WHERE meta_id = $results1->meta_id;
                ");

        };
    };
  };
};

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.