MySQL Int Removes 0

Question

Hi I have got a column in my database which is set to Int.

But my data always starts with a 0 so whenever I add a new record, it strips the 0 off and I don't want it to do that incase the first character has to be a 1 at some point.

How can I overcome this issue?

Is the best way to use VARCHAR any then validate using PHP?

Update

If I enter 02118272 it is saved to the database as 2118272.

Answer 1

I think that you should use varchar type for that field. If you want to convert a variable to integer in php you can simply do this:

$var=preg_replace("/[^\d]/","",$var);

with this you delete all characters that aren't numbers and then you can put this value into the db preserving the initial 0.

Answer 2

The integer 7 is the same thing as the integer 000000000000000000000000007. They're both... ya know... seven. When stored in the database, it's actually stored as something like 000000000000000000000000007 but most MySQL clients won't bother to show all those zeros.

If it's important in your application to show these numbers using a certain number of digits, you can add back as many leading zeros as you want using the str_pad() function:

str_pad($your_string, 10, '0', STR_PAD_LEFT);

Here, 10 is the length you want the string to be, '0' is the character that will get added on in order to make it that length, and STR_PAD_LEFT says to add characters to the left-hand side of the string.

If, on the other hand, the number '007' is fundamentally different than the number '7', then you will have to use a VARCHAR() field to store it. Those are no longer integers; they're strings with very different meanings.

Answer 3

What you should be storing in your database is data. Formatting of that data is the responsibility of applications, not the database itself.

I would store it as an integer and, if you need that to 7 decimal places with leading zeros, the right place to do that is after extraction of the data to your application.