PDOException - SQLSTATE[22003] - Numeric Value Out of Range

 

This blog describes how to solve PDOException - SQLSTATE[22003] - Numeric value out of range: 1264 Out of range. When you try to store a large integer value in the 'integer' field type, then you will get this error. It is because the maximum value of the 'integer' field type is exceeded.

 

For example, you want to store a phone number in a content type. Then you may create a new field with the 'integer' field type in the content type. When you store 10 digit phone number in this field, "PDOException error" will be shown. MySQL allows you to store values from -2147483648 to 2147483647 in the integer field type if signed, so you can't store phone numbers in the 'integer' field type. MySQL allocates 4 bytes for 'integer' field type ie.,

4 bytes = 4 x 8 = 32 bits
2^32 => 4294967296 values
2^32 - 1 => 4294967295 (if unsigned, 0 to 4294967295 values)
-2^(32 - 1) to (2^(32 - 1) - 1) => -2147483648 to 2147483647 (if signed, -2147483648 to 2147483647 values).

If you want to store a large integer value, then you need to use field type either 'bigint' or 'text field'. If you choose 'bigint', then there is no need to add custom validation for the phone number field. On the other hand, if you choose 'text field' then you need to add custom validation using either hook_form_alter() or hook_form_form_id_alter().

The ctype_digit() is used to check whether all of the characters in a given string are numerical or not. So it doesn't allow users to store string values such as a phone number.