Leaving leading zeros

PHP
1

Hi,

Have a quick Q about MYSQL/PHP (5) automatic stripping of leading zeros.

I have users entering Australian mobile numbers, and these contain a leading zero.

Whats the best way to store them with the leading zero? Ive tried something in PHP like $number = “0” . $num, but it gets stripped away again when entered in to the database.

From my understanding of zerofill, it adds zeros to make it the chosen length? My only problem with this is, if I have a number like 0400000000, does MYSQL also strip zeros in the tail, so I have 000000004?

I have also tried changing the data type to varchar. Which didn’t work.

2

Nothing is being stripped, a conversion is happening. 000007 is exactly equal to 07 and 007 in the integer world, but “000069” is not exactly equal to “69” in the string world. In either case, 400 would never become 4.

3

Remember that octal can be tricky here, so leading zeros are not always insignificant.

<?php
$n1 = 034; // octal
$n2 = 34;
echo "$n1 == $n2: ";
var_dump($n1 == $n2);
?>
4

hash: That makes sense. I thought zero stripping might be similar to something like trim, where characters can be stripped from the front AND back of a string, in this case being the zeros.

5

You can use int(10) with zero fill, and the leading zero should remain. If you might need to store international numbers you’ll need a field for the country code though, and the leading zero may be dropped for presentation.
For example an Australian mobile number with country code looks like 61444555666

6

The varchar type will work without stripping any zeros. I haven’t needed to use an int with leading zeros before, so I can’t help with that. But if varchar it isn’t working for you, there is something wrong with your PHP or your MySQL statement.

Print out your query before it’s executed to check for errors. If you are using VARCHAR and your query looks like INSERT INTO TABLE (phone) VALUES(0400000000); it will convert the number into a string, which will remove your leading zero. However, if you do INSERT INTO TABLE (phone) VALUES(‘0400000000’); the value is already a string and you will not lose the leading zero. If your query statement is already missing the leading zero, there is an error in your PHP code.

Hope that helps.

7

If anything you’d use CHAR over VARCHAR.