Delete oldest wordpress post (SQL query)

Question

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?

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.