PHPmyadmin field type

Question

I want to give the user the option to enter a phone number in a app i'm creating with android studio but what is the proper field type to put in on phpmyadmin ? E.g varchar, int, etc.

Also in my php code do I put it as string like I did for username and password or something different ?

$statement = mysqli_prepare($con, "INSERT INTO user (username,password, phone) VALUES (?, ?, ?)");
mysqli_stmt_bind_param($statement, "ss",$username, $password);

depends on the phone number format. If you are using purely numbers e.g. `0123456` then you can use `int` otherwise if you are using a format like `0-123-456` then `varchar` etc is needed. — Matt, Jul 02 '16 at 22:22
I want to put purely numbers , but what if the user doesn't put purely numbers ? — , Jul 02 '16 at 22:24
You will need to use some javascript to do some client-side validation to stop them using anything but numbers. Otherwise the bind_param will fail to bind a non-int variable to a int reference (when you put `'i'` in bindparm) — Matt, Jul 02 '16 at 22:25
Use strings, php will be smart enough to change the type later down the road if a user uses just numbers or you can force an int->string conversion to be safe/starts throwing errors :) but it'd be wise to enforce a common format for easier handling later on for whatever reason you might need. — Matt, Jul 02 '16 at 22:28

score 0 · Answer 1 · edited May 23 '17 at 12:08

A phone number, despite containing digits, isn't a number. It's not a count of anything, you won't be doing math with it. I definitely think you should store it as a char (or possibly varchar, see below about extensions). There can be unexpected ramifications of trying to store a non number in a numeric data type (for instance, what happens if — against common convention — a user enters a phone number that starts with 0?). Some data types could introduce rounding errors.

You can do client-side or server-side validation to make sure the format matches how you prefer to store them in the database (for instance, as 555-123-4567 or (555) 123-4567 or 5551234567). Personally, I'd strip off all formatting such as dashes, spaces, and parenthesis for storage, then on display you can format it based on client setting, business purpose, or end-user preference.

If you have any chance of ever storing an extension (555-123-4567 x890) or a phone number from any other country, you should probably use varchar with enough characters to include all possibilities. I just happen to use USA phone numbers in my examples, but if someone from the UK wants to put their international number in my database, I need to allow enough character space for their longer number plus country code.

There's some interesting discussion (and differing opinions) at mysql datatype for telephone number and address

PHPmyadmin field type

1 Answers1