My apologies if this has already been answered, I used the search function and could not find a question which matched this exactly.
I am trying to insert a postcode field (Australian) into my database via mysql_query, I have confirmed that everything works, however a valid postcode could be, for example 0021 or 0214.
The problem is, that it is automatically discarding the leading zero's when inserting into the database which is obviously problematic, if memory serves me this could be because the integer is unsigned?
I need the value to be of the SMALLINT type, and retain the leading zeros, I understand that mySQL has a CONVERT function, however I never could get it to work.
This is my current insert query:
$insertquery = "INSERT IGNORE INTO user (username, password, firstname, surname, address, state, postcode)
VALUES ('$username', '$password', '$fname', '$surname', '$address', '$state', '$postcode')";
$insertresult = mysql_query($insertquery);
The removal of leading zeroes is not because of an integer being signed or small. It has to do with the fact that it is an integer all-together. If you want to retain leading zeroes, you will need to convert to using VARCHAR, or you will have to use lpad() when fetching the postcodes.
In my opinion, just go with VARCHAR.
edit : Please read Leonardo Phinheiro's answer for an alternative! As he says, you can set your integer to ZEROFILL. Though I would still use VARCHAR for something like postcodes. Here in The Netherlands we even have letters in our postcodes.
If you want to retain leading zeros you should consider changing type of the postcode to varchar. Execute following SQL:
ALTER TABLE user MODIFY postcode VARCHAR(8);
As far as I know, you can't store an integer with leading zeros inherently in MySQL. However, you can use both MySQL and PHP to generate the leading zeros every time.
Check LPAD() in MySQL: http://dev.mysql.com/doc/refman/5.1/en/string-functions.html#function_lpad
And str_pad() in PHP: http://il1.php.net/manual/en/function.str-pad.php
change data type to unsigned-zerofill whatever you are using, float, int, smallint... only edit the field to unsigned-zerofill