Take the 2-minute tour ×
WordPress Development Stack Exchange is a question and answer site for WordPress developers and administrators. It's 100% free, no registration required.

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
}
share|improve this question
    
In case someone is looking for the table prefix variable, here are a couple of ways to access it: (i) $wpdb->prefix within the global $wpdb scope and (ii) with the $table_prefix variable. –  joelhaus Feb 25 '11 at 22:52

1 Answer 1

up vote 5 down vote accepted

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
}
share|improve this answer
    
why is get_posts preferred? –  curtismchale Oct 13 '10 at 16:23
    
It is the recommended way to retrieve posts from the database in that case, and it is more futureproof. –  sorich87 Oct 13 '10 at 16:35
3  
get_posts() abstracts the database calls so that you're never directly calling a query. If someone tries to use a plug-in on a site using a non-MySQL database and you're using direct queries, your plug-in could break. I've seen some sites that use MS SQL, some that use flat databases (text files) etc. get_posts() can use all of these if you have the right abstraction layer set up. –  EAMann Oct 13 '10 at 16:44

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.