0

I am using Drupal 7 and have a float field. It is used to enter prices.

Most of prices I enter have decimals (e.g. 101.82, 99.88). Some of the prices are negative values (e.g. -55.52, -1009.88).

Sometimes when I enter a decimal value, Drupal will remove the decimal value. (E.g. 101.82 will become 101.00). Other times, it will keep the decimal value.

There doesn't seem to be any rhyme or reason as to why it keeps the decimal values for some fields and omits it for other fields.

How can I ensure that it always keeps the decimal value?

2
1

Your float values are working correctly. It is important to understand when to use a float and when to use a decimal type field. In your situation, using a decimal field seems to be the best solution. Check out these two wiki pages which break down how you would use these in Drupal.

Float

Floating point is something that confuses a lot of people. Floating point numbers are essentially numbers stored in scientific notation, almost the same thing that we learned in math class in school...Floats and Doubles are used to store estimated values. The numbers they store can only be accurate to a certain precision.

Decimal (Numeric)

It allows you to specify the precision and scale of a number...use DECIMAL data type to store exact numeric values, where we do not want any rounding off, but exact and accurate values.

How to convert the field

In order to accomplish this you will need to create a simple module and activate it in Drupal. Start off by following steps 1-4 of this Drupal wiki page. After that is done, follow these steps:

  1. Copy the new ****.module file you created in steps 3-4 and change the extension to ****.install so you now have two files, both with just <?php at the beginning.
  2. Inside of the ****.install file, you need to add an update function that Drupal can run.
  3. Once you have that function, you would place the code from this answer inside.
  4. You will want to adjust the variables in the script to match your configuration ie; $field, precision, scale, not null.
  5. Backup your database, and be ready to revert back to the backup if things go wrong.
  6. Activate the new module, which I suggest you do from the command line via drush en your_module_name.

If your website is not too large, you can get away with this, but if it is big, you will have to explore your options because eventually PHP will timeout.

9
  • 1
    In Drupal 7, is there anyway to convert a float field into a decimal field? I have hundreds of values in the float field already (I know some of these won't be correct, but I'll just have to live with that). Thanks!
    – big_smile
    Sep 9 at 17:21
  • @big_smile use drupal.stackexchange.com/a/151367/20866 and edit the script to set the correct precision and scale. Sep 9 at 17:50
  • @big_smile Sorry! I had the edit my answer a few times when I realized my first solution wouldn't handle everything you needed. Sep 9 at 17:52
  • Thanks for your help! I assume I go to PHPmyadmin > Database name > SQL. Then put the code in the query window & hit "Go". Is that correct? I get this error: 3 errors were found during analysis. Unexpected character. (near "$" at position 0) Unexpected beginning of statement. (near "$" at position 0) Unrecognized statement type. (near "field" at position 1) SQL query: $field = 'field_transaction_amount' #1064 - You have an error; check the manual that corresponds to your MySQL server version for the right syntax to use near '$field = 'field_transaction_amount'' at line 1
    – big_smile
    Sep 10 at 12:23
  • @big_smile I updated the answer with more details on the script. Sep 10 at 13:26
0

Ryan Hartman's answer is the best to use. However, here is an alternative just in case that answer doesn’t work.

  1. Make a back up of your database.
  2. Export your database from phpmyadmin. Before exporting, you might want to sort tables by size and then empty all the cache tables if they are too big. You can also empty search_index.
  3. Open the software in an application that can read large database files such as BBedit. Sublime doesn’t seem to cope with the size.
  4. Search for “number_float” (ensure grep is off)
  5. You will find results like this:
(23, 'field_name’, 'number_float', 'number', 1, 'field_sql_storage', 'field_sql_storage', ….)
  6. Replace the 'number_float' bit with 'number_decimal',
  7. Repeat steps 3-5 for all instances of “number_float”.
  8. Now search for “float
  9. You will find multi line entries that begin with:
CREATE TABLE field_data_field_name( 
In the multiple lines, they will have a line like this: 
 field_name float DEFAULT NULL
  10. Replace the word float with ‘decimal(10,2)’.
  11. Repeat steps 8 to 10 for all instances of “float”. However, when searching you will come across entries that don’t make references to fields, such as search_index and search total. Do not changes these.
  12. Save the file and import into Phpmyadmin. Check in Drupal to see if all is okay.

Tip: Before exporting in step 2, go to Drupal and create a dummy field that uses decimal. (Give it a name that it is easy to search for such as aaaaa). Fill it with some dummy entries within Drupal. Then when you export your database, you can study how Drupal handles decimal fields, so you will know how best to change your float fields in the database.

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.