Help running a MySQL query to update all wp_#_options tables in a Multisite install

1 Views

I’d like to run a query through my WordPress database that updates the blog_public values on all individual sites. I’m stuck on how to have the statement look for wp_2_options, wp_3_options, etc.

I can do it individually with something like this:

Read More
UPDATE wp_10_options
SET option_value = REPLACE(option_value, '1', '0')

But am stuck on how to run a query that would go across all the tables. Any help?

Thanks in advance 🙂

Post Views: 1

Related posts

Leave a Reply

2 comments

  1. Got a partial solution, but requires VPS to run it as shared hosting has a restriction on what you can do with a cursor.

    Anyway,

    DROP PROCEDURE IF EXISTS `update_all_options`;
DELIMITER //
CREATE PROCEDURE update_all_options(
IN db varchar(255),
IN theoption varchar(255),
IN set_val VARCHAR(255)
)
BEGIN
DECLARE table_val VARCHAR(255);

-- Declare variables used just for cursor and loop control
DECLARE no_more_rows BOOLEAN;

-- Declare the cursor
DECLARE options_cur CURSOR FOR SELECT table_name FROM information_schema.tables WHERE TABLE_SCHEMA = db AND TABLE_NAME LIKE 'wp_%options';
DECLARE CONTINUE HANDLER FOR NOT FOUND SET no_more_rows = TRUE;

OPEN options_cur;

the_loop: LOOP

FETCH options_cur
INTO table_val;

IF no_more_rows THEN
CLOSE options_cur;
LEAVE the_loop;
END IF;

SET @qry = CONCAT('UPDATE ', table_val, ' SET option_value = "', set_val, '" WHERE option_name = "', theoption, '"');
PREPARE sqlstatement FROM @qry;
EXECUTE sqlstatement;
DEALLOCATE PREPARE sqlstatement;

END LOOP the_loop;

END //
DELIMITER ;

    You run it in your sql query window to create the procedure. It’ll then stick around until you need it.
    To use it do:

    CALL update_all_options('wpglobal', 'blog_public', '1');

    To change all options with the name blog_public in the wpglobal database to 1. PHP would probably be much much easier, eh? Or so say my colleagues who came up with the above 🙂

  2. There are probably plenty of approaches to this from database perspective… But if this only needs to be done once and sticking to WP APIs:

    • make simple plugin out of it
    • place you replacement into activation routine
    • network-activate (only I don’t remember if you still need to visit dashboards for sites for activation to trigger or that got fixed)