get_post_meta fails after switch_to_blog

Question

I have some code for custom cron in wordpress multisite, that from each blog site, gets their posts and postmeta data and put them on an xml structure.

But, when I retrive metadata (using get_post_meta) from each blog (using switch_to_blog), I get the data from the principal site (blog_id=1) not from the site I would like (for instance blog_id=33)

This is an example of part of my code:

function generate_xml(33);

function generate_xml($blog_id){
global $wpdb;

$change_ok = switch_to_blog($blog_id);
if ( is_wp_error($change_ok) ) {
    WriteErrorLog($change_ok->get_error_message());
}
else {
    $xml = new SimpleXMLElement('<?xml version="1.0" encoding="UTF-8"?><data></data>', null, false);
    $Body = $xml->addChild('Body');
    $add_adverts = $Body->addChild('add_adverts');

    $sql_posts = "select id, post_title, post_content from wp_".$blog_id."_posts where post_type = 'custom_add' and post_status = 'publish'";
    $result_posts = $wpdb->get_results ($sql_posts);
    foreach ( $result_posts as $data_posts ){
        $post_id_loop = $data_posts->id;
        $advert  = $add_adverts->addChild('advert');
        //...
        $price = get_post_meta($post_id_loop, '_price', true);
        $advert->addChild('price', $price);
        $color = get_post_meta($post_id_loop, '_color', true);
        $advert->addChild('color', $color);
        $size = get_post_meta($post_id_loop, '_size', true);
        $advert->addChild('size', $size);
        //...
    }
}
//... more code
restore_current_blog(); 

}

A summary of the data in the database tables is this:

But, the data I get are these:

How can I use get_post_meta() on each site blog after do it switch_to_blog()?

Answer 1

reading through your code ...

if ( is_wp_error($change_ok) ) {
    WriteErrorLog($change_ok->get_error_message());
}

is not applicable as $change_ok will always be true. ref https://codex.wordpress.org/Function_Reference/switch_to_blog

as the comments state it is better to use get_posts() and this may fix your issue, otherwise use $wpdb->prepare before your get_results.

also a comment is that your SQL statement will not work for the primary site.. $sql_posts = "select id, post_title, post_content from wp_".$blog_id."_posts where post_type = 'custom_add' and post_status = 'publish'";

A note is that the "wp_" table prefix can be changed in the wp-config.php file and if a site has done the "wp_" hardcoded prefix would never work the $wpdb->base_prefix can be used to make the "wp_" always fit the current site. also the wp_".$blog_id."_posts is not going to work for the main site as the table structure is different

a simple function to get the current site WP database table prefix...

// Get the database prefix
function get_blog_prefix( $blog_id=null ) {

    global $wpdb;

    if ( null === $blog_id ) {
        $blog_id = $wpdb->blogid;
    }
    $blog_id = (int) $blog_id;

    if ( defined( 'MULTISITE' ) && ( 0 == $blog_id || 1 == $blog_id ) ) {
        return $wpdb->base_prefix;
    } else {
        return $wpdb->base_prefix . $blog_id . '_';
    }
}