PHP MySQL转换为SMALLINT

Question

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);

Answer 1

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.