update int value to mysql php

Question

I have a text input field that has a type=number.When i post it from the form containing this element to the saveform.php it recieves the numbers in the form of string.So when i use the update query in saveform.php to update the db with this value , mysql gets updated with the intval of this string.In mysql i have a column by the name mobile_no which is of the type int and of length 15.

saveform.php

 $query="UPDATE `registered_users` SET `user_name`=?,`mobile_no`=?,`addressline1`=?,`addressline2`=?,`city`=?,`zipcode`=? WHERE `user_email`=?";
 $statement = $mysqli->prepare($query);
 $statement->bind_param("sisssis", $username, $mobileno, $addressline1, $addressline2, $city, $zipcode, $email);

This is the code of the query that i have made.I have made a var_dump of the $mobile_no and the output of this dump is given below.

string '9560987629' (length=10)

This gets updated in the db with this int 2147483647 .How am i supposed to update the right values without changing my mysql column from int to string.

Make it a bigint. https://dev.mysql.com/doc/refman/5.5/en/integer-types.html — chris85, Mar 21 '16 at 19:14
mobile phone nnumbers can contain characters like `+` before the country code. It might be better to make it some form of text column instead of a number — RiggsFolly, Mar 21 '16 at 19:16
You may actually want to consider storing phone #s as strings instead of ints. A quick search on SO shows several reasons for using a string data type in this case. — mopo922, Mar 21 '16 at 19:17
Phone numbers aren't numeric data, they're string data. You're storing the value incorrectly in the first place. — David, Mar 21 '16 at 19:18
So i should use string as the datatype to store phone no? @David — Nahid Suhail, Mar 21 '16 at 19:18
@NahidSuhail: Yes. A phone number is a string. By coincidence alone that string generally contains number characters, but it's still a string. I can't imagine any scenario where you'd ever need to *perform math* on a phone number. — David, Mar 21 '16 at 19:19

lshas · Accepted Answer · 2016-03-21T19:37:16.327

The value 2147483647 is actually the highest value a signed integer can have. You need to change the datatype to bigint, then it will allow for large enough numbers.

I would recommend that you store phone numbers as strings though. Depending on the use case, it could be necessary at some point. Some countries actually operate with phone numbers starting with 0. At that point your best bet is to store the numbers as a string.

Why is 2,147,483,647 the max int value?

score 1 · Answer 2 · answered Mar 21 '16 at 19:24

The answer to your question is that the maximum value for an integer column is 2147483647 or 4294967295 if unsigned. https://dev.mysql.com/doc/refman/5.5/en/integer-types.html

You should use a bigint data type to store integers larger than those values.

You probably shouldn't store the phone numbers as integers though. Phone numbers, and zip codes, although formatted as numbers, aren't actually numbers. I recommend storing them as strings.

For example my zip code 02128 if stored in an integer column would be 2128.

update int value to mysql php

2 Answers2