Sql-request to sort blogs by order in wordpress mu?

Question

I'm using the following code to generate a list of all wordpress blogs in my wordpress mu network:

    $blogs = $wpdb->get_results("SELECT * FROM " . $wpdb->blogs . " WHERE last_updated!='0000-00-00 00:00:00' AND public='1' AND spam = '0' AND deleted ='0' ORDER BY registered " . $order . " LIMIT " . $limit);

How do i do to order them alphabetically instead of by when they were registered? If you are not familliar with the database layout of wordpress, i'd be happy with a coneptual explanation! The name is not registered in the same database, however this is how the blogname is collected for output:

foreach ($blogs as $blog) {
$blog_options = "wp_" . $blog->blog_id . "_options";
$blog_name = $wpdb->get_col("SELECT option_value FROM " . $blog_options . " WHERE option_name='blogname'");
        }

Answer 1

Without knowing the WordPress database schema:

If there is a unique blog ID in both the $wpdb->blogs table and the $blog_options table, you can join between them, like so (pseudocode):

SELECT [...]
FROM $wpdb->blogs a
LEFT JOIN (SELECT id, option_value AS blogname FROM $blog_options WHERE option_name='blogname') b ON a.id = b.id
WHERE last_updated!='0000-00-00 00:00:00' AND public='1' AND spam = '0' AND deleted ='0' ORDER BY blogname [... etc ...]

In this query, you've joined each blog to the associated blog_option record with optionname='blogname'. The inner query in the JOIN returns the option_value with the alias 'blogname', so that you can reference it in your ORDER BY clause.

You'll have to work with that query a bit based on the actual database schema, but hopefully it's a start!

Answer 2

According to the wordpress documentation, It's not possible to sort wordpress-blogs on a multi-blog-network using only SQL, due to the database-structure.