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.

I need to find a random blog across my multisite network that has at least one post published of a certain post_type, and return its blogid as a variable so I can use it in a switch_to_blog() call.

I need to figure out how to build the $randomblog variable in this scenario (I know the current SQL query is bogus):

// Find a random blog that has at least one post of post_type published and return its blogid as $randomblog
$randomblog = $wpdb->get_results($wpdb->prepare("SELECT (1 random blogid) FROM $wpdb->blogs WHERE at least 1 of post_type = 'special' exists "));

// Switch to the random blog
    switch_to_blog($randomblog);

        (do some fun stuff)

    // Switch back to the current blog
    restore_current_blog();

I'm not sure, but I believe this bit of code I found could possibly be altered to do what I need. Just not sure how:

global $wpdb;
global $table_prefix;

// get an array of the table names that our posts will be in
// we do this by first getting all of our blog ids and then forming the name of the 
// table and putting it into an array
$rows = $wpdb->get_results( "SELECT blog_id from $wpdb->blogs WHERE
    public = '1' AND archived = '0' AND mature = '0' AND spam = '0' AND deleted = '0';" );
if ( $rows ) :
$blogPostTableNames = array();
foreach ( $rows as $row ) :
    $blogPostTableNames[$row->blog_id] = $wpdb->get_blog_prefix( $row->blog_id ) . 'posts';
endforeach;

// now we need to do a query to get all the posts from all our blogs
// ordered by the number of comments and with limits applied
if ( count( $blogPostTableNames ) > 0 ) :
    $query = '';
    $i = 0;
    foreach ( $blogPostTableNames as $blogId => $tableName ) :
        if ( $i > 0 ) :
            $query.= ' UNION ';
        endif;
        $query.= " SELECT ID, post_type, $blogId as `blog_id` FROM $tableName WHERE post_type = 'prompt' AND post_status = 'publish' ";
        $i++;
    endforeach;

    $rows = $wpdb->get_results( $query );

    // now we need to get each of our posts into an array and return them
    if ( $rows ) :
        $posts = array();
        foreach ( $rows as $row ) :
            $posts[] = get_blog_post( $row->blog_id, $row->ID );
        endforeach;
        print_r($posts);
        return $posts;
        echo ('<br /><br />');
    endif;
endif;
endif;
share|improve this question
1  
+1 for a funny question. Question to you: why would you need a random blog? –  kaiser Aug 1 '12 at 2:13
2  
Thanks. I'm using it to display a post_title from a random user in another user's post-new page in admin. It's bizarre, but I'm doing all sorts of crazy stuff in this project! –  quirk Aug 1 '12 at 2:39
add comment

1 Answer 1

An example that displays an admin notice listing a randomized array with all blog IDs, the result of a get_posts( array( 'numberposts' => 1 ) ) and marking the first one which get_posts result is different from zero.

Result

admin notice listing all blogs and number of posts found

After refreshing:
admin notice after refreshing

Code

add_action( 'admin_notices', 'wpse_60401_print_random_blog' );

function wpse_60401_print_random_blog()
{ 
    global $wpdb;

    $rows = $wpdb->get_results( $wpdb->prepare( "SELECT blog_id from $wpdb->blogs WHERE public = '1' AND archived = '0' AND mature = '0' AND spam = '0' AND deleted = '0';" ) );

    if( !$rows )
    {
        echo '<div class="error">No blogs found (!)</div>';
    }
    else
    {
        shuffle( $rows );
        $counter = 0;
        echo '<div class="error">';
        foreach ( $rows as $row ) 
        {
            switch_to_blog( $row->blog_id );
            $get_posts = get_posts( array( 'numberposts' => -1 ) );
            echo 'Blog ID: ' . $row->blog_id . ' - Number posts: ' . count($get_posts) . '<br />';

            if( count($get_posts) != 0 && $counter == 0 ) 
            {
                echo 'First blog with a post: ' . $row->blog_id . '<br />';
                $counter++;
            }
                    restore_current_blog();
        }
        echo '</div>';
    }

}

Code in form an usable function

function get_random_blog()
{ 
    global $wpdb;

    $rows = $wpdb->get_results( $wpdb->prepare( "SELECT blog_id from $wpdb->blogs WHERE public = '1' AND archived = '0' AND mature = '0' AND spam = '0' AND deleted = '0';" ) );

    if( !$rows )
    {
        return 0;
    }
    else
    {
        shuffle( $rows );
        foreach ( $rows as $row ) 
        {
            switch_to_blog( $row->blog_id );
            $get_posts = get_posts( array( 'numberposts' => -1 ) );

            if( count($get_posts) != 0 ) 
            {
                restore_current_blog();
                return $row->blog_id;               
            }
        }
    }
    restore_current_blog();
    return 0;
}
share|improve this answer
add comment

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.