Hello All,
I have an issue where I need to delete a specific row in a table for a database and this would need to occur in a multisite setup and I was wondering if there was a way to do this easily.
The row is found in: Main Wordpress Database > wp_1001_options > wp-piwik_settings
I need to delete this for about 1000 blogs. so each one would be like wp_800_options > wp-piwik_settings, wp_745_options > wp-piwik_settings, etc etc, for each blog's database
Is there a way I can query and delete the wp-piwik_settings row with a single command in MYSQL for all the blogs?
Hello All,
I have an issue where I need to delete a specific row in a table for a database and this would need to occur in a multisite setup and I was wondering if there was a way to do this easily.
The row is found in: Main Wordpress Database > wp_1001_options > wp-piwik_settings
I need to delete this for about 1000 blogs. so each one would be like wp_800_options > wp-piwik_settings, wp_745_options > wp-piwik_settings, etc etc, for each blog's database
Is there a way I can query and delete the wp-piwik_settings row with a single command in MYSQL for all the blogs?
I just looked at that code, how would I use it and where would I put it? The code looks like something that would generate a list and then I just need to delete them.
Here is the code they have in the example:
// 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);
Here is what I think I need to use:
// 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 wp-piwik_settings, CAST( '.$blog_row['blog_id'].' AS UNSIGNED INTEGER ) AS blog_id FROM '.$wpdb->get_blog_prefix($blog_row['blog_id'])."options WHERE wp-piwik_settings='$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);
I would have no idea how or where I would put this code. Any help would be appreciated.
I'd definitely recommend doing this on a test server first. Make a backup, and if you can test the backup somewhere else with this query.
As to the actual query you've got above, that's not an SQL Query. It looks like a bit of php. I can't really test this for you and it really looks incomplete to me. You can try dropping it a new file name it filename.php and drop it in mu-plugins, but I still doubt that's it's original intent. Running an SQL statement would be a much better route anyway.
To run a query through phpMyAdmin just login and click on the database in the left-hand menu. Then select the SQL tab form the top menu. This is where you enter the query statement and hit 'go'.
If you don't have access to phpMyAdmin there is also software to help with this. I use Sequel Pro on my Mac, and the process of running the statement is largely the same.
Definitely run it through a backup copy first and make another backup before running it live (ya never know).
Hello Masonjames,
My problem is that I need to know the sql statement to run. I have MySQL tools that I could run, but I don't know what statement to enter.
I want to delete a specific row from the wp_options table named "wp-piwik_settings". Since this is a Multisite setup there are over 1000 different wp_blog ID #_options tables that are not sequential.
Responses (5)
WPMU DEV Fanatic — 31st May 2011 #
Hi kennibc,
The only way I know of to do that is by coding a relatively simple script to execute the query on all blogs. There's an example of this given in the following:
http://stackoverflow.com/questions/3817540/how-can-i-simultaneously-query-all-blog-options-table-in-a-wordpress-multisite-in
That said, I'll ask a few of the brainier folks over here if they know of another way, perhaps using pure SQL.
Cheers,
David
Member — 1st June 2011 #
Thanks. I'd appreciate any help in this matter.
Member — 1st June 2011 #
I just looked at that code, how would I use it and where would I put it? The code looks like something that would generate a list and then I just need to delete them.
Here is the code they have in the example:
Here is what I think I need to use:
I would have no idea how or where I would put this code. Any help would be appreciated.
Sales & Support Lead — 1st June 2011 #
Hiya kennibc,
I'd definitely recommend doing this on a test server first. Make a backup, and if you can test the backup somewhere else with this query.
As to the actual query you've got above, that's not an SQL Query. It looks like a bit of php. I can't really test this for you and it really looks incomplete to me. You can try dropping it a new file name it filename.php and drop it in mu-plugins, but I still doubt that's it's original intent. Running an SQL statement would be a much better route anyway.
To run a query through phpMyAdmin just login and click on the database in the left-hand menu. Then select the SQL tab form the top menu. This is where you enter the query statement and hit 'go'.
If you don't have access to phpMyAdmin there is also software to help with this. I use Sequel Pro on my Mac, and the process of running the statement is largely the same.
Definitely run it through a backup copy first and make another backup before running it live (ya never know).
Member — 2nd June 2011 #
Hello Masonjames,
My problem is that I need to know the sql statement to run. I have MySQL tools that I could run, but I don't know what statement to enter.
I want to delete a specific row from the wp_options table named "wp-piwik_settings". Since this is a Multisite setup there are over 1000 different wp_blog ID #_options tables that are not sequential.
Do you have any suggestions for a sql statement?
Become a member