I have a content type with an integer content field. The field needs to contain float data. What's the easiest way to change the field type from integer to decimal? (There are thousands of existing nodes I will have to convert).
I've found a solution in this comment and this comment. Is it the best?
Just to merge all the previous suggestions into one convenient chunk:
// Change this to your field name, obvs.
$field = 'field_myfieldname';
// Update the storage tables
$tables = array('field_data', 'field_revision');
foreach ($tables as $table) {
$tablename = $table .'_'. $field;
$fieldname = $field .'_value';
db_change_field($tablename, $fieldname, $fieldname, array(
'type' => 'numeric',
'precision' => 10,
'scale' => 2,
'not null' => FALSE,
));
}
// Fetch the current field configuration
$field_config = db_query("SELECT data FROM {field_config} WHERE field_name = :field_name", array(
':field_name' => $field,
))
->fetchObject();
$data = unserialize($field_config->data);
// Update the settings entry
$data['settings'] = array(
'precision' => 10,
'scale' => 2,
'decimal_separator' => '.',
);
// Store the new field config, update the field type at the same time
db_update('field_config')
->fields(array(
'data' => serialize($data),
'type' => 'number_decimal',
))
->condition('field_name', $field)
->execute();
// If you are confident about what bundles have instances of this field you can
// go straight to the db_query with a hardcoded entity_type / bundle.
$instances = field_info_field_map();
foreach ($instances[$field]['bundles'] as $entity_type => $bundles) {
foreach ($bundles as $bundle) {
// Fetch the field instance data
$field_config_instance = db_query("SELECT data FROM {field_config_instance}
WHERE field_name = :field_name
AND entity_type = :entity_type
AND bundle = :bundle", array(
':field_name' => $field,
':entity_type' => $entity_type,
':bundle' => $bundle,
))
->fetchObject();
$data = unserialize($field_config_instance->data);
// Update it with the new display type
$data['display']['default']['type'] = 'number_decimal';
// Store it back to the database
db_update('field_config_instance')
->fields(array('data' => serialize($data)))
->condition('field_name', $field)
->condition('entity_type', $entity_type)
->condition('bundle', $bundle)
->execute();
}
}
A better approach might be to use Drupal's built-in capability to alter the field type, db_change_field().
db_change_field('field_data_field_number', 'field_number_value', 'field_number_value', array(
'type' => 'numeric',
'precision' => 10,
'scale' => 2,
'not null' => FALSE,
));
I just wrote some code that should handle this for you in my Helper module: http://cgit.drupalcode.org/helper/tree/lib/FieldChangeHelper.php?h=7.x-1.x
FieldChangeHelper::changeType('my_int_field_name', 'number_decimal');
It should work fine for most simple cases and fields, but should be tested thoroughly. It also currently doesn't update any field formatters, so you'll want to review the display configuration for the field after running this.
Dave Reids drupal.org/project/helper module above is a good simple solution.
Can't add comment above so posting steps here.
1) Download and enable Helper module
2) Create a new file: changeIntField.php
<?php
FieldChangeHelper::changeType('my_int_field_name', 'number_decimal');
?>
3) Save file in root folder where drupal is installed
4) drush scr changeIntField.php
5) Delete file changeIntFiled.php as you no longer need it.
6) Edit display format for node/entity where field is being used.
7) Edit Views - where the field is used and fix display format settings here as well.
drush php-eval "FieldChangeHelper::changeType('my_int_field_name', 'number_decimal');"?
Mar 9, 2017 at 14:23
Changing the type of a Drupal 7 content field from integer to decimal is quite simple. There is no need to create a new field and then copy definitions. I've used the following three steps:
Find the name of the field's table and the name of the field in that table where the field's value is stored, using MySQL.
Use the MySQL alter statment to alter the field's datatype:
ALTER TABLE field_data_field_myfield MODIFY field_myfield_value decimal(10,2) ;
Change the field definitions in Drupal:
UPDATE field_config SET type = 'number_decimal' WHERE field_name = 'field_myfield';
Steps 1-3 are enough. Although, in certain cases it might be beneficial to compare the properties of an integer and a decimal fields on the field_config table to see if there is anything else which needs to be changed: SELECT id, field_name, type, CONVERT(data USING utf8) FROM field_config WHERE type like 'number_%'.
Note that there is another table for each field which is called field_revision_field_{field_name} and this table mysql column type should also change
Also decimal field has different settings which should be set, use the following script to unserialize field settings modify it and then serialize it again. There are two table field_config and field_config_instance. the following is for field_config
$s = unserialize('field_config table serialize string');
$s['settings'] = array (
'precision' => '20',
'scale' => '0',
'decimal_separator' => '.',
);
print serialize($s);
here is for field config instance :
$s = unserialize('field_config_instance table serialize string');
$s['display']['default']['type'] = 'number_decimal';
print serialize($s);
NOTE : fields may may more than one instance, and the next code should be used for all instances.
Now clear Drupal's cache and head to field settings , edit the field and save it. If you receive no error you're probably good to go :)
I had to change a field called "field_margin_percentage" to a decimal
Step 1 - Updated the database fields
db_change_field('field_data_field_margin_percentage', 'field_margin_percentage_value', 'field_margin_percentage_value', array(
'type' => 'numeric',
'precision' => 10,
'scale' => 2,
'not null' => FALSE,
));
Step 2 - Update the field_config so the field knows the correct handler
db_query("UPDATE field_config SET type = 'number_decimal' WHERE field_name = 'field_margin_percentage'");
Step 3 - Get the existing 'data' column from field_config and add the decimal settings
$current_field_config = 'a:7:{s:12:"translatable";s:1:"0";s:12:"entity_types";a:0:{}s:8:"settings";a:0:{}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:34:"field_data_field_margin_percentage";a:1:{s:5:"value";s:29:"field_margin_percentage_value";}}s:19:"FIELD_LOAD_REVISION";a:1:{s:38:"field_revision_field_margin_percentage";a:1:{s:5:"value";s:29:"field_margin_percentage_value";}}}}}s:12:"foreign keys";a:0:{}s:7:"indexes";a:0:{}s:2:"id";s:3:"161";}';
$s = unserialize($current_field_config);
$s['settings'] = array (
'precision' => '10',
'scale' => '2',
'decimal_separator' => '.',
);
print serialize($s); // This will give you the new value for the data column (see below)
Step 4 - Get the existing 'data' column from field_config_instance and add the decimal settings
$s = unserialize('a:7:{s:5:"label";s:17:"Margin Percentage";s:6:"widget";a:5:{s:6:"weight";s:2:"13";s:4:"type";s:6:"number";s:6:"module";s:6:"number";s:6:"active";i:0;s:8:"settings";a:0:{}}s:8:"settings";a:7:{s:3:"min";s:1:"0";s:3:"max";s:3:"100";s:6:"prefix";s:0:"";s:6:"suffix";s:0:"";s:26:"commerce_user_profile_pane";i:0;s:35:"commerce_user_profile_pane_required";i: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:14:"number_integer";s:6:"weight";s:2:"12";s:8:"settings";a:4:{s:18:"thousand_separator";s:1:" ";s:17:"decimal_separator";s:1:".";s:5:"scale";i:0;s:13:"prefix_suffix";b:1;}s:6:"module";s:6:"number";}}s:8:"required";i:0;s:11:"description";s:115:"The Paddy\'s Seals % of this sale. You can enter this OR the Margin Value. The other one will be calculated for you.";s:13:"default_value";a:1:{i:0;a:1:{s:5:"value";s:1:"0";}}}');
$s['display']['default']['type'] = 'number_decimal';
print serialize($s);
Step 5 - Use the above serialized strings to update your 'data' columns in field_config and field_config_instance
db_query('update field_config set data=\'a:7:{s:12:"translatable";s:1:"0";s:12:"entity_types";a:0:{}s:8:"settings";a:3:{s:9:"precision";s:2:"10";s:5:"scale";s:1:"2";s:17:"decimal_separator";s:1:".";}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:34:"field_data_field_margin_percentage";a:1:{s:5:"value";s:29:"field_margin_percentage_value";}}s:19:"FIELD_LOAD_REVISION";a:1:{s:38:"field_revision_field_margin_percentage";a:1:{s:5:"value";s:29:"field_margin_percentage_value";}}}}}s:12:"foreign keys";a:0:{}s:7:"indexes";a:0:{}s:2:"id";s:3:"161";}\' where field_name=\'field_margin_percentage\'');
db_query('update field_config_instance set data=\'a:7:{s:5:"label";s:17:"Margin Percentage";s:6:"widget";a:5:{s:6:"weight";s:2:"13";s:4:"type";s:6:"number";s:6:"module";s:6:"number";s:6:"active";i:0;s:8:"settings";a:0:{}}s:8:"settings";a:7:{s:3:"min";s:1:"0";s:3:"max";s:3:"100";s:6:"prefix";s:0:"";s:6:"suffix";s:0:"";s:26:"commerce_user_profile_pane";i:0;s:35:"commerce_user_profile_pane_required";i: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:14:"number_decimal";s:6:"weight";s:2:"12";s:8:"settings";a:4:{s:18:"thousand_separator";s:1:" ";s:17:"decimal_separator";s:1:".";s:5:"scale";i:0;s:13:"prefix_suffix";b:1;}s:6:"module";s:6:"number";}}s:8:"required";i:0;s:11:"description";s:115:"The Paddy-s Seals % of this sale. You can enter this OR the Margin Value. The other one will be calculated for you.";s:13:"default_value";a:1:{i:0;a:1:{s:5:"value";s:1:"0";}}}\' where field_name=\'field_margin_percentage\'');
Here's how I did it Drupal 7 in an update_hook_N().
The plan was to change one field called "field_ui_current_salary" from DECIMAL(10) to DECIMAL(10,2) which would allow 2 decimal points for the values instead of no decimals at all!
/**
* Changing Database Currenty Salary field to allow decimals.
* Replace MYMODULE with your module's name and number N with your next incremented value.
*/
function MYMODULE_update_7001() {
// field_ui_current_salary scale = 2 in field_config table.
// Use your 'data' value for $new_data from 'field_config' table for your field and just change where 'scale' is from 0 to 2 (or your desired number of decimals)
$new_data = 'a:8:{s:12:"entity_types";a:0:{}s:17:"field_permissions";a:1:{s:4:"type";i:0;}s:7:"indexes";a:0:{}s:8:"settings";a:5:{s:3:"max";d:99999999.989999995;s:3:"min";d:-99999999.989999995;s:9:"precision";i:10;s:16:"profile2_private";b:0;s:5:**"scale";i:2;**}s:12:"translatable";i:0;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:34:"field_data_field_ui_current_salary";a:2:{s:6:"amount";s:30:"field_ui_current_salary_amount";s:8:"currency";s:32:"field_ui_current_salary_currency";}}s:19:"FIELD_LOAD_REVISION";a:1:{s:38:"field_revision_field_ui_current_salary";a:2:{s:6:"amount";s:30:"field_ui_current_salary_amount";s:8:"currency";s:32:"field_ui_current_salary_currency";}}}}}s:12:"foreign keys";a:0:{}s:2:"id";s:3:"372";}';
db_query('UPDATE {field_config} SET data = :new_data WHERE field_name = :salary;', array(':new_data' => $new_data, ':salary' => 'field_ui_current_salary'));
// field_data_field_ui_current_salary and its revision copy -> DECIMAL(10,2)
db_query("ALTER TABLE {field_data_field_ui_current_salary} MODIFY field_ui_current_salary_amount DECIMAL(10,2);");
db_query("ALTER TABLE {field_revision_field_ui_current_salary} MODIFY field_ui_current_salary_amount DECIMAL(10,2);");
cache_clear_all();
}
The original question was regarding Drupal 7, but I suspect a lot of people are wondering how to do this in Drupal 8. Unfortunately, there isn't any good method for this at the moment. The least bad option is to simply delete the field (and any data it contains) and let Drupal recreate it from scratch.
I'm trying to find a better solution and will post here if and when it's available: https://www.drupal.org/node/2843108
