Magento 2 Attribute Type change

Question

I have created product level attribute with the type text field.

Now I am facing issue because complete value not coming.

How we can update the Catalog Input Type for Store Owner text field to the text area in magento2?

• Purushotam Sharma: check eav_attribute table and check the input type as text, hope that help you

Answer 1

Magento 2: Changing Product Attribute Type Admin does not allow you to change the attribute type once it is created and values have been entered. But as mentioned above we can achieve this

programmatically.We have to do this without losing the existing attribute values that (in the above example) have been entered for 1000 products.

When the attribute is in "Text", the product value of this attribute is stored in the "catalog_product_entity_varchar", but when we change the attribute type as "Dropdown", the product value will be stored in "catalog_product_entity_int". By using the below code we can change the product attribute type from text box to drop down in a short time without losing the existing values.

Place this code in the root directory of Magento 2 files and execute it.

---

<?php
error_reporting(1);
ini_set('max_execution_time', 0);
use \Magento\Framework\App\Bootstrap;
require __DIR__ . '/app/bootstrap.php';
$bootstrap = Bootstrap::create(BP, $_SERVER);
$objectManager = $bootstrap->getObjectManager();
$url = \Magento\Framework\App\ObjectManager::getInstance();
$storeManager = $url->get('\Magento\Store\Model\StoreManagerInterface');
$mediaurl= $storeManager->getStore()->getBaseUrl(\Magento\Framework\UrlInterface::URL_TYPE_MEDIA);
$state = $objectManager->get('\Magento\Framework\App\State');
$state->setAreaCode('frontend');
// Attribute Id that we need to change
$attributeId = 162; 
$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 the product values as an option 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 to dropdown
$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());
}
?>

---

How To Execute This Code?

Step 1 – Copy the above code and paste the same in a new file called sample.php in the Magento 2 root directory.

Step 2 – Execute the sample.php file by entering the browser URL along with the file name (example – www.yourdomainname.com/sample.php)

Step 3 – Once the above code is executed, please do not forget to reindex and also clear all the cache.

Hope this will help you out

• aaaa: i need varchar to text not select

Answer 2

You can try below query:

UPDATE eav_attribute SET frontend_input = 'textarea' WHERE eav_attribute.attribute_code = 'your_attribute_code';

Update your attribute code in query and run it. Please try at local or staging server first and then try at production server if everything goes as expected.

• aaaa: i need to run any command?after this
• Himmat Paliwal: No, I think that would not be required, however, you can run php bin/magento indexer:reindex
• aaaa: how we change data type in catalog product varchar table
• Himmat Paliwal: Was your first issue resolved?
• aaaa: yes resolved now in other table need

Answer 3

If you want to change the type of a Magento product attribute from textfield to textarea.

Please create a backup before making any changes in the database table and follow these steps :

1. Please go to your database

2. Look at the table eav_attribute

3. Select your attribute row

4. Change only two column values

   backend_type : change varchar to text

   frontend_input : text to textarea

5. Now check your attribute changed from textfield to textarea with old values of all products.