transfer-entries.php

<?php 
 
//this script duplicates channel entries with category assignments from one MSM site to another. 
//set the from/to site id below. 
//it assumes you've already duplicated your channels, channel fields, and categories between sites perfectly preserving url_titles for all. (see note on categories at end)

//run in staging before production! (and backup!)

//using nsm config bootstrap http://ee-garage.com/nsm-config-bootstrap 
//otherwise manually set your db connection details following 
include('../../config.php'); 

// Create connection
$conn = new mysqli( $env_db_config['hostname'], $env_db_config['username'], $env_db_config['password'] );

// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
} 

$from_site_id = 1; 
$to_site_id = 3; 

$conn->select_db($env_db_config['database']);

//lets just wipe destination site stuff in case we're rerunning this script
$conn->query('delete from exp_channel_titles where site_id = '.$to_site_id);
$conn->query('delete from exp_channel_data where site_id = '.$to_site_id);
//TODO: after deleting those get the highest entry_id from channel_titles and then delete everything above that from category_posts

//get channel old / new ids
$channels = $conn->query('select * from exp_channels where site_id = '.$from_site_id.' or site_id = '.$to_site_id.' order by channel_name,site_id asc');
while( $channel = $channels->fetch_assoc() ) $_channels[$channel['channel_name']][$channel['site_id']] = $channel['channel_id'];

//clean
foreach($_channels as $channel_name => $channel_from_to_array)
{
	if(count($channel_from_to_array)<2) { unset($_channels[$channel_name]); continue; } //if we have an array with only a from or only a to then it means that channel wasn't duplicated correctly - so we skip it.
	//I don't think we need this id conversion array as conversion id values are saved in channel_titles and reused in channel_data
	//$new_channel_id[$channel_from_to_array[$from_site_id]] = $channel_from_to_array[$to_site_id];
}
//get field old / new ids
$fields = $conn->query('select * from exp_channel_fields where site_id = '.$from_site_id.' or site_id = '.$to_site_id.' order by field_name,site_id asc');
while( $field = $fields->fetch_assoc() ) $_fields[$field['field_name']][$field['site_id']] = $field['field_id'];

$new_channel_data_columns = ''; $old_channel_data_columns = '';

//build select string for old data and insert string for new data 
foreach($_fields as $field_name => $field_from_to_array)
{
	if(count($field_from_to_array)<2) { unset($_fields[$field_name]); continue; } //if we have an array with only a from or only a to then it means that field wasn't duplicated correctly - so we skip it.
	$old_channel_data_columns .= '`field_id_'.$field_from_to_array[$from_site_id].'`,`field_ft_'.$field_from_to_array[$from_site_id].'`,';
	$new_channel_data_columns .= '`field_id_'.$field_from_to_array[$to_site_id].'`,`field_ft_'.$field_from_to_array[$to_site_id].'`,';
}

//now trim last comma off the strings
$old_channel_data_columns = rtrim($old_channel_data_columns,',');
$new_channel_data_columns = rtrim($new_channel_data_columns,',');

//get channel title column headings - we could hard code these but making this upgrade safe! 
$_columns = $conn->query('show columns from exp_channel_titles'); 
while( $column = $_columns->fetch_assoc() ) 
{
	if( !in_array( $column['Field'] , array( 'entry_id' ) ) ) $new_columns[] = $column['Field']; //auto increment
	if( !in_array( $column['Field'] , array( 'entry_id','channel_id','site_id' ) ) ) $old_columns[] = $column['Field']; //we set chan and site. 
}
$new_channel_titles_columns = '`'.implode('`,`',$new_columns).'`'; //make into mysql strings
$old_channel_titles_columns =  '`'.implode('`,`',$old_columns).'`';

//dupe entries for each channel
foreach($_channels as $channel_name => $channel_from_to_array)
{
	$conn->query('insert into exp_channel_titles ('.$new_channel_titles_columns.') select '.$to_site_id.','.$channel_from_to_array[$to_site_id].','.$old_channel_titles_columns.' from exp_channel_titles where channel_id = '.$channel_from_to_array[$from_site_id]); 
}

//get channel entry from/to id's
$entries = $conn->query('select * from exp_channel_titles where site_id = '.$from_site_id.' or site_id = '.$to_site_id.' order by channel_id,site_id asc' );
while( $entry = $entries->fetch_assoc() )
{
	//store id from/to
	$_entries[$entry['url_title']]['id_from_to'][$entry['site_id']] = $entry['entry_id'];
	//store channel from/to
	$_entries[$entry['url_title']]['channel_from_to'][$entry['site_id']] = $entry['channel_id'];
}

//echo "<pre>"; var_dump($_entries); die;

foreach($_entries as $url_title => $entry_from_to_array)
{
	//we need two of each. 
	if(count($entry_from_to_array['id_from_to'])<2 OR count($entry_from_to_array['channel_from_to'])<2) { unset($_entries[$url_title]); continue; }
	$conn->query('insert into exp_channel_data (`entry_id`,`site_id`,`channel_id`,'.$new_channel_data_columns.') select '.$entry_from_to_array['id_from_to'][$to_site_id].','.$to_site_id.','.$entry_from_to_array['channel_from_to'][$to_site_id].','.$old_channel_data_columns.' from exp_channel_data where entry_id = '.$entry_from_to_array['id_from_to'][$from_site_id]); 
	$new_entry_id[ $entry_from_to_array['id_from_to'][$from_site_id] ] = $entry_from_to_array['id_from_to'][$to_site_id]; //we'll need this for categories 
}

//note if you had any grid fields... they will be empty and you'll have to manually populate. 
	
//now for categories!!! note that this depends on the fact that you NEVER had duplicate category url titles in either site but you did duplicate them across perfectly matching... 

//get channel old / new ids
$categories = $conn->query('select * from exp_categories where site_id = '.$from_site_id.' or site_id = '.$to_site_id.' order by cat_url_title,site_id asc');
while( $category = $categories->fetch_assoc() ) $_categories[$category['cat_url_title']][$category['site_id']] = $category['cat_id'];

foreach($_categories as $cat_url_title => $category_from_to_array)
{
	if(count($category_from_to_array)<2) { unset($_categories[$cat_url_title]); continue; } //if we have an array with only a from or only a to then it means that category wasn't duplicated correctly - so we skip it.
	//map old to new ids
	$new_category_id[$category_from_to_array[$from_site_id]] = $category_from_to_array[$to_site_id];
}

$catmaps = $conn->query('select * from exp_category_posts order by entry_id asc' );
while( $catmap = $catmaps->fetch_assoc() )
{
	$conn->query('insert into exp_category_posts values ('.$new_entry_id[$catmap["entry_id"]].','.$new_category_id[$catmap["cat_id"]].')');
}






?>