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 🙂