Copy Magento Category

Since first laying eyes on Magento, the open source eCommerce software, I been impressed by it’s power, flexibility and rigid code structure. But despite Magento’s almost endless list of strengths, it also has some significant weaknesses, that often leave me frustrated and confused. One such weakness is the apparent lack of any functionality in regards to copying products and categories.

I had created a category, with about 10 child categories. Now I just wanted to duplicate that category 5 times, rather than creating another 4 from scratch, each with 10 children (4×10=40 categories!). Rather than spend the next couple of hours adding categories, I knocked together this quick PHP script. Simply pass in the ID of the category you’d like to duplicate, and it will create a copy of the category, along with all of it’s child categories, as deep as they go!

Ideally, this would be a proper Magento module, but I’m just starting out with Magento. Give me 6 months, eh?!

To use the script, simply copy it into a file such as copycat.php in your Magento root directory and call it like so:

http://www.example.com/copycat.php?id=[CATEGORY ID TO COPY]

<?php
 
 
if(!is_numeric($_GET['id']))die('Please specify a category ID');
 
$catId = $_GET['id'];
 
$xml = simplexml_load_file('app/etc/local.xml');
$host = $xml->global->resources->default_setup->connection->host;
$username = $xml->global->resources->default_setup->connection->username;
$password = $xml->global->resources->default_setup->connection->password;
$dbname = $xml->global->resources->default_setup->connection->dbname; 
$res = mysql_pconnect($host, $username, $password);   
mysql_select_db($dbname);
 
 
 
 
$catsDone = 0;
duplicate_entity($catId);
echo $catsDone . ' Categories duplicated.';
 
