Taking WordPress table prefixes into account

Question

Given the code below how would I account for the table prefix that can change from installation to installation? Is there a better way to write it than an SQL query? Is there a variable I've missed that provides the table prefix for times like this?

// setting posts with current date or older to draft
if (!wp_next_scheduled('sfn_expire_hook')){
    wp_schedule_event( time(), 'hourly', 'sfn_expire_hook');
}
add_action( 'sfn_expire_hook', 'sfn_show_expire');
function sfn_show_expire(){
    global $wpdb;
    $server_time = date('mdy');
    $result = $wpdb->get_results("SELECT * FROM GrandDn4wP_posts WHERE post_type = 'show' AND post_status = 'publish'");
    if( !empty($result)) foreach ($result as $a){
        $show_time = get_the_time('mdy', $a->ID);
        if ( $server_time > $show_time ){
           $my_post = array();
           $my_post['ID'] = $a->ID;
           $my_post['post_status'] = 'draft';
           wp_update_post( $my_post );
        }
    } // end foreach
}

Answer 1

You can rewrite the code like this:

// setting posts with current date or older to draft
if (!wp_next_scheduled('sfn_expire_hook')){
    wp_schedule_event( time(), 'hourly', 'sfn_expire_hook');
}
add_action( 'sfn_expire_hook', 'sfn_show_expire');
function sfn_show_expire(){
    global $wpdb;
    $server_time = date('mdy');
    $result = $wpdb->get_results("SELECT * FROM $wpdb->posts WHERE post_type = 'show' AND post_status = 'publish'");
    if( !empty($result)) foreach ($result as $a){
        $show_time = get_the_time('mdy', $a->ID);
        if ( $server_time > $show_time ){
           $my_post = array();
           $my_post['ID'] = $a->ID;
           $my_post['post_status'] = 'draft';
           wp_update_post( $my_post );
        }
    } // end foreach
}

but a better way to write it would be to use the function get_posts:

// setting posts with current date or older to draft
if (!wp_next_scheduled('sfn_expire_hook')){
    wp_schedule_event( time(), 'hourly', 'sfn_expire_hook');
}
add_action( 'sfn_expire_hook', 'sfn_show_expire');
function sfn_show_expire(){
    $server_time = date('mdy');
    $result = get_posts( array( post_type => 'show', post_status => 'publish' ) );
    if( !empty($result)) foreach ($result as $a){
        $show_time = get_the_time('mdy', $a->ID);
        if ( $server_time > $show_time ){
           $my_post = array();
           $my_post['ID'] = $a->ID;
           $my_post['post_status'] = 'draft';
           wp_update_post( $my_post );
        }
    } // end foreach
}