Changing Product Attribute Type from Text to Dropdown in Magento 2

By default in Magento 2, there is no option on the admin side to change the product attribute type that once we created. So we have to create the custom script to change the product attribute type. Here is the example code that will help you to change the product attribute type from text to dropdown in Magento 2.

Also Read: Create Custom Attribute Group & Product Attribute in Magento 2 via InstallData.php

Here are the steps that we have followed in the below example,

  • Give the attribute Id that we want to change from text to dropdown
  • Get the product values that are already stored in the database for the given attribute Id
  • Insert the previously stored product values as an option on the database tables “eav_attribute_option” & “eav_attribute_option_value” for a given attribute Id
  • Assign the inserted attribute option to an appropriate product by using option Id
  • Finally, change the attribute type from text to dropdown on the database by using an UPDATE query
<?php
use Magento\Framework\App\Bootstrap;

/**
 * If the external file is in the root folder
 */
require __DIR__ . '/app/bootstrap.php';

$params = $_SERVER;
$bootstrap = Bootstrap::create(BP, $params);
$obj = $bootstrap->getObjectManager();

$state = $obj->get('Magento\Framework\App\State');
$state->setAreaCode('frontend');
 
/*
 * Instance of object manager
 */
$objectManager = \Magento\Framework\App\ObjectManager::getInstance();

/*
 * Attribute Id that we want to change from text to dropdown
 */
$attributeId = 155;  

$resource = $objectManager->get('Magento\Framework\App\ResourceConnection');
$connection = $resource->getConnection();
 
/*
 * Get the product values that are already stored in the database for given attribute
 */
$catalog_product_entity_varchar = $resource->getTableName('catalog_product_entity_varchar');
$attribute_values = $connection->fetchAll("SELECT DISTINCT attribute_id, value FROM $catalog_product_entity_varchar where attribute_id = $attributeId"); 
if(!empty($attribute_values)) {
    foreach ($attribute_values as $_attribute_values) {

        $attribute_values = $connection->fetchRow("SELECT count(*) as cnt FROM `eav_attribute_option` where attribute_id = $attributeId;");
        $count = $attribute_values['cnt'] + 1;

        /*
         * Insert previously stored product values as an option on the database for a given attribute
         */
        $eav_attribute_option = $resource->getTableName('eav_attribute_option');
        $sql = "insert into $eav_attribute_option(option_id, attribute_id, sort_order) values (null, $attributeId, $count)";
        try {
            $resp = $connection->query($sql);
        } catch (Exception $e) {
            echo '<pre>';  print_r($e->getMessage());
        }
        $lastInsertId = $connection->lastInsertId();

        $eav_attribute_option_value = $resource->getTableName('eav_attribute_option_value');
        $sql = "insert into $eav_attribute_option_value(value_id, option_id, store_id, value) values (null, $lastInsertId, 0, '$_attribute_values[value]')";
        try {
            $resp = $connection->query($sql);
        } catch (Exception $e) {
            echo '<pre>';  print_r($e->getMessage());
        }

        $sql = "insert into $eav_attribute_option_value(value_id, option_id, store_id, value) values (null, $lastInsertId, 1, '$_attribute_values[value]')";
        try {
            $resp = $connection->query($sql);
        } catch (Exception $e) {
            echo '<pre>';  print_r($e->getMessage());
        }
    }
}
 
$catalog_product_entity_varchar = $resource->getTableName('catalog_product_entity_varchar');
$attribute_values = $connection->fetchAll("SELECT * FROM $catalog_product_entity_varchar where attribute_id = $attributeId"); 
if(!empty($attribute_values)) {
    foreach ($attribute_values as $_attribute_values) {

        /*
         * Get the option id for the specific product
         */
        $option_values = $connection->fetchRow("SELECT * FROM `eav_attribute_option` as eao INNER JOIN `eav_attribute_option_value` as eaov on eao.option_id = eaov.option_id WHERE eao.attribute_id = $attributeId and eaov.store_id = 1 and eaov.value = '$_attribute_values[value]'");

        if(!empty($option_values)) {
            $catalog_product_entity_int = $resource->getTableName('catalog_product_entity_int');
            $product_values_exist = $connection->fetchRow("SELECT value_id FROM $catalog_product_entity_int WHERE attribute_id = $attributeId and entity_id = $_attribute_values[entity_id]");

            if(empty($product_values_exist)) {
                $sql = "insert into $catalog_product_entity_int(value_id, attribute_id, store_id, entity_id, value) values (null, $attributeId, 0, $_attribute_values[entity_id], $option_values[option_id])";
                try {
                    $resp = $connection->query($sql);
                } catch (Exception $e) {
                    echo '<pre>';  print_r($e->getMessage());
                }
            } else {
                $sql = "Update $catalog_product_entity_int set value = $option_values[option_id] WHERE attribute_id = $attributeId and entity_id = $_attribute_values[entity_id]";
                try {
                    $resp = $connection->query($sql);
                } catch (Exception $e) {
                    echo '<pre>';  print_r($e->getMessage());
                }
            }
        }
    }
}
 
/*
 * Change the attribute type from text to dropdown on database
 */
$eav_attribute = $resource->getTableName('eav_attribute');
$sql = "UPDATE $eav_attribute SET `backend_type` = 'int', `frontend_input` = 'select', `source_model` = 'Magento\\\Eav\\\Model\\\Entity\\\Attribute\\\Source\\\Table' WHERE `attribute_id` = $attributeId";
try {
    $resp = $connection->query($sql);
} catch (Exception $e) {
    echo '<pre>'; print_r($e->getMessage());
}
 
$catalog_eav_attribute = $resource->getTableName('catalog_eav_attribute');
$sql = "UPDATE $catalog_eav_attribute SET `is_filterable` = 1, is_comparable = 1, is_visible_on_front = 1, is_html_allowed_on_front = 1, is_filterable_in_search = 1, used_in_product_listing = 1 WHERE `attribute_id` = $attributeId";
try {
    $resp = $connection->query($sql);
} catch (Exception $e) {
    echo '<pre>'; print_r($e->getMessage());
}

Hope this helps.

Leave a Reply

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