First 0 disappearing?

Question

How do I tell phpmyadmin, MySQL to allow the first 0 in front of a phone number for example ? (Not a zero fill)

When I try to put a zero in front of it (in phpmyadmin), it removes it automatically.
When I submit my form number with the 0 specified in front of the number it doesn't add it.

Is there a workaround or something ?

Perhaps you are using an integer type and not a string, or the formatting is off. — jrafferty, Aug 16 '13 at 16:47
Use `VARCHAR` or `CHAR` and not `INT`. The integer datatype will trim leading zeros and is not appropriate for phone numbers. — Bad Wolf, Aug 16 '13 at 16:51
indeed sprint() might work, but what if column type allow's null and sprint is set in a foreach loop ? — TreeID, Aug 16 '13 at 16:54
http://stackoverflow.com/questions/2786193/allow-number-to-start-with-zero-when-stored-in-mysql-integer-field http://stackoverflow.com/questions/6279723/mysql-datatype-for-storing-integers-starting-from-zero — Rohit Chemburkar, Aug 16 '13 at 16:58
How are you storing phone numbers in a numerical field? Don't. Some phone numbers are 12-18 digits long and include important extension information. — tadman, Aug 16 '13 at 17:12
@Rohit, indeed it was already posted, don't saw it with the keywords I searched. — TreeID, Aug 16 '13 at 19:21

score 12 · Accepted Answer · answered Aug 16 '13 at 16:53

The INT datatype in mysql stores just that, an integer. As you would expect with the integer datatype in any programming language, the database engine trims off any leading zeros and rounds any decimal points, making it inappropriate for storing phone numbers.

If the number of digits in the phone number is always the same, use the datatype CHAR(length) instead, or if it's not use the datatype VARCHAR(max_length). These will store the exact value entered rather than an integer value.

score 2 · Answer 2 · edited May 19 '19 at 10:27

When using mysql, and php dont put your string in this way:

mysql_query("UPDATE bids SET time_off = ".$editend." WHERE id = '" . $editbid_id . "'");

without a single quote around your var (which is a string number). do this:

mysql_query("UPDATE bids SET time_off = '".$editend."' WHERE id = '" . $editbid_id . "'");

in other words, make sure there are quotes around the var you put it into the db, otherwise it will see it as a numeric value and remove your leading 0, even if your field is a string value.

score 1 · Answer 3 · answered Aug 16 '13 at 17:04

1

When storing numbers that might have a 0 in front, use a VARCHAR or CHAR data type. Phone numbers, zip codes, product SKUs are prime examples of where you should always use a non number variable type.

answered Aug 16 '13 at 17:04

TravisO

9,406
4
36
44

score 0 · Answer 4 · answered Aug 16 '13 at 16:54

0

Or, you can leave the PHONE in the 'int' format, and add the leading zeroes when you select it.

Like this:

select right('0000000000'+convert(varchar(10), PHONE), 10)

-- if your phone numbers have more than 10 digits, you adjust accordingly

answered Aug 16 '13 at 16:54

BWS

3,786
18
25

First 0 disappearing?

4 Answers4

Linked