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);

Answer 2

If you want to query directly MySQL database, you can create a procedure and use it:

use wordpress;
Drop Procedure IF EXISTS wordpress.MyProcedure;
DELIMITER | ;
CREATE PROCEDURE MyProcedure (param1 VARCHAR(30))
BEGIN
        DECLARE tbname CHAR(50);
        DECLARE endfetch INT DEFAULT 0;
        DECLARE cur1 CURSOR FOR 
        SELECT table_name FROM information_schema.tables WHERE table_schema='wordpress' and table_name like '%options';
        DECLARE CONTINUE HANDLER FOR SQLSTATE '02000'
                SET endfetch = 1;
        OPEN cur1;
        FETCH cur1 INTO tbname;
        fetchloop: WHILE NOT endfetch DO
            SELECT tbname ; 
            SET @opt = param1;
            SET @table_name = tbname;
            SET @sql_text = concat('SELECT option_value FROM ',@table_name,' WHERE option_name=''',@opt,'''');
            PREPARE stmt FROM @sql_text;
            EXECUTE stmt;
            DEALLOCATE PREPARE stmt;

            FETCH cur1 INTO tbname;
        END WHILE fetchloop;
END
|
DELIMITER ; |


CALL MyProcedure('siteurl');