Change field type with data to text
2013 10 Dec

Change Field Type with data to Text

By Akshat Khariwal

Sometimes, our client can change his/her requirements and that change might involve changing the type of a field from any type to TEXT. Now, Drupal 7 does not support changing the field types if there is existing data.

First thing which comes to our mind is to create a new field and delete the old one, but we have data in the fields and we cannot afford to lose them. So, what to do now?

There might be many ways to do this, but the thing which I found satisfactory and used it, is to change the settings of the field in the drupal installation's database. But before doing this, do keep a dump of your database so that you can revert back to status quo. And do check your queries not twice but three or four times before running them since you will be changing the database of your drupal installation.

For this you have to run around 8 queries

Query 1 and 2: -

Change the type of column in the field's tables.

ALTER TABLE field_data_FIELD_NAME MODIFY FIELD_NAME_value varchar(SIZE);

ALTER TABLE field_revision_FIELD_NAME MODIFY FIELD_NAME_value varchar(SIZE);

Here, SIZE can be 10, 20, 255 etc.

FIELD_NAME is the machine of the field.

Query 3 & 4: -

Add the FIELD_NAME_format column in the field's tables. (Run these two queries only if the columns does not exist.)

ALTER TABLE field_data_FIELD_NAME ADD FIELD_NAME_format VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL;

ALTER TABLE field_revision_FIELD_NAME ADD FIELD_NAME_format VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL;

Query 5 & 6: -

Update type and module column of field_config table.

 
UPDATE field_config SET type = 'text' WHERE field_name = 'FIELD_NAME';

UPDATE field_config SET module = 'text' WHERE field_name = 'FIELD_NAME';

Query 7: -

Update data column of field_config_instance table. In this since we will be working with data in serialized form, you have to make sure that you are attentive to minute details.

UPDATE field_config_instance SET data = 'a:7:{s:5:"label";s:LENGTH_OF_LABEL:"LABEL OF FIELD";s:6:"widget";a:5:{s:6:"weight";s:1:"2";s:4:"type";s:14:"text_textfield";s:6:"module";s:4:"text";s:6:"active";i:1;s:8:"settings";a:1:{s:4:"size";s:2:"SIZE";}}s:8:"settings";a:2:{s:15:"text_processing";s:1:"0";s:18:"user_register_form";b:0;}s:7:"display";a:1:{s:7:"default";a:5:{s:5:"label";s:5:"above";s:4:"type";s:12:"text_default";s:8:"settings";a:0:{}s:6:"module";s:4:"text";s:6:"weight";i:WEIGHT;}}s:8:"required";i:REQUIRED;s:11:"description";s:LENGTH_OF_THE_FOLLOWING_STRING:"DESCRIPTION";s:13:"default_value";N;}' WHERE field_name = 'FIELD_NAME';

REQUIRED = 0 or 1.

WEIGHT = Weight of the field in content type.

DESCRIPTION = Description, if any.

In the table field_config_instance, there is one more column FIELD_ID, keep in mind the value in this field for field_name = 'FIELD_NAME'.

Query 8: -

Update the data column of field_config table.

UPDATE field_config SET data = 'a:7:{s:12:"translatable";s:1:"0";s:12:"entity_types";a:0:{}s:8:"settings";a:1:{s:10:"max_length";s:2:"MAX_LENGTH_OF_FIELD";}s:7:"storage";a:5:{s:4:"type";s:17:"field_sql_storage";s:8:"settings";a:0:{}s:6:"module";s:17:"field_sql_storage";s:6:"active";s:1:"1";s:7:"details";a:1:{s:3:"sql";a:2:{s:18:"FIELD_LOAD_CURRENT";a:1:{s:LENGTH_OF_THE_FOLLOWING_STRING:"field_data_FIELD_NAME";a:2:{s:LENGTH_OF_THE_FOLLOWING_STRING:"value";s:LENGTH_OF_THE_FOLLOWING_STRING:"FIELD_NAME_value";s:6:"format";s:LENGTH_OF_THE_FOLLOWING_STRING:"FIELD_NAME_format";}}s:19:"FIELD_LOAD_REVISION";a:1:{s:LENGTH_OF_THE_FOLLOWING_STRING:"field_revision_FIELD_NAME";a:2:{s:5:"value";s:LENGTH_OF_THE_FOLLOWING_STRING:"FIELD_NAME_value";s:6:"format";s:LENGTH_OF_THE_FOLLOWING_STRING:"FIELD_NAME_format";}}}}}s:12:"foreign keys";a:1:{s:6:"format";a:2:{s:5:"table";s:13:"filter_format";s:7:"columns";a:1:{s:6:"format";s:6:"format";}}}s:7:"indexes";a:1:{s:6:"format";a:1:{i:0;s:6:"format";}}s:2:"id";s:4:"FIELD_ID";}' WHERE field_name = 'FIELD_NAME';

 

After running these queries the type of field will be changed from any other type to TEXT.

If you are not sure that changing the data values would work, then the best way is to create the field in any other drupal instance, copy the data from there, and run the queries using that data. But do keep in mind to change the FIELD_ID.

Featured blog

web-personalization

Personalized Content is a Proven Entity !!

Irrespective of how big a business icon or brand you are, increasing the relevance of your website will always be critical to your success.

Read More

Git Hooks

Git hooks for better codes

We are programmers and we are always on the lookout for ways to improve our code. A good and structured way of coding defines the completeness of a programmer.

Read More

Drupal ,varnish cache

Hard time with Drupal, Varnish Cache and Cookies

Using a reverse proxy server in front of a web server is usually needed for every big site and it is a very good thing to do so as reverse proxy server will handle all the anonymous traff

Read More

Say no to captcha

Say no to captcha - Various Spam Protection Methods

Maintaining high traffic websites have their own merits and demerits, the most annoying thing about them is SPAM.

Read More