0

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);
Improve this question
2

4 Answers 4

Reset to default
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.

Improve this answer
Sign up to request clarification or add additional context in comments.

10 Comments

Okay that makes sense, my coworker requested SMALLINT however I will explain that multiple Australian postcodes start with 0's. So how would I go about implementing that into my query?
@user3577618 Which solution would you like to implement? smallint with lpad() , Smallint with zerofill or varchar ?
smallint with lpad() looks to me like the most viable option. So for instance if you padded it to a length of 4, and a user entered 0023. It will convert from 23 to 0023?
@user3577618 Ok, for implementation, when you are doing your select statements, do them like this: select lpad(postcode,4,'0') from myTable. My advice is go with VARCHAR, or else use ZEROFILL so you don't have to adjust your queries.
Where would I put that select statement though? I'm selecting everything from the registration form and inserting into the database (it's hosted on a server), so the select statement is "SELECT * FROM user"; I don't want to modify the actual postcode database field itself, just modify the way that the user entered postcode variable is stored, will the lpad function store it as a SMALLINT?
|
1

change data type to unsigned-zerofill whatever you are using, float, int, smallint... only edit the field to unsigned-zerofill

Improve this answer

1 Comment

Well spotted! Never used this before
0

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);
Improve this answer

Comments

0

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: https://www.php.net/manual/en/function.str-pad.php

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.