function duplicate_entity($id, $parent_id = null){
	global $catsDone;
 
 
	// Grab category to copy
	$sql = "SELECT * FROM catalog_category_entity WHERE entity_id = " . $id;
	$query_entity = mysql_query($sql);
 
	$entity = mysql_fetch_object($query_entity);
 
 
	if(!$parent_id)$parent_id = $entity->parent_id;
 
 
 
 
	mysql_query("INSERT INTO catalog_category_entity (entity_type_id, attribute_set_id, parent_id, created_at, updated_at, path, position, level, children_count)
						VALUES ({$entity->entity_type_id}, {$entity->attribute_set_id}, {$parent_id}, NOW(), NOW(), '', {$entity->position}, {$entity->level}, {$entity->children_count})");
	$newEntityId = mysql_insert_id();
 
	$query = mysql_query("SELECT path FROM catalog_category_entity WHERE entity_id = " . $parent_id);
	$parent = mysql_fetch_object($query);
	$path = $parent->path . '/' . $newEntityId;
 
	mysql_query("UPDATE catalog_category_entity SET path='". $path."' WHERE entity_id=". $newEntityId);
 
 
	foreach(array('datetime', 'decimal', 'int', 'text', 'varchar') as $dataType){
		$sql = "SELECT * FROM catalog_category_entity_".$dataType."
				WHERE entity_id=" . $entity->entity_id;
				//die($sql);
		$query = mysql_query($sql);
		while ($value = mysql_fetch_object($query)){
			mysql_query("INSERT INTO catalog_category_entity_".$dataType." (entity_type_id, attribute_id, store_id, entity_id, value)
							VALUES ({$value->entity_type_id}, {$value->attribute_id}, {$value->store_id}, {$newEntityId}, '{$value->value}')");
		}
	}
 
 
	$sql = "SELECT entity_id FROM catalog_category_entity WHERE parent_id = " . $id;
	$query = mysql_query($sql);
 
	while ($entity = mysql_fetch_object($query)){
		duplicate_entity($entity->entity_id, $newEntityId);
	}
	$catsDone++;
}
 
 
 
 
?>

About the author

Hayden

I am a freelance web developer with a passion for making awesome stuff.

E: hayden@haydenkibble.com
T: 07747 506 967

48 Comments

Leave a comment
  • Thanks a lot for these my friend; not only was it helpfull in a direct sense, but also gave my an insight into Magento´s Inner Workings! Thanks!

  • Hi!
    Works great, but is there a possibility that the products in the categories also can be copied?

    Hope so…

  • You are awsome.. I was looking to write a mysql script to duplicate all the categories… than i found this. Works great!

  • ei… its six months later… where is the extension? lol (<—–Joke)

    thanks for your work. Im trying right now

  • Nice piece of work – I thought I’d add that I had difficulties getting it to work but eventually traced it to the fact that cpanel’s installatron had prepended “mage_” on to all of my tables during the install. Simply remedied by doing a [search replace] in the code and prepending whatever installatron adds onto “catalog_category”.
    So if you’ve used Installatron or Fantastico or anything this is something to be aware of.

  • I do not think that this is working with 1.6.2 It says it was successful, but I cannot find the categories anywhere! lol

    I hope you update this one day!

  • Hi,

    I am getting the following error when trying this in Magento CE 1.6.1

    Warning: mysql_fetch_object(): supplied argument is not a valid MySQL result resource in /home6/tersdire/public_html/category_copy.php on line 31

    I called the file category_copy.php

    Any ideas?

  • Many thanks for posting this solution.

    Tested this on Magento 1.7.0.1 and it need’s a small fix for certain NULL value’s,

    @line;
    [pre]
    while ($value = mysql_fetch_object($query)){
    mysql_query(“INSERT INTO catalog_category_entity_”.$dataType.” (entity_type_id, attribute_id, store_id, entity_id, value)
    VALUES ({$value->entity_type_id}, {$value->attribute_id}, {$value->store_id}, {$newEntityId}, ‘{$value->value}’)”);
    }

    [/pre]

    @change it to;
    [pre]
    if (is_null($value->value)) {
    mysql_query(“INSERT INTO catalog_category_entity_”.$dataType.” (entity_type_id, attribute_id, store_id, entity_id, value)
    VALUES ({$value->entity_type_id}, {$value->attribute_id}, {$value->store_id}, {$newEntityId}, NULL)”);
    } else {
    mysql_query(“INSERT INTO catalog_category_entity_”.$dataType.” (entity_type_id, attribute_id, store_id, entity_id, value)
    VALUES ({$value->entity_type_id}, {$value->attribute_id}, {$value->store_id}, {$newEntityId}, ‘{$value->value}’)”);
    }
    [/pre]

    Also it seems the children count doesn’t get updated, for that to be fixed too, change the following;

    @line
    [pre]
    duplicate_entity($catId);
    echo $catsDone . ‘ Categories duplicated.’;
    [/pre]

    @change to
    [pre]
    duplicate_entity($catId);

    // Fix the children count for all (sub)categories
    $sql = “CREATE TABLE catalog_category_entity_tmp LIKE catalog_category_entity”;
    mysql_query($sql);
    $sql = “INSERT INTO catalog_category_entity_tmp SELECT * FROM catalog_category_entity”;
    mysql_query($sql);
    $sql = “UPDATE catalog_category_entity cce
    SET children_count =
    (
    SELECT count(cce2.entity_id) – 1 as children_county
    FROM catalog_category_entity_tmp cce2
    WHERE PATH LIKE CONCAT(cce.path,’%’)
    )”;
    mysql_query($sql);
    $sql = “DROP TABLE catalog_category_entity_tmp”;
    mysql_query($sql);

    echo $catsDone . ‘ Categories duplicated.’;
    [/pre]

    It works like a chamr here, now just to figure out how to copy product relations too 😉

  • good work actually, but don’t working when magento tables are using some prefix, so have to use a variable to get prefix of tables and then add it in front of every table. adding here so just someone can get help

  • Excellent work! I confirm this to work on the recent Magento 1.7.0.2. Just care a bit if you have a prefix set for your tables that you put it before the table names.
    This code saved me 70$.
    <3

  • Duplicate categories in magento 1.7.0.2 with this script works really like a charm. And it just takes a minute to install. And it doesnt mess all your tables like the Amasty extension. Great work!!!!!! There is no need for an extension.

  • Wow thanks! This will save a ton of time… One thing I see when the new categories are created in the display settings of the category “Layered Navigation Price Step” gets set to 0.0000 any idea how to set to default “Use Config Settings” Thanks again!

  • I just used UPDATE catalog_category_entity_decimal SET value = NULL WHERE value = 0.0000, Thanks again for the script!

  • To Copy Product too, you can use:
    global->resources->default_setup->connection->host;
    $username = $xml->global->resources->default_setup->connection->username;
    $password = $xml->global->resources->default_setup->connection->password;
    $dbname = $xml->global->resources->default_setup->connection->dbname;
    $res = mysql_pconnect($host, $username, $password);
    mysql_select_db($dbname);
    $catsDone = 0;
    duplicate_entity($catId);
    echo $catsDone . ‘ Categories duplicated.’;

    function duplicate_entity($id, $parent_id = null){
    global $catsDone;

    mysql_query(“SET NAMES ‘utf8′”);
    // Grab category to copy
    $sql = “SELECT * FROM catalog_category_entity WHERE entity_id = ” . $id;
    $query_entity = mysql_query($sql);

    $entity = mysql_fetch_object($query_entity);

    if(!$parent_id)$parent_id = $entity->parent_id;

    mysql_query(“INSERT INTO catalog_category_entity (entity_type_id, attribute_set_id, parent_id, created_at, updated_at, path, position, level, children_count)
    VALUES ({$entity->entity_type_id}, {$entity->attribute_set_id}, {$parent_id}, NOW(), NOW(), ”, {$entity->position}, {$entity->level}, {$entity->children_count})”);
    $newEntityId = mysql_insert_id();

    $query = mysql_query(“SELECT path FROM catalog_category_entity WHERE entity_id = ” . $parent_id);
    $parent = mysql_fetch_object($query);
    $path = $parent->path . ‘/’ . $newEntityId;

    mysql_query(“UPDATE catalog_category_entity SET path='”. $path.”‘ WHERE entity_id=”. $newEntityId);

    foreach(array(‘datetime’, ‘decimal’, ‘int’, ‘text’, ‘varchar’) as $dataType){
    $sql = “SELECT * FROM catalog_category_entity_”.$dataType.”
    WHERE entity_id=” . $entity->entity_id;
    //die($sql);
    $query = mysql_query($sql);
    while ($value = mysql_fetch_object($query)){
    mysql_query(“INSERT INTO catalog_category_entity_”.$dataType.” (entity_type_id, attribute_id, store_id, entity_id, value)
    VALUES ({$value->entity_type_id}, {$value->attribute_id}, {$value->store_id}, {$newEntityId}, ‘{$value->value}’)”);
    }
    }

    //for Products
    $sql = “SELECT * FROM catalog_category_product WHERE category_id = ” . $id;
    $query = mysql_query($sql);
    while ($value = mysql_fetch_object($query)){
    $sql=”INSERT INTO catalog_category_product (category_id, product_id, position)
    VALUES ({$newEntityId},{$value->product_id},{$value->position})”;
    echo $sql;
    if(!mysql_query($sql))
    echo(“Error”);
    }

    $sql = “SELECT entity_id FROM catalog_category_entity WHERE parent_id = ” . $id;
    $query = mysql_query($sql);

    while ($entity = mysql_fetch_object($query)){
    duplicate_entity($entity->entity_id, $newEntityId);
    }
    $catsDone++;
    }
    ?>

  • Hi guys, does this script work in magento 1.7.0.2? i just added this script to my root folder and tried to run the script, but nothing happend. does anyone have a solution for this?
    Any advice is appreciated!

  • I’m the first to appreciate a man’s work of this sort, but globals, mysql_query? Are you kidding me? And this is for magento…

  • Awesome!! I was nervous about duplicating a root directory but it worked like a charm. Definitely make this an extension. Such a “simple” task that should be in the core.

  • I tried your original code in 1.7.0.2 and that worked, but I also need to duplicate the products within each category. Which your code does not do. I tried the addition that @Mua wrote, but that seems to do nothing. Any advancement on this would be awesome.

  • Hello Guys,

    @Hayden, fantastic script, works great for me, except for the null values.

    @Roy & Mau, also great work for creating the additions, but both of you had errors that prevented this to be a copy & past job.

    @James Philips, I fixed everything up from the 2 other posters and it works perfectly now. I have used this in CE 1.8.1.0 (YMMV).

    I am pasting the code below:


    [php open tag]

    if(!is_numeric($_GET['id']))die('Please specify a category ID');

    $catId = $_GET['id'];

    $xml = simplexml_load_file('app/etc/local.xml');
    $host = $xml->global->resources->default_setup->connection->host;
    $username = $xml->global->resources->default_setup->connection->username;
    $password = $xml->global->resources->default_setup->connection->password;
    $dbname = $xml->global->resources->default_setup->connection->dbname;
    $res = mysql_pconnect($host, $username, $password);
    mysql_select_db($dbname);

    $catsDone = 0;
    duplicate_entity($catId);

    // Fix the children count for all (sub)categories
    $sql = "CREATE TABLE catalog_category_entity_tmp LIKE catalog_category_entity";
    mysql_query($sql);
    $sql = "INSERT INTO catalog_category_entity_tmp SELECT * FROM catalog_category_entity";
    mysql_query($sql);
    $sql = "UPDATE catalog_category_entity cce
    SET children_count =
    (
    SELECT count(cce2.entity_id) – 1 as children_county
    FROM catalog_category_entity_tmp cce2
    WHERE PATH LIKE CONCAT(cce.path,'%')
    )";
    mysql_query($sql);
    $sql = "DROP TABLE catalog_category_entity_tmp";
    mysql_query($sql);

    echo $catsDone . ' Categories duplicated.';

    function duplicate_entity($id, $parent_id = null){
    global $catsDone;

    mysql_query("SET NAMES 'utf8'");
    // Grab category to copy
    $sql = "SELECT * FROM catalog_category_entity WHERE entity_id = " . $id;
    $query_entity = mysql_query($sql);

    $entity = mysql_fetch_object($query_entity);

    if(!$parent_id)$parent_id = $entity->parent_id;

    mysql_query("INSERT INTO catalog_category_entity (entity_type_id, attribute_set_id, parent_id, created_at, updated_at, path, position, level, children_count)
    VALUES ({$entity->entity_type_id}, {$entity->attribute_set_id}, {$parent_id}, NOW(), NOW(), '', {$entity->position}, {$entity->level}, {$entity->children_count})");
    $newEntityId = mysql_insert_id();

    $query = mysql_query("SELECT path FROM catalog_category_entity WHERE entity_id = " . $parent_id);
    $parent = mysql_fetch_object($query);
    $path = $parent->path . '/' . $newEntityId;

    mysql_query("UPDATE catalog_category_entity SET path='". $path."' WHERE entity_id=". $newEntityId);

    foreach(array('datetime', 'decimal', 'int', 'text', 'varchar') as $dataType){
    $sql = "SELECT * FROM catalog_category_entity_".$dataType."
    WHERE entity_id=" . $entity->entity_id;
    //die($sql);
    $query = mysql_query($sql);
    while ($value = mysql_fetch_object($query)) if (is_null($value->value)) {
    mysql_query("INSERT INTO catalog_category_entity_".$dataType." (entity_type_id, attribute_id, store_id, entity_id, value)
    VALUES ({$value->entity_type_id}, {$value->attribute_id}, {$value->store_id}, {$newEntityId}, NULL)");
    } else {
    mysql_query("INSERT INTO catalog_category_entity_".$dataType." (entity_type_id, attribute_id, store_id, entity_id, value)
    VALUES ({$value->entity_type_id}, {$value->attribute_id}, {$value->store_id}, {$newEntityId}, '{$value->value}')");
    }
    }

    //for Products
    $sql = "SELECT * FROM catalog_category_product WHERE category_id = " . $id;
    $query = mysql_query($sql);
    while ($value = mysql_fetch_object($query)){
    $sql="INSERT INTO catalog_category_product (category_id, product_id, position)
    VALUES ({$newEntityId},{$value->product_id},{$value->position})";
    echo $sql;
    if(!mysql_query($sql))
    echo("Error");
    }

    $sql = "SELECT entity_id FROM catalog_category_entity WHERE parent_id = " . $id;
    $query = mysql_query($sql);

    while ($entity = mysql_fetch_object($query)){
    duplicate_entity($entity->entity_id, $newEntityId);
    }
    $catsDone++;

    }
    [php close tag]

  • I need to copy large categories from time to time. I use an extension for that (duplicate categories by Amasty). This is useful if you need to make such things quite often I believe,
    If you need to copy categories only once in your life, the script will work great!

  • Jai,

    Thanks for the recent update.

    I have copied and pasted and all I get it “Please specify a category ID”

    Anyone else get this?

  • Hello,

    Thank you to everyone for the code.

    I have an issue with it that perhaps someone can shed light on. I have Magento 1.9.0.1. After duplicating the category, reindexing, and looking to see if the products show up within that category via the “All store views” selection, the products show up (back and front end.)

    However, If I drag that category to another store via the Magento->Catalog->Manage Categories the products show up via the “All store views” selection but they don’t show up when I change the selection to that particular stores’ view.

    Any suggestions?

    Advanced Thanks
    Greg Hamamjian

  • Hello,

    thank you every body who have contributed to this script and the synthesis by Jai Molloy,
    but it seems not completely operational in multistore/multiview environment.

    Values in store views are not duplicated…

  • Using it in 2015 with Magento 1.9.1.0 and still working perfectly, each category will have to have its “Layered Navigation Price Step” on the “Display Settings” tab though. Thanks a lot for this solution!

Leave a Reply

Your email address will not be published. Required fields are marked *

Copyright © 2013. Created by Hayden Kibble.