如何在Wordpress多站点安装（3.0）中同时查询所有博客选项表？

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.

在我们的Wordpress 3.0多站点安装中，我们为所有博客提供了一个自定义选项，称为“平台”。管理员可以在创建或编辑博客时输入此平台的值。有些博客可能没有平台。

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

我们需要能够创建所有平台及其相关博客的列表。问题是，我们通过其他站点机制动态创建和删除博客，因此我们有许多博客选项表，其中的数字不一定是连续的。（即wp_2_options，wp_4_options，wp_12_options等）

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:

我的问题是，在Wordpress中有没有办法在所有博客中获取选项？相反，是否有可以运行的查询可以手动执行此操作？我尝试过这样的事情没有效果：

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

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.

我想做什么才有意义？我再次为我缺乏MySql知识而道歉，但我无法找到任何关于如何做到这一点的答案。我还可以先查询所有这些表名，然后分别查询每个表，但这不是一个选项，因为我们有很多博客，我们可能需要同时运行此查询以查找许多页面请求，这将增加数百个查询每个请求。

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

任何建议或帮助你们都会非常感激。

2 个解决方案

#1

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

如果要直接查询MySQL数据库，可以创建一个过程并使用它：

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

#2

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

如果有人感兴趣，我最终会这样做（但我仍然想知道是否有可能使用LIKE搜索表名，然后查询这些表，如果有人知道的话）。

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

#1