Skip to content

Instantly share code, notes, and snippets.

@jaseclamp
Created March 4, 2015 06:32
Show Gist options
  • Save jaseclamp/2dcda64e8912f7361d94 to your computer and use it in GitHub Desktop.
Save jaseclamp/2dcda64e8912f7361d94 to your computer and use it in GitHub Desktop.
<?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"]].')');
}
?>
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment