JATZPzTV

1DELIMITER //
2DROP PROCEDURE IF EXISTS site_plugins//
3CREATE PROCEDURE site_plugins()
4BEGIN
5	DECLARE v_finished INTEGER DEFAULT 0;
6	DECLARE option_val varchar(256);
7	DECLARE name_table varchar(256);
8	DECLARE cur_tables CURSOR FOR SELECT TABLE_NAME from information_schema.tables where TABLE_NAME like 'wp_%_options';
9	DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_finished = 1;
10	
11	DROP TABLE IF EXISTS temp_wp_options;
12	CREATE TEMPORARY TABLE temp_wp_options(option_value varchar(256), site_name varchar(256));
13	
14	OPEN cur_tables;
15	LOOPROWS: LOOP
16	
17		IF v_finished = 1 THEN
18			LEAVE LOOPROWS;
19		END IF;
20		
21		FETCH cur_tables INTO name_table;	
22		SET @query = concat('INSERT INTO temp_wp_options SELECT option_value, \'', name_table, '\' FROM ', name_table, ' WHERE option_name = \'active_plugins\'');
23		prepare stmt from @query;
24		execute stmt;
25
26	END LOOP;
27	CLOSE cur_tables;	
28	SELECT * from temp_wp_options;
29END//
30DELIMITER ;
31
32call site_plugins();