I've reached the database limit on my hosting platform. I want to add a function to my functions.php that deletes the oldest post in my database anytime that I publish a new post.

function deleteOldestPost(){
    global $wpdb;
    $prefix = $wpdb->prefix;
    $wpdb->query("DELETE code");
}
add_action('publish_post', 'deleteOldestPost');

Can someone please help me with the appropriate query?

I've reached the database limit on my hosting platform. I want to add a function to my functions.php that deletes the oldest post in my database anytime that I publish a new post.

function deleteOldestPost(){
    global $wpdb;
    $prefix = $wpdb->prefix;
    $wpdb->query("DELETE code");
}
add_action('publish_post', 'deleteOldestPost');

Can someone please help me with the appropriate query?

• functions
• query
• mysql
• wpdb
• sql

• Are you just wanting to target Core Posts, or all entries in the posts table? Have you also turned off revisions and removed old media that you're not using? Those are taking up space in the posts table too. – WebElaine Commented Jun 1, 2020 at 19:03
• @WebElaine hey! All Entries in the post table. I've done a lot of optimizing and currently have 200 posts and still under 1mb. However, I'm using a free plan that only allows 5mb db limit so I'm planning for the future. – Kareem Sayeed Commented Jun 1, 2020 at 19:53
• Did you try this plugin wordpress/plugins/wp-mass-delete I've faced the situation before where i have limited resource and its shared hosting. So i prefer to clean my database and media which is not in use. and this plugin too wordpress/plugins/advanced-database-cleaner I know you need sql query but that might leave off some transient data in tables. So to do it with WordPress way is good for your site. – Akash Commented Jun 1, 2020 at 20:12
• 1 Deleting the oldest entry in the posts table may have unintended consequences - you may be deleting a menu item, or an image, or who knows what. It would be wiser to check analytics to see what content is least visited and remove those posts/pages/cpts manually. – WebElaine Commented Jun 1, 2020 at 20:22
• @WebElaine You're totally right since menu items are also stored in wp_posts. I was planning on avoiding this problem by hardcoding my 5 menu items directly into customized child theme's header file. – Kareem Sayeed Commented Jun 1, 2020 at 21:21

1 Answer 1

I figured it out! Pretty simple actually.. You can replace post_date_gmt with ID depending on your needs. post_author != 1 will prevent the admin's post from being deleted, so the menu links and pages will stay intact :)

function deleteOldestPost(){
    global $wpdb;
    $prefix = $wpdb->prefix;
    $wpdb->query("DELETE
        FROM ".$prefix."posts where post_author != 1  order by post_date_gmt asc limit 1");
}
add_action('publish_post', 'deleteOldestPost');

There will be some transient data, i.e. wp_term_relationships. Any of the database optimize plugins will get rid of them.