2

So, I've created a datatable that asks for a users Mobile Provider (varchar), phonenumber, and pin. The table seems to work well except that when a user inputs a phone number the original number converts to 2147483647. I've spent sometime looking around and I learned that this is because I've gone beyond the 32-bit limit on my table. In order to fix this, I need to make my integer limit larger than 10 (what I originally had) in MYSQL database.

I've changed the MYSQL table using PhpMyAdmin; however, I am still getting the same problem. Is this because I need to now change something on the Yii-end?

I know that I could use CRUD to contstruct a new MVC relationship - but I've already added in quite a few functions to the Model and the Controller. Is there a way with Yii to change my database in piecemeal way? I.e. without having to use Crud?

O. Jones
  • 103,626
  • 17
  • 118
  • 172
Stephen
  • 2,360
  • 2
  • 18
  • 23
  • 1
    Store number as varchar. – sectus Jul 07 '14 at 14:44
  • Check this out from previous answer whey its getting converted to 2147483647 http://stackoverflow.com/questions/23550231/long-integer-is-transformed-when-inserted-in-shorter-column-not-truncated-why/23550275#23550275 – Abhik Chakraborty Jul 07 '14 at 14:44
  • 1
    phone numbers have never been, and never will be "ints". if you insist on continuing to use ints, then use a bigint, which is a 64bit data type. an unsigned big int could represent up to 18,446,744,073,709,551,616 as a "phone number" – Marc B Jul 07 '14 at 14:49
  • 1
    Only use integers if you think there's any case ever where math makes sense. Would you ever need to perform any mathematical operations on a phone number? No. Therefore it should be stored as a string, not an integer. – Andrew Jul 07 '14 at 15:05

2 Answers2

5

The fact that telephone directory numbers (DNs) resemble integers notwithstanding, using a integer of any width to store a DN is very bad practice indeed.

That's because, as you have discovered, DNs that get manipulated as if they were integers sometimes become corrupted.

Using a 32-bit number to store a North American Dialing Plan number (Canada, Caribbean, US) is just flat incorrect. Partway through the 429 area code, your numbers will be corrupted even if you manage to use an unsigned integer.

Use varchar(20) and you'll be fine.

Here's something to think about: ITU-T recommendation E.123 for representing telephone numbers. http://en.wikipedia.org/wiki/E.123

O. Jones
  • 103,626
  • 17
  • 118
  • 172
-2

INT is still a 32-bit number, the (10) in INT(10) is an artificial cap that's mostly useful for saying how far ZEROFILL should fill to.

Try BIGINT(10), as this will allow all 10-digit numbers, even those higher than 231-1

Niet the Dark Absol
  • 320,036
  • 81
  • 464
  • 592