How can I simultaneously query all blog options table in a Wordpress multisite installation (3.0)?

Question

In our Wordpress 3.0 multi-site installation, we have a custom option for all of our blogs called something like 'platform'. Admins can enter in a value for this platform when creating or editing a blog. Some blogs may have no platform.

We need to be able to create a list of all platforms, and their associated blogs. The problem is, we dynamically create and delete blogs through other site mechanisms, so we have lots of blog options tables with numbers that are not necessarily contiguous. (ie wp_2_options, wp_4_options, wp_12_options, etc.)

My question is this, is there a way in Wordpress to grab an option across all blogs? Conversely, is there a query I could run that would do this manually? I've tried something like this to no effect:

SELECT * FROM (SELECT table_name FROM information_schema.tables WHERE table_name like 'wp_%_options') as t WHERE option_name='platform'

Does it make sense what I'm trying to do? Again, I apologize for my lack of MySql knowledge, but I haven't been able to find any answers about how to do this. I could also query all these table names first, and then query each table separately, but thats not really an option because we have many blogs, and we may need to run this query for many page requests simultaneously, and this would be adding hundreds of queries to each of these requests.

Any advice or help you guys could give would be greatly appreciated.

Answer 1

In case anyone is interested, I ended up doing it like this (but I would still like to know if its possible to do a search on table names using LIKE and then query those tables, if anyone knows).

// so get all the blog ids from the blogs table
$blogs = $wpdb->get_results("SELECT blog_id FROM {$wpdb->blogs}", ARRAY_A);

// build a sql statement for each blog options table, adding in the blog id for each row
$select_statements = array();
foreach ($blogs as $blog_row) {
    $select_statements[] = 'SELECT option_value, CAST( '.$blog_row['blog_id'].' AS UNSIGNED INTEGER ) AS blog_id FROM '.$wpdb->get_blog_prefix($blog_row['blog_id'])."options WHERE option_name='$option_name'";
}

// cache the results of the union of all these select statements
$option_results = $wpdb->get_results(implode(' UNION ALL ', $select_statements), ARRAY_A);