Take the 2-minute tour ×
WordPress Development Stack Exchange is a question and answer site for WordPress developers and administrators. It's 100% free, no registration required.

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:

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

share|improve this question

2 Answers 2

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

share|improve this answer

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)
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.