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 found this really cool function to list all the blogs on a multisite network install.

Because the original multisite list blogs function was depreciated :/


So I am using this function below which is great because it is so flexible.

But the only thing I can't seem to work out is how to re-order the get_results query. Can any one please help me understand how to re-order this query by blog id, instead of the current outputted alphabetical order.


function projects_menu($link_self = true)
{
    global $wpdb;
    echo '<ul class="nav nav-list">';

    $blogs = $wpdb->get_results("
        SELECT blog_id
        FROM {$wpdb->blogs}
        WHERE site_id = '{$wpdb->siteid}'
        AND spam = '0'
        AND deleted = '0'
        AND archived = '0'
        AND blog_id != 1
    ");
    $sites = array();
    foreach ($blogs as $blog) {
        $sites[$blog->blog_id] = get_blog_option($blog->blog_id, 'blogname');
    }
    natsort($sites);
    foreach ($sites as $blog_id => $blog_title) {
        projects_menu_entry($blog_id, $blog_title, $link_self);
    }

    echo '</ul>';
}


Source of this functions https://gist.github.com/davejamesmiller/1966341


Thanks in advance.


See this should work but does not...

$blogs = $wpdb->get_results("
        SELECT blog_id
        FROM {$wpdb->blogs}
        WHERE site_id = '{$wpdb->siteid}'
        AND spam = '0'
        AND deleted = '0'
        AND archived = '0'
        AND blog_id != 1
        ORDER BY blog_id
    ");

// EVENTS SITEMAP
function projects_menu_entry($id, $title, $link_self)
{
    global $blog_id;
    if ($link_self || $id != $blog_id) {
        if ($id == $blog_id) {
            echo '<li class="active">';
        } else {
            echo '<li>';
        }
        $url = get_home_url($id);
        if (substr($url, -1) != '/') {
            // Note: I added a "/" to the end of the URL because WordPress
            // wasn't doing that automatically in v3.0.4. YMMV.
            $url .= '/';
        }
        echo '<a class="notranslate" href="' . $url . '"><i class="icon-folder-close"></i> ' . $title . '</a>';
        echo '</li>';
    }
}
function projects_menu($link_self = true)
{
    global $wpdb;
    echo '<ul class="nav nav-list">';
    echo '<li class="nav-header">Admin sitemap</li>';

    $query = "
    SELECT blog_id
    FROM $wpdb->blogs
    WHERE site_id = %d
       AND public   = '1'
       AND archived = '0'
       AND mature   = '0'
       AND spam     = '0'
       AND deleted  = '0'
       AND blog_id != '1'
    ORDER BY blog_id ASC";
    $blogs = $wpdb->get_col( $wpdb->prepare( $query, $wpdb->siteid ) );

    $sites = array();
    foreach ($blogs as $blog) {
        $sites[$blog->blog_id] = get_blog_option($blog->blog_id, 'blogname');
    }

    foreach ($sites as $blog_id => $blog_title) {
        projects_menu_entry($blog_id, $blog_title, $link_self);
    }

    projects_menu_entry(1, 'Home', $link_self);

    echo '</ul>';

}

Please see above my function 'trying' to list all blogs in on my network site and show which is currently being viewed by having a active class.

But the above code is only returning one site. And the home link.

share|improve this question
    
Just add ORDER BY blog_id to the SQL query. –  toscho Apr 24 '13 at 10:13
    
I just tried this and its not working... $blogs = $wpdb->get_results(" SELECT blog_id FROM {$wpdb->blogs} WHERE site_id = '{$wpdb->siteid}' ORDER BY blog_id AND spam = '0' AND deleted = '0' AND archived = '0' AND blog_id != 1 "); –  Joshc Apr 24 '13 at 10:46
    
Add it to the end. –  toscho Apr 24 '13 at 10:48
    
Ok I tried this originally and still not work see my code above... Just is still alphabetical. I can't figure it out. I wonder why I got -1 on this question. –  Joshc Apr 24 '13 at 11:52
1  
@toscho - it's valid wordpress based question - why you close it? –  Joshc Apr 24 '13 at 13:00
show 4 more comments

1 Answer

The following query works. Note the quotes in AND blog_id != '1'

global $wpdb;
$query = "
SELECT blog_id
FROM $wpdb->blogs
WHERE site_id = %d
    AND public   = '1'
    AND archived = '0'
    AND mature   = '0'
    AND spam     = '0'
    AND deleted  = '0'
    AND blog_id != '1'
ORDER BY blog_id ASC";
$blogs = $wpdb->get_col( $wpdb->prepare( $query, $wpdb->siteid) );

Result in a multi-site with three sites:

blogs = array (
  0 => '2',
  1 => '3',
)
share|improve this answer
    
Thank you for your help. It only seems to return the one blog? Which is no6. And I have nine. They are definitely public too. Hmmm this is strange and you tested it to :/ –  Joshc Apr 28 '13 at 20:07
    
@Joshc Disable all plugins and switch to Twenty Twelve. Then test just this snippet again. –  toscho Apr 28 '13 at 20:39
    
See my updated question with your code. I think your just query may work with my nav list fuction. Sorry for not posting my full code orginally, I thought that just a $blogs query change would fix. But I think there maybe code problems as your query works fine on its own. –  Joshc Apr 29 '13 at 11:06
    
Thank you for your help. I am going to try and build a new fucntion but if you see a quick fix my existing function please let me know. Thanks –  Joshc Apr 29 '13 at 11:08
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.