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?
Magento Stack Exchange is a question and answer site for users of the Magento e-Commerce platform. It only takes a minute to sign up.
Sign up to join this communityI 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?
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
You can try below query:
UPDATE
eav_attribute
SETfrontend_input
= 'textarea' WHEREeav_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.
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 :
Please go to your database
Look at the table eav_attribute
Select your attribute row
Change only two column values
backend_type : change varchar
to text
frontend_input : text
to textarea
Now check your attribute changed from textfield to textarea with old values of all products.