Magento Forum

How to Convert Attributes from Text Type to Dropdown Type
 
WildWire
Jr. Member
 
Total Posts:  19
Joined:  2008-03-31
 

I didn’t notice a way to convert from a Text Attribute to a Select.  Text types are nice since they let you import via CSV without entering the data in one thing at a time.  So this script will let you do your initial import as a text type, then convert it to dropdown for use as a Filter. 
Don’t run this on a live store, and please backup before using it.

Also, store_id_eav is kinda a mystery to me.. For some reason my magento store_id is 0 everywhere except in catalogindex_eav and if set to ‘0’.. they will not work.  Might be wise to check in your catalogindex_eav to see if ‘1’ is what is being used as well.  Again: Backup !!

i put this in your ./magentoroot/customfolder/fixmeup.php
just point your browser to:  http://yourhostname/customfolder/fixmeup.php

<?php 
$attribute_label 
'Whatever It Is Called';  // This is the label as it is shown on the product Page
$store_id '0';
$store_id_eav '1';  // Why is this 1 and not 0? I have no idea.. maybe my store is tweaked !
$sort_order '0';
$entity_id_type '4';
$Do_Queries FALSE// Set this to true when you want it to actually write the changes
$DB_Scheme 'magento';  // your db is called this



$link mysql_connect('hostname''username''password');
if (!
$link{
   
die('Could not connect: ' mysql_error());
}

$sql0 
"SELECT attribute_id FROM ".$DB_Scheme.".eav_attribute e where frontend_label = '".$attribute_label."'";
$a_id mysql_fetch_array(mysql_query($sql0$link), MYSQL_ASSOC);

$sql1 "SELECT distinct value FROM ".$DB_Scheme.".catalog_product_entity_varchar c where attribute_id = '".$a_id['attribute_id']."' AND store_id = '".$store_id."'";
$lazyimport mysql_query($sql1$link);

while(
$row mysql_fetch_array($lazyimportMYSQL_ASSOC)) {

        $sql2 
"INSERT INTO ".$DB_Scheme.".eav_attribute_option (attribute_id, sort_order) VALUES ('".$a_id['attribute_id']."', '".$sort_order."')";
        echo 
$sql2 '<br>';
        if (
$Do_Queries == TRUE{ mysql_query($sql2$link); }
        
        $sql3 
"INSERT INTO ".$DB_Scheme.".eav_attribute_option_value (option_id, store_id, value) VALUES ((SELECT max(option_id) FROM ".$DB_Scheme.".eav_attribute_option where attribute_id = '".$a_id['attribute_id']."'), '".$store_id."', '".$row['value']."')";
        echo 
$sql3 '<br>';
        if (
$Do_Queries == TRUE{ mysql_query($sql3$link); }
        
}

$xy 
0;
$uccess FALSE;  //do not touch please

$sql4 "SELECT c.entity_id, a.option_id FROM ".$DB_Scheme.".catalog_product_entity_varchar c LEFT JOIN ".$DB_Scheme.".eav_attribute_option_value a ON c.value = a.value where c.attribute_id = '".$a_id['attribute_id']."'";
$gettingold mysql_query($sql4$link);

while(
$row2 mysql_fetch_array($gettingoldMYSQL_ASSOC)) {
        
        
if ($row2['option_id'!= ''){
                $xy
++;
                
$sql5 "INSERT INTO ".$DB_Scheme.".catalogindex_eav (store_id, entity_id, attribute_id, value) VALUES ('".$store_id_eav."', '".$row2['entity_id']."', '".$a_id['attribute_id']."', '".$row2['option_id']."')";
                
$sql6 "INSERT INTO ".$DB_Scheme.".catalog_product_entity_int (entity_type_id, store_id, entity_id, attribute_id, value) VALUES ('".$entity_id_type."', '0', '".$row2['entity_id']."', '".$a_id['attribute_id']."', '".$row2['option_id']."')";
                echo 
$sql5 '<br>';
                echo 
$sql6 '<br>';
                if (
$Do_Queries == TRUE{ mysql_query($sql5$link); mysql_query($sql6$link); }
        }
        
        
if ($xy == count($gettingold)) {
                $uccess 
TRUE;
        
}
}

if ($uccess == TRUE{
        $sql7 
"DELETE FROM ".$DB_Scheme.".catalog_product_entity_varchar WHERE attribute_id = '".$a_id['attribute_id']."' AND store_id = '".$store_id."'";
        
$sql8 "UPDATE ".$DB_Scheme.".eav_attribute SET backend_type = 'int', frontend_input = 'select', is_global = '1', is_filterable = '1', apply_to='simple' WHERE attribute_id = '".$a_id['attribute_id']."'";
        echo 
$sql7 '<br>';
        echo 
$sql8;
        if (
$Do_Queries == TRUE{ mysql_query($sql7$link); mysql_query($sql8$link); }
}


mysql_close
($link);

?>
 
Magento Community Magento Community
Magento Community
Magento Community
 
ibeau
Jr. Member
 
Total Posts:  20
Joined:  2012-01-28
 

Worked perfectly on CE1.4.2! Thank you so much

Steps I took:

1) I ran this based on the attribute I wanted to change to a “select” from a “text”

2) Edited the attribute in phpmyadmin and changed it from “text” to “select”

3) Re-indexed and cleared cache

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