Remove all posts except bulk URLs list

kabe said:

DELETE FROM posts WHERE url NOT IN ('url1', 'url2', 'url3', ...);

Click to expand...

kabe said:

DELETE FROM posts WHERE url NOT IN ('url1', 'url2', 'url3', ...);

Click to expand...

jootess said:

I can not find url column in the wp-posts table!!!
Where is the url column? Which table?

Click to expand...

jootess said:

I tried this code workes properly but som of our URLs has emoji or non-english chars... This code not work for those URLs. What should I do?

Click to expand...

kabe said:

DELETE FROM posts WHERE url NOT IN ('url1', 'url2', 'url3', ...);

Click to expand...

jootess said:

Hello, I want to remove all posts except bulk URLs from MySql.
How can I do it?

Click to expand...

BlogPro said:

Hey,

Not sure if you still need help with this. But I wrote a tad more clear format for you.

1. Getting Post IDs from URL

First we convert your URLs to Post IDs

We use the WordPress url_to_postid function to do this

PHP:

<?php

// Include WordPress core files.
define('ABSPATH', dirname(__FILE__) . '/');
include(ABSPATH . 'wp-load.php');


// Define an array of post URLs you want to delete.
$post_urls = array(
    'https://domain.com/post1-to-delete/',
    'https://domain.com/post2-to-delete'
    // Add more URLs as needed.
);

// Initialize an array to store the post IDs.
$post_ids_to_delete = array();

foreach ($post_urls as $url) {
    // Get the post ID from the URL.
    $post_id = url_to_postid($url);

    if ($post_id !== 0) {
        // Post ID is valid; add it to the list of IDs to delete.
        $post_ids_to_delete[] = $post_id;
    }
}

// Output the list of post IDs to delete.
echo 'Post IDs to delete: ' . implode(', ', $post_ids_to_delete);
?>

You can save this as a PHP file such as postIDs.php and add it to the root of your WordPress installation. Then visit domain.com/postIDs.php

It should give you an output like below

Code:

Post IDs to delete: 201, 197, 22, 343, 664, 5552

2. Running SQL to delete the posts and associated orphan postmeta

Then you can run an SQL command such as

SQL:

DELETE posts, postmeta
FROM wp_posts AS posts
LEFT JOIN wp_postmeta AS postmeta ON posts.ID = postmeta.post_id
WHERE posts.ID IN (1, 2, 3, ... /* List of Post IDs */)
AND posts.post_type = 'post'
AND posts.post_date > '2023-09-27 00:00:00';

The last line will only affect date where it's greater than whatever you set it to.

//

P.S - Do take a backup of your database before messing with anything.
P.P.S - Try the SQL and the script with a smaller number of posts first to verify correct post IDs are retrieved and the deletion occurs.

Hope this helps, let me know if you're stuck or need anymore help.

Click to expand...

vers said:

Hi, im going to try this again, but i get a Timeout Error when i execute the SQL because i have like 30k posts. I asked my hosting support and they said me that i cant modify the timeout server. Do you know a hosting or a trick to execute it?? Thanks

Click to expand...