I have a column in my database named "O_ContactNo." (Owner Contact Number). Whenever I insert a value into this column it always goes into my database as "247483737". This seems to be a phone number that was in a record that I had previously deleted. But, no matter what new records that I insert with phone numbers this column always ignores the phone number that I try to insert and puts in "247483737". Does anyone know how to fix this?

Maybe there's some type of MySql ghost inside of my database or something.

My guess is that the contact number in the db isn't defined as large enough to hold the phone number, so you're getting an overlow problem.

Right... are you using a numeric/integer field for phone numbers? Make sure you're using a text/varchar field for those, if nothing else you risk weird calculations on the number. For example if you didn't clear out dashes, and tried to insert a value of 605-555-1234 into an unsigned integer field, you'd get "18446744073709551615". Although if your number is consistent regardless of input it's probably an overflow or something. Consider displaying the SQL query and then running it manually at the mysql command line or phpMyAdmin to make sure and catch any warnings/errors that SQL itself is generating.

/edit

Actually it sounds like you might have already tried some of this... give us the table definition for the columns and the actual SQL statement you're trying to run if you're still stuck and maybe a 2nd pair of eyes will catch something.

Right... are you using a numeric/integer field for phone numbers? Make sure you're using a text/varchar field for those, if nothing else you risk weird calculations on the number. For example if you didn't clear out dashes, and tried to insert a value of 605-555-1234 into an unsigned integer field, you'd get "18446744073709551615".


I'm simply usint int(10). I'm inputing the phone number as simply 10 numbers without hyphens or anything. The number is always the same number like you mentioned. Maybe it's the overflow. But, I am going to change the definition to char(10) and see what happens. My plan was to only have the raw number in the database and format it with code as it's retrieved on a webpage with php.

It works now guys. I changed the field definition to varchar and it works. Thank you.

Yup... I just really recommend that route over numeric fields for anything you don't need to actually do calculations on. While you do save a few bytes per record, what's 1MB every 175,000 rows in most applications? :)

Okay, first thing -- you are misunderstanding how the integer type works.

int(10) doesn't mean you will get a 10 "space" integer. The 10 just represents how many spaces to pad on the left when displaying the integer (when you specify ZEROFILL, it is the number of zeros to pad). It has absolutely nothing to do with the size of the field, as it will always be a maximum of 4 bytes, and will max out at 2147483647 for signed and 4294967295 for unsigned integer columns. So obviously, you have run out of signed signed integer space in your column; because as per MySQL's default behavior, if you have maxed out the field, it will always insert the maximum known value for that type.

Just FYI, the BIGINT type is a 8-byte integer that has a range of -9223372036854775808 to 9223372036854775807 for signed and 0 (obvious, but some people miss that) to 18446744073709551615 for unsigned integers.

Also, as others have mentioned, unless you are doing mathematical calculations, its not necessary to store numbers in integer fields, and for telephone numbers, its really not necessary since you will almost never have to do mathematical operations on telephone numbers.

As a sidebar, for IPv4 addresses, its always better to store them in a 4-byte integer field (that's your normal INT type) rather than VARCHAR(15) or some other variation of character types because it will give you certain fringe benefits. For example, it will not let you insert in a bad (10.0.0.500) IP address and you can do queries with netmasks using the inet_aton() function -- not to mention the enormous amount of disk space that you will be saving.