Magento Forum

   
How to remove duplicate categories from database using a script! 
 
cesarfelip3
Jr. Member
 
Avatar
Total Posts:  8
Joined:  2012-08-30
Salt Lake City
 

hey guys

Our store have more than 15 thousands categories, We had a data entry team adding the categories however they add many duplicate categories. 

i would like to have code that check if the same title and if the same title delete the higher ids… however Magento database is pretty complex and have many associations I am kind of worry of doing this in the wrong way and broke something…

Do you guys have any suggestion of the best way for do that?

thanks smile

 
Magento Community Magento Community
Magento Community
Magento Community
 
tzyganu
Mentor
 
Avatar
Total Posts:  2205
Joined:  2009-11-18
Bucharest, Romania
 

Hello.

Here is how you can identify the duplicate names.
First you need the id of the name attribute for categories.
For this run this sql.

select attribute_id from eav_attribute a LEFT join eav_entity_type e on a.entity_type_id e.entity_type_id 
where a
.attribute_code 'name' and e.entity_type_code 'catalog_category';
For me is 33.

Now you need to identify the duplicate names.
Here is the sql (replace 33 with the value you get from the sql above):

select *, count(*)
  
from catalog_category_entity_varchar
where attribute_id 
33 AND store_id 0
  group by value
  having count
(*) > 1

Now all you need to do is to loop through the result from the sql above and for each record do something like this.

//assumun $record is one row from the sql above
SELECT entity_id from catalog_category_entity_varchar where attribute_id 33 and value '{$record['value']}' ORDER BY entity_id ASC;

You will get a set of categories with the same name.
Now loop through this result, skip the first record and delete the rest from the main category table:

//lets assume results is the list of results from the query above:
foreach ($results as $key=> $result){
    
if ($key == 0){
        
continue;//skip first row
    
}
    
//now run this sql
    
DELETE FROM catalog_category_entity where entity_id {$result['entity_id']}
}

This should be it.
I haven’t tested the script but it should work.
Back-up your database before you try this.

Let me know if it’s ok.

Marius.

 
Magento Community Magento Community
Magento Community
Magento Community
 
cesarfelip3
Jr. Member
 
Avatar
Total Posts:  8
Joined:  2012-08-30
Salt Lake City
 

tnank you so much for your help, but it\’s not working I think I am doing something wrong here is the code that I am using:

<?php

$record 
mysql_query(\"select *, count(*) from mage_catalog_category_entity_varchar where attribute_id = 41 AND store_id = 0 group by value  having count(*) > 1 \");

$results mysql_query(\"SELECT entity_id from mage_catalog_category_entity_varchar where attribute_id = 41 and value = \'{$record[\'value\']}\' ORDER BY entity_id ASC;\");


foreach (
$results as $key=> $result){
    
if ($key == 0){
        
continue;//skip first row
    
}
    
//now run this sql
     
mysql_query(\"DELETE FROM catalog_category_entity where entity_id = {$result[\'entity_id\']}\");
}

?>

I am get the error:

Warning: Invalid argument supplied for foreach() in /www/toolsandmachinery.net/htdocs/remove.php on line 24

what am I doing wrong?

 
Magento Community Magento Community
Magento Community
Magento Community
 
tzyganu
Mentor
 
Avatar
Total Posts:  2205
Joined:  2009-11-18
Bucharest, Romania
 

I think that instead of foreach you should use

while ($row mysql_fetch_assoc($result)) {
    mysql_query
(\"DELETE FROM catalog_category_entity where entity_id = {$row[\'entity_id\']}\");
}

It’s been a while since I’ve used musql_query and mysql_fetch_assoc

 
Magento Community Magento Community
Magento Community
Magento Community
 
cesarfelip3
Jr. Member
 
Avatar
Total Posts:  8
Joined:  2012-08-30
Salt Lake City
 

the script was able to run without error but didn\\\’t delete the duplicates categories… Also if I try use the line:

print_r(mysql_fetch_assoc($results));

won\\\’t display anything… do you know what could be?

again thanks for your help smile

 
Magento Community Magento Community
Magento Community
Magento Community
 
roseguang
Jr. Member
 
Total Posts:  3
Joined:  2012-10-21
 

this topic is very interesting to me. I read wiki tutorial how to add field to customer registration form - but when i try to add field to customer addresses - i cannot write and properly read from/to database.
cheap nike air max tn shoes nike air max tn au nike free trainer 7.0

 
Magento Community Magento Community
Magento Community
Magento Community
 
cesarfelip3
Jr. Member
 
Avatar
Total Posts:  8
Joined:  2012-08-30
Salt Lake City
 

I found a way to do using only sql query on phpmyadmin…

So First we have to get the id of the name attribute for categories. (like zyganu said):

here is the sql:

select attribute_id from eav_attribute a LEFT join eav_entity_type e on a.entity_type_id e.entity_type_id 
where a
.attribute_code \'name\' and e.entity_type_code \'catalog_category\';

In my db case i got 41, se we have to run this second sql putting the \"41\" or whatever number you got at vtable.attribute

DELETE FROM atalog_category_entity USING
catalog_category_entity
catalog_category_entity_varchar as
vtablecatalog_category_entity_varchar as vtable1 WHERE
(vtable1.entity_id>vtable.entity_id) AND
(
vtable1.store_id=vtable.store_id) AND (vtable1.attribute_id=41) AND
(
vtable.attribute_id=41) AND (vtable1.value=vtable.value) AND
(
catalog_category_entity.entity_id=vtable1.entity_id);

That\’s it it!  smile

 
Magento Community Magento Community
Magento Community
Magento Community
Magento Community
Magento Community
    Back to top