How do I update post based on meta_key in another table?

Question

So I have a function that is called by the wp_schedule_event (cron job) once daily. The goal of the function is to set all posts with a field wpcf-engine-days-to-go that has a value of "0" to a draft.

It isn't working, and I suspect the problem is with my SQL. I'm referencing meta values that aren't in the row (or table) of wp_posts. How do I reference wp_meta table in connection with wp_posts?

    register_activation_hook(__FILE__, 'tdengine_my_activation');
add_action('tdengine_my_daily_event', 'tdengine_do_this_daily');
function tdengine_my_activation() { 

wp_schedule_event(time(), 'daily', 'tdengine_my_daily_event');
}

function tdengine_do_this_daily() {
    global $wpdb;
    $query = "UPDATE `" . $wpdb->prefix . "posts` SET `post_status`=`draft` WHERE `meta_key` = 'wpcf-engine-days-to-go' = 0 ";
    $wpdb->query($query);
}

register_deactivation_hook(__FILE__, 'tdengine_my_deactivation');
function tdengine_my_deactivation() {
    wp_clear_scheduled_hook('tdengine_my_daily_event');
}

Answer 1

I couldn't comment, because i dont have the rep

Your code will not update because meta_key field is not in the wp_post table.

First, you'll want to query all posts where the meta_key wpcf-engine-days-to-go = 0 and then iterate through the post ID's and make your changes to the wp_posts.

Un-tested Example:

$meta_value = 0; 
$results = $wpdb->get_results("SELECT * from wp_posts, wp_postmeta
                               WHERE wp_posts.ID = wp_postmeta.post_id
                               AND  wp_posts.post_status = 'publish' 
                               AND  wp_postmeta.meta_key ='wpcf-engine-days-to-go' 
                               AND  wp_postmeta.meta_value = '".$meta_value ."'", ARRAY_A );

foreach($results as $row){
            // do update on this POST
           $update = $wpdb->update('wp_post',
                        array('post_status' => 'draft'),                        
                        array('ID'    => $row['ID'] ), 
                        array(
                                '%s',   // string
                            ),
                        array( '%d') // id
                        );
    }
}