CODEWITHSUNDEEP
phpmysqlsqlphpmyadmin
TreeID
TreeID

Reputation: 47

First 0 disappearing?

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

Is there a workaround or something ?

Upvotes: 2

Views: 13565

Answers (4)

Andrew Hyder
Andrew Hyder

Reputation: 51

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.

Upvotes: 2

TravisO
TravisO

Reputation: 9550

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.

Upvotes: 1

BWS
BWS

Reputation: 3846

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

Upvotes: 0

Bad Wolf
Bad Wolf

Reputation: 8349

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.

Upvotes: 12

Related Questions