0

Stuck with some mysql for my wordpress site. All within 1 DB.

I am attempting to update wp_options table from wp_2_options (Multisite).

They both have option_name (unique_key) and option_value columns

It needs to update the option_value in the wp_options table if the option_name already exists in wp_2_options table

AND Insert the row from wp_2_options if the option_name doesnt exist.

So how do I UPDATE and `INSERT using the UNIQUE KEY value instead of the PRIMARY KEY

How do I do this?

Structure is the same for both of them

wp_options ( option_id bigint(20) unsigned NOT NULL AUTO_INCREMENT, option_name varchar(64) NOT NULL DEFAULT '', option_value longtext NOT NULL, autoload varchar(20) NOT NULL DEFAULT 'yes', PRIMARY KEY (option_id), UNIQUE KEY option_name (option_name) ) ENGINE=InnoDB AUTO_INCREMENT=1402 DEFAULT CHARSET=utf8;

Thanks in advance


UPDATE:

Ended up using this INSERT then UPDATE. This worked for me. Will just use this through PHP now.

-- Adding completely new rows from wp_2_options into wp_options INSERT IGNORE INTO wp_options SELECT * FROM wp_2_options WHERE option_name NOT IN (SELECT option_name FROM wp_options);

-- Updating the option_name field in wp_options, if that got changed in wp_2_options UPDATE wp_options JOIN wp_2_options ON wp_options.option_name = wp_2_options.option_name SET wp_options.option_value = wp_2_options.option_value;

0

Use PHP and the WordPress API. Place the following in copy-options.php and FTP it to wp-content/plugins, then activate it from the main site:

<?php

/**
 * Plugin Name: Copy Options
 * Plugin URI:  http://wordpress.stackexchange.com/q/186092/1685
 * Description: Copy options from site 2 to site 1.
 * Version:     0.1
 * Author:      TheDeadMedic
 * Author URI:  http://wordpress.stackexchange.com/users/1685/thedeadmedic
 */

function wpse_186092_copy_options() {
    if ( 2 !== $blog_id = get_current_blog_id() )
        switch_to_blog( 2 );

    // Get all options from blog 2
    $options = wp_load_alloptions();

    // Switch to main blog
    switch_to_blog( 1 );

    foreach ( $options as $name => $value )
        update_option( $name, $value ); // Will update option if exists, insert if not

    // Switch back to current blog
    switch_to_blog( $blog_id );     
}

register_activation_hook( __file__, 'wpse_186092_copy_options' );

Backup first. This code is not tested and comes without warranty.

  • Nice! Very handy. Unfortunately I don't have access to WP functions at this point only the database. Working on a deployment solution – Cptn May 2 '15 at 22:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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