4

I have a MySQL database where I want to store phone numbers among other things.

The fieldtype is INT(10)

When I try to insert a number starting with a 0, like 0504042858 it's stored like 504042858. This only happens with phone numbers with leading zeros. When the number start with any other number, it's stored correctly.

What am I doing wrong?

nhahtdh
  • 55,989
  • 15
  • 126
  • 162
iggnition
  • 77
  • 2
  • 4

3 Answers3

5

You should probably store phone numbers as a varchar. Phone numbers are only numeric by accident.

You may also be interested in checking out the following Stack Overflow posts:

Community
  • 1
  • 1
Daniel Vassallo
  • 337,827
  • 72
  • 505
  • 443
  • Hi, Thank you for your quick response. I was wondering if storing a number as a string isn't bad practice. Also why would MySQL delete my 0 without me asking it? – iggnition Apr 26 '10 at 07:40
  • 1
    It is not deleting it. 0504 is 504 in numeric terms... In addition, I would say that storing them as an `varchar` is in fact the good practice. – Daniel Vassallo Apr 26 '10 at 07:41
  • because integers doesn't have leading zeroes, 01, 0001 and 1 is still the same number , 1. – nos Apr 26 '10 at 07:42
  • Thank you everybody for your answers, I changed the fieldtype to varchar(10) yet still my zeroes are deleted. EDIT: emptied cache and suddenly works now, thanks everybody! – iggnition Apr 26 '10 at 07:45
2

You can give length INT(11) with attribute value UNSIGNED_ZEROFILL. it will fill all 11 digits and if any digit length is less than 11, it will add zero itself before the value. This might solve your problem.

ﻂﺎﻫﺭ ﻏﻔﺎﺮ
  • 49
  • 2
1

it is removing the leading zero because mathematically they are the same and removing the leading zero is a quick storage optimization. In addition it also makes the numbers easier to read imagine a number padded with several leading zeros in a column of several hundred numbers.

I agree with Daniel change your column to a varchar.

Steve Robillard
  • 13,445
  • 3
  • 36
  • 32