0

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?

  • check eav_attribute table and check the input type as text, hope that help you – Purushotam Sharma Dec 18 '18 at 10:31
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

  • i need varchar to text not select – aaaa Dec 18 '18 at 14:34
1

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.

  • i need to run any command?after this – aaaa Dec 18 '18 at 10:31
  • No, I think that would not be required, however, you can run php bin/magento indexer:reindex; – Himmat Paliwal Dec 18 '18 at 10:52
  • how we change data type in catalog product varchar table – aaaa Dec 18 '18 at 13:51
  • Was your first issue resolved? – Himmat Paliwal Dec 19 '18 at 4:59
  • yes resolved now in other table need – aaaa Dec 19 '18 at 5:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